Sqlite in iOS memory issues

I spent hours trying to fix that but I've just given up; I have no idea what's wrong.

In my app, I do nested SQL operations to set all my objects correctly. For some reason, sometimes the sqlite3 objects do not get release properly, causing the memory to go up the roof. I understand it is a problem with using correctly sql3_close and sql3_finalize. However, as you will see, I think I have used them correctly.

Here is the source of the problem:

- (NSArray *) getAllSessions {
    if (sqlite3_open(dbPath, &db) == SQLITE_OK) { 
        if (sqlite3_prepare_v2(db, query_stmt, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {

                //I found out that doing something like that
                //toAdd.in_loc = [self getIndoorLocationWithId:[NSNumber numberWithInt:(int)sqlite3_column_int(statement, 6)]];
                //messes the memory up all the time
                //but doing that works OK:
                NSNumber *_id = [[NSNumber alloc]  initWithInt:(int) sqlite3_column_int(statement, 5)];
                toAdd.out_loc = [self getOutdoorLocationWithId:_id];
                [_id release];

                //So I did the same with the second one, but this one messes memory up:
                NSNumber *id2 = [[NSNumber alloc] initWithInt:(int)sqlite3_column_int(statement, 6)];
                toAdd.in_loc = [self getIndoorLocationWithId:id2];
                [id2 release];
            }
            sqlite3_finalize(statement);
        }
        sqlite3_close(db);
    } 
}

So here is the one that messes memory up:

- (IndoorLocation *) getIndoorLocationWithId:(NSNumber *) locId {
    if (sqlite3_open([databasePath UTF8String], &db) == SQLITE_OK) { 
        if (sqlite3_prepare_v2(db, query_stmt, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {  
                //if I comment the thing below it works
                NSNumber *_id = [[NSNumber alloc]  initWithInt:(int) sqlite3_column_int(statement, 5)];
                toReturn.outLoc = [self getOutdoorLocationWithId:_id];
                [_id release];

            }
            sqlite3_finalize(statement);
        }
        sqlite3_close(db);
    }
}

So in the one that messes memory up, I use exactly the same function as the first time (getOutdoorLocationwithId), in the same way but it doesn't work, sqlite3 objects don't get released properly.

I hope you understand my problem, this is driving me nuts!

In a single user app like this, there's no need to continually open and close. This is not like a server based app where you have connection pooling with multiple users where holding the connection can defeat the pool and hurt scalability. In a phone app open it and hold it open. close it when you're done. At a minimum, do that within a recursive call.

To make it worse, you're opening and closing within recursive calls - just leave it open.

Also:

• You're not checking the return codes of finalize
• You're not checking the return codes of close.
• You're preparing (compiling) the statments but you're not saving them off - call reset on the saved statement and execute it again.

Consider using FMDB - it's a good wrapper.

BTW, here's a richer and more durable close but don't use it on every call. Close it when you're ending or your app is going into the background ... This is mine which is similar to what fmdb does.

- (void)close
{
    if (_sqlite3)
    {
        ENInfo(@"closing");
        [self clearStatementCache];

        int rc = sqlite3_close(_sqlite3);
        ENDebug(@"close rc=%d", rc);

        if (rc == SQLITE_BUSY) 
        { 
            ENError(@"SQLITE_BUSY: not all statements cleanly finalized");

            sqlite3_stmt *stmt; 
            while ((stmt = sqlite3_next_stmt(_sqlite3, 0x00)) != 0) 
            {
                ENDebug(@"finalizing stmt");
                sqlite3_finalize(stmt); 
            }

            rc = sqlite3_close(_sqlite3);
        }

        if (rc != SQLITE_OK)
        {
            ENError(@"close not OK.  rc=%d", rc);
        }

        _sqlite3 = NULL;
    }
}

I met the same issue. When i use and open FMDB, it works OK. but in other callbacks it failed and threw exception of "Closing leaked statement." Finally I found that's because i kept the pointer from [FMDatabase databaseWithPath:dbPath] and the pointer is a autorelease object. I fixed the issue by this:

FMDatabase *db = [[FMDatabase databaseWithPath:dbPath] retain];

and when you want to close the database: db [close]; [db release]; db = nil;

By this, you don't always open and close database for each operation, the should be a manager object accounting for it. When the manager starts, the database is open always until the manager stops.