sqlite eatingup memory on iPhone when doing insert

Question

I am having problem with inserting data to sqlite database.

    char *update="INSERT OR REPLACE INTO ct_subject (id,id_parent, title, description, link, address, phone, pos_lat, pos_long, no_votes, avg_vote, photo, id_comerc, id_city, placement, type, timestamp, mail) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
        sqlite3_stmt *stmt;
        if(sqlite3_prepare_v2(database, update, -1, &stmt, nil) == SQLITE_OK){
            sqlite3_bind_int(stmt, 1, [[[newCategories objectAtIndex:i] valueForKey:@"id"] intValue]);
            sqlite3_bind_int(stmt, 2, [[[newCategories objectAtIndex:i] valueForKey:@"id_parent"] intValue]);
            sqlite3_bind_text(stmt, 3, [[[newCategories objectAtIndex:i] valueForKey:@"title"] UTF8String], -1, NULL);
            sqlite3_bind_text(stmt, 4, [[[newCategories objectAtIndex:i] valueForKey:@"description"] UTF8String], -1, NULL);
            sqlite3_bind_text(stmt, 5, [[[newCategories objectAtIndex:i] valueForKey:@"link"] UTF8String], -1, NULL);
            sqlite3_bind_text(stmt, 6, [[[newCategories objectAtIndex:i] valueForKey:@"address"] UTF8String], -1, NULL);
            sqlite3_bind_text(stmt, 7, [[[newCategories objectAtIndex:i] valueForKey:@"phone"] UTF8String], -1, NULL);
            sqlite3_bind_text(stmt, 8, [[[newCategories objectAtIndex:i] valueForKey:@"pos_lat"] UTF8String], -1, NULL);
            sqlite3_bind_text(stmt, 9, [[[newCategories objectAtIndex:i] valueForKey:@"pos_long"] UTF8String], -1, NULL);
            sqlite3_bind_int(stmt, 10, [[[newCategories objectAtIndex:i] valueForKey:@"no_votes"] intValue]);
            sqlite3_bind_text(stmt, 11, [[[newCategories objectAtIndex:i] valueForKey:@"avg_vote"] UTF8String], -1, NULL);
            if ([[[newCategories objectAtIndex:i] valueForKey:@"photo"] length]!=0) {
                NSMutableString *webUrl = (NSMutableString *)[[NSMutableString alloc] initWithString:@"http://www.crotune.com/public/images/subjects/"];
                [webUrl appendString:[[newCategories objectAtIndex:i] valueForKey:@"photo"]];
                UIImage *myImage =  [self getWebImage:webUrl]; 
                if(myImage != nil){
                    sqlite3_bind_blob(stmt, 12, [UIImagePNGRepresentation(myImage) bytes], [UIImagePNGRepresentation(myImage) length], NULL);
                }
                else {
                    sqlite3_bind_blob(stmt, 12, nil, -1, NULL);
                }
                [webUrl release];
                [myImage release];
            }
            else {
                sqlite3_bind_blob(stmt, 12, nil, -1, NULL);
                    //NSLog(@" ne dodajem sliku2");
            }

            sqlite3_bind_int(stmt, 13, [[[newCategories objectAtIndex:i] valueForKey:@"id_comerc"] intValue]);
            sqlite3_bind_int(stmt, 14, [[[newCategories objectAtIndex:i] valueForKey:@"id_city"] intValue]);
            sqlite3_bind_int(stmt, 15, [[[newCategories objectAtIndex:i] valueForKey:@"placement"] intValue]);
            sqlite3_bind_int(stmt, 16, [[[newCategories objectAtIndex:i] valueForKey:@"type"] intValue]);
            sqlite3_bind_int(stmt, 17, [[[newCategories objectAtIndex:i] valueForKey:@"timestamp"] intValue]);
            sqlite3_bind_text(stmt, 18, [[[newCategories objectAtIndex:i] valueForKey:@"mail"] UTF8String], -1, NULL);
        }
        if (sqlite3_step(stmt) != SQLITE_DONE) {
            NSLog(@"%s", sqlite3_errmsg(database));
            NSAssert1(0,@"nemogu updateat table %s", errorMsg);
        }
        else {
                NSLog(@"Ubacio %d",i);
        }sqlite3_finalize(stmt);

What happens is that it starts to eat memory until it finaly quits... On memory warning i close and open database again, I have set cache size to 50 as mentioned in some posts here, and tried putting query into statement - same result.. it just garbles mamory and app quits after 300 inserts on iphone or somewhere around 900 inserts on iPad... Any help would be appreciated..

Answer 1

Wow. SQLite code is a lot of ugly compared to CoreData. But, hey, to each his own...

Use the Allocations instrument to figure out what is consuming all the memory. Off-hand and without seeing the code, is UIImage *myImage = [self getWebImage:webUrl]; potentially over-retaining the image data?

Beyond that it is hard to guess what might be going on without seeing either more of the code or more evidence.