SQLite record not correct on iPhone simulator

Question

I have an SQLite db with 19 records in it. Of those records 18 have a "threatLevel" value of 1, and one of them has a "threatLevel" of 2. The field is set to default to 1. I recently changed the 19th record to "threatLevel" 2 for testing purposes. I used SQLite Manager (a Firefox extension) to add the value directly to the sqlite file in my project. I can query the table and it shows that the 19th record has a "threatLevel" of 2. When I run my app in the simulator, the 19th record has a "threatLevel" of 1. I have tried cleaning my build, uninstalling the app from the simulator, even removing the sqlite database from my project and readding it. Nothing works. SQLite Manager says it has a value of 2, but when the program is run it gets a value of 1. The code to load it into the class from the DB is this:

primaryKey = pk;
 database = db;

 // Compile the query for retrieving City data.
 if (init_statement == nil){
  const char *sql = "SELECT * FROM myTable WHERE id=?";
  if (sqlite3_prepare_v2(database, sql, -1, &init_statement, NULL) != SQLITE_OK){
   NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
  }
 }

 sqlite3_bind_int(init_statement, 1, primaryKey);
 if (sqlite3_step(init_statement) == SQLITE_ROW){
                LOAD THE FIRST 12 FIELDS HERE
     self.threat_level = sqlite3_column_int(init_statement, 13);
 } else {
  self.name = @"Nothing";
 }

EDIT I just tried deleting the sqlite database from the project, and I moved it to my desktop to back it up. I can still build and run my app. So it's looking at an older version of my database, but Spotlight says there are no other versions on my machine except for the one on my desktop. Any help here?

Answer 1

I think instead of using SELECT * you may want to use explicit column names in the select string...

Answer 2

You can use lsof to list the files open by a process, which can hopefully help you nail down which database is being used. Find the process ID of your application with Activity Monitor or the like, and run lsof -p <PID> in Terminal.

Answer 3

Where is your sqlite in your project - eg. Is it in your app bundle or are you writing it to the Documents folder for your app?

If you are writing it to the Documents folder for your app and running the simulator your file will be located in the Application Support folder for the iPhone Simulator

~/Library/Application Support/iPhone Simulator/User/Applications/<application id>

Also I'll add a +1 to using Core Data in general - I just attended an Apple iPhone event and there are some significant efficiency and performance benefits to be gained by using Core Data over most of the other sqlite frameworks and especially over directly accessing the sqlite c api.

Answer 4

It should be pretty easy to identify the name/path of the database that you are using as you're needing to provide the argument as part of sqlite3_open()?

if (sqlite3_open([dataFilePath UTF8String], &database) == SQLITE_OK) {

The database file is likely in the simulator. There is an option within the simulator to "Reset Content and Settings."