Errors: SQLite3 Database Doesn't Remain Updated

Today I ran into a pretty odd error for a program I've been working on. It makes heavy use of a SQLite3 database, and the core of the database work has been working for a while, but this morning I found that my database changes (INSERTs and UPDATEs) were no longer sticking. I saw them as long as I was running the program, but as soon as I restarted, the database reverted to its previous state.

I tracked this bug through some pretty common debugging procedures:

• I looked at /var/log/system.log, but found no errors.
• I put an NSLog command in to report the results of my INSERT/UPDATE functions to /var/log/system.log, but found they were all returning 101, which the SQLite3 Documentation reports is SQLITE_DONE.
• Finally I went over to to ~/Library/Application Support/iPhone Simulator/User/Applications so that I could see the real state of the database as I was running it.

(And I mention that all because I think it's a pretty good procedure for checking database problems, something that I would have liked to dedicate more time and space to in the book.)

Here I found something peculiar. Beside the standard file mydatabase.db, there was also a new file called mydatabase.db-journal.

The SQLite3 site has some info on this file on a page called File Locking and Concurrency. I'm not going to get into all the specifics (which you can find through that link), but the short answer is that this "rollback journal" was what kept reverting my program to its previous state.

Why? Programming sloppiness. Specifically, I'd missed calling sqlite3_finalize for the latest SQLite function I'd written and an overly-protective SQLite was keeping the database locked as a result. The simple answer was to put the finalize statement in.

And now if you see the same error, you should know the direction to head to fix it.

I've been working on a vastly expanded database class to replace the short sample we presented on pp.307-308 of iPhone in Action. It's cleaner and lets you do a lot more than our original sample. I'm hoping to present that next week, after I finish the work I'm doing with it. Besides making it a lot easier to use SQLite, it'll also keep you from hitting this type of error, since all of this sort of detail will be taken care of for you (hopefully, correctly).

It may be that database work will be largely outmoded when iPhoneOS 3.0 is released, since Apple's Core Data framework is supposed to be included, but until then, there's a lot of us who probably need to work with databases

Comments

[Dustin Swede]

Thank you, Thank you, Thank you, Thank you

I've been struggling over this for the last day because my database was working when I initialize it but after that is didn't save anything. It was all because I was returning in the middle of looping through the columns without finalizing first. Thank you so much for pointing this out!

[Michael]

I had been struggling with the same problem, and was so happy to read this article as I had the same symptoms! However, when I added the sqlite3_finalize functions that I had missed out, it still didn't work. I noticed that the sqlite3_finalize function for one of my update queries returned an error code of 3, meaning "Access permission denied". What could be the cause of this? Thanks for the article though, I'm glad I've found out why my changes were not persisting!

[Michael]

Actually, scratch that, it appears that the sqlite3_finalize function is returning SQLITE_OKAY. However I still get the -journal database file after I quit the app. I have finalized all my statements and I am closing the database.

[Michael]

Ahh I figured it out! One of my obscure finalize statements never actually got reached for one of my prepared statements! 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!

[Shannon Appelcline]

I think my error was in a SELECT too. I'd update the article to note that, but it's probably sufficiently recorded by these comments.

Glad you got the problem fixed!

[Eamon]

Every poster on this sits is heading in the wrong direction !

Any sqlite3 database is installed with an app in the app bundle - which is not a writable folder on any iPhone OS.

You have to copy it over to the writable Documents folder with a function call (and make sure not to overwrite it again by get/set a NSUserDefaults user setting variable that you must permanently store on the phone). Also, you can't rewrite the Documents version back to the app bundle (of course !). Here's a code extract from how we do it ... originality is a delegate property that stores the state of the database (0 is original so copy from app bundle, 1 implies only use the writableDBPath i.e. the Documents version). Then rewrite the incremented originality after a 1st-time use to ensure the copy doesn't recur.

if (originality == 0) {
copied = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
originality++;
[self setUserDefaults];
}