Some changes to SQLite database are not persisting after app close and relaunch

Question

I've got an iPhone app I'm developing and when the app launches I open an SQLite database connection, and I close it when the application terminates. The database is in the application documents folder.

During the lifetime of the app I run several INSERT and UPDATE statements. However, for some reason my UPDATE statements are not saving properly once the application is closed and then restarted. I am calling sqlite3_prepare_v2, sqlite3_step and sqlite3_finalize properly so I'm sure the transactions should be finalized. I've added breakpoints on all those 3 functions and they are all being hit okay and all returning the correct values.

When I close the application after an update and look in the documents directory I can see another database file with -journal after it. Does this mean something's not working properly transaction wise?

I've also noticed that in my applicationWillTerminate: method where I close the connection using sqlite3_close(), that it is returning SQLITE_BUSY. Why could this be?

I've been struggling for a whole day now so I really hope someone can point me in the right direction!

Here's my code to perform my update queries:

// Update statement
sqlite3_stmt *stmt;
char *query = "UPDATE records SET fielda = ? WHERE pkfield = ?;";
if (sqlite3_prepare_v2(database, query, -1, &stmt, nil) == SQLITE_OK &&

   // Bind
   sqlite3_bind_text(stmt, 1, [myNSStringVar UTF8String], -1, nil) == SQLITE_OK &&
   sqlite3_bind_int(stmt, 2, recordID) == SQLITE_OK) {

   // Execute
   int ret = sqlite3_step(stmt);
   if (ret == SQLITE_DONE) {
      sqlite3_finalize(stmt);
   }
}

Answer 1

Ah... I figured it out! One of my obscure finalize statements never actually got reached! As it was only a SELECT statement I didn't check it over because it has nothing to do with any data manipulation or transactions! That's a day's worth of development out of the window! But I won't make that mistake again!

Thanks for your help anyway guys!

Answer 2

Looks like you need to let sqlite finish up it's current task(s) before killing your app - SQLITE_Busy means that "the database file is locked", so following the docs here should set you on the right path.

Answer 3

If you are reusing the update statements you should call sqlite3_reset() on the statement right after use, a call to sqlite3_clear_bindings() might also be appropriate to make sure the assigned parameters are reset. You should then call sqlite3_finalize only when cleaning up. This might be your problem because if the statement is still being executed and you call sqlite3_finalize on it then execution will be interrupted. That said, you should most probably be reusing statements for efficiency if you are not already. For me it works like so:

static sqlite3_stmt *update_statement = nil;

-(void)performUpdateOnRow: (NSInteger)primaryKey {

    if (update_statement == nil){
        const char *sql = "UPDATE foo SET bar=?,baz=? WHERE pk=?";
        if( sqlite3_prepare_v2(database, sql, -1, &update_statement, NULL) != SQLITE_OK ){
                // Insert some serious error handling here!
        }
    }
    sqlite3_bind_text(update_statement, 1, @"first", -1,SQLITE_TRANSIENT);
    sqlite3_bind_text(update_statement, 2, @"second", -1,SQLITE_TRANSIENT); 
    sqlite3_bind_int(update_statement, 3, primaryKey);

    sqlite3_step(update_statement);
    sqlite3_reset(update_statement);

}

Answer 4

Are you sure saving lots of stuff to your SQLite DB during the app usage is best for performance?

I tend to store changes etc in an NSMutableArray during runtime, setting a "dirty" flag to YES each time an object is updated.

Then in my applicationWillTerminate method I call the following:

[todos makeObjectsPerformSelector:@selector(dehydrate)];

My "dehydrate" method (this is based on Apple's own guide for manipulating SQLite databases) is then essentially this:

- (void) dehydrate {
if (dirty) {
    if (dehydrate_statement == nil) {
        const char *sql = "UPDATE todo SET text=?,priority=?,complete=?,detail=? WHERE pk=?";
        if (sqlite3_prepare_v2(database,sql,-1,&dehydrate_statement,NULL) != SQLITE_OK) {
            NSAssert1(0,@"Error: failed to prepare statement with message '%s'.",sqlite3_errmsg(database));
        }
    }

    sqlite3_bind_int(dehydrate_statement, 5, self.primaryKey);
    sqlite3_bind_text(dehydrate_statement, 4, [self.detail UTF8String],-1,SQLITE_TRANSIENT);
    sqlite3_bind_int(dehydrate_statement, 3, self.status);
    sqlite3_bind_int(dehydrate_statement, 2, self.priority);
    sqlite3_bind_text(dehydrate_statement, 1, [self.text UTF8String],-1,SQLITE_TRANSIENT);
    int success = sqlite3_step(dehydrate_statement);

    if (success != SQLITE_DONE) {
        NSAssert1(0,@"Error: failed to save changes with message '%s'.", sqlite3_errmsg(database));
    }

    sqlite3_reset(dehydrate_statement);
    dirty = NO;
    }
}

Does this help at all?