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);
   }
}

Michael Waterfall · Accepted Answer · 2009-05-13 15:52:27Z

2

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!

answered May 13, 2009 at 15:52

Michael Waterfall

20.6k27 gold badges115 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jason Coco Over a year ago

@Michael - in the future, on issues like this, you can use sqlite3_next_stmt() to find un-finalized statements that would prevent your db from closing.

Codebeef · Accepted Answer · 2009-05-13 12:46:44Z

1

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.

answered May 13, 2009 at 12:46

Codebeef

44.1k23 gold badges90 silver badges119 bronze badges

2 Comments

Michael Waterfall Over a year ago

I haven't got many tasks going at all. The update query I'm running is very simple (you can see it on my edited question). I even get that error if I wait a while before terminating the application, and sqlite shouldn't be doing anything at that point.

Codebeef Over a year ago

Are you sure there is not a missing finalize somewhere else in your code?

mh0rkk · Accepted Answer · 2009-05-13 12:52:46Z

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

}

edited May 13, 2009 at 12:52

answered May 13, 2009 at 12:39

mh0rkk

1444 bronze badges

1 Comment

Michael Waterfall Over a year ago

Thanks for your input. I'm not reusing the prepared statement, it's just used once within a function. Should I use sqlite3_reset() or sqlite3_finalize() for that? I'm binding parameters and it's only a very simple update statement so I don't think it's a problem with it still processing. I've just tried clearing the bindings and using reset instead of finalize and it's still not working! I'll edit my post including the code that I'm using to perform the update

h4xxr · Accepted Answer · 2009-05-13 12:55:59Z

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?

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related