SQLite3 database doesn't actually insert data - iPhone

Question

I'm trying to add a new entry into my database, but it's not working. There are no errors thrown, and the code that is supposed to be executed after the insertion runs, meaning there are no errors with the query. But still, nothing is added to the database. I've tried both prepared statements and the simpler sqlite3_exec and it's the same result.

I know my database is being loaded because the info for the tableview (and subsequent tableviews) are loaded from the database. The connection isn't the problem.

Also, the log of the sqlite3_last_insert_rowid(db) returns the correct number for the next row. But still, the information is not saved.

Here's my code:

db = [Database openDatabase];           
NSString *query = [NSString stringWithFormat:@"INSERT INTO lists (name) VALUES('%@')", newField.text];
NSLog(@"Query: %@",query);

sqlite3_stmt *statement;

if (sqlite3_prepare_v2(db, [query UTF8String], -1, &statement, nil) == SQLITE_OK) {
  if (sqlite3_step(statement) == SQLITE_DONE){
    NSLog(@"You created a new list!");
    int newListId = sqlite3_last_insert_rowid(db);
    MyList *newList = [[MyList alloc] initWithName:newField.text idNumber:[NSNumber numberWithInt:newListId]];
    [self.listArray addObject:newList];
    [newList release];
    [self.tableView reloadData];
    sqlite3_finalize(statement);
  }
  else {
    NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(db));
  }
}
[Database closeDatabase:db];

Again, no errors have been thrown. The prepare and step statements return SQLITE_OK and SQLITE_DONE respectively, yet nothing happens.

Any help is appreciated!

Answer 1

You should commit the transtaction after the insert using COMMIT; statement.

Answer 2

I have exactly the same problem. Core Data doesn't exactly fit my needs and I've gotten used SQLite. My code is not very different from MishieMoo's, and I experience the very same problems. I even included the sqlite3_last_insert_rowid(), which indicates that the INSERT:ed row actually is inserted, but the changes does not shine through to the database. It's as if we're working on a local copy of the database in the application, and it is never saved down to disc. This truly beats me, since it's not the way I interpreted the API's to work!

Did your find a remedy, MishieMoo!? Or someone else who has?

Answer 3

Make sure you have moved the database to writable directory such as NSDocumentDirectory and NSLibraryDirectory.

The database file you see in XCode resides in the MainBunble which is readable but not writable.

You need to add a method to check at runtime if the database exists in your decided (writable) location, if it doesn't exist, use NSFileManager to copy the database file to the writable directory path.

Example of the method to check database existence: brandontreb