iPhone & SQLite: Some database changes are not persisting after app close and relaunch.

Answer 1

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 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

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?

Answer 4

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 5

Hi Jason,

Is "sqlite3 next stmt()" supported on iPhone ? I am trying to find un-finalized statements as suggested since my database application can't close in ApplicationWillTerminate method.

Any pointers will be much appreciated as I can't find anything beyond the error message "Unable to close due to unfinalised statements".