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!

Core data doesn't quite fit what I'm trying to do. I prefer working with SQL, thanks. — MishieMoo, May 07 '10 at 13:41

Matt Chuang · Accepted Answer · 2011-08-01T01:45:09.893

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.

Here's a snippet from my app Snap-it Notes:

+ (BOOL)createEditableCopyOfDatabaseIfNeeded {
    // First, test for existence.
    BOOL success;
    BOOL newInstallation;
    NSFileManager *fileManager = [[NSFileManager alloc] init];
    NSError *error;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"database_file_path"];
    success = [fileManager fileExistsAtPath:writableDBPath];
    newInstallation = !success;
    if (success) {
        [fileManager release];
        return newInstallation;
    }
    // The writable database does not exist, so copy the default to the appropriate location.
    //NSLog(@"database does not exist");
    NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"database_file_path"];
    success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
    [fileManager release];
    if (!success) {
        NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
    }
    return newInstallation;
}

This was the problem and I fixed it months ago. – MishieMoo Aug 31 '10 at 14:02 — MishieMoo, Aug 31 '10 at 14:02

score 0 · Answer 2 · answered May 07 '10 at 10:02

0

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

answered May 07 '10 at 10:02

newtover

31,286
11
84
89

I'm not using transactions in this, as I'm only inserting 1 row. It's meant to be very simple. I've successfully used this code to insert into the database before and still nothing happens. If I add a Start Transaction and then add the Commit, still nothing happens. Everything gets executed and yet nothing is in the database. – MishieMoo May 07 '10 at 13:37
@MishieMoo: each time you issue a DML statement the transaction is opened implicitly. The symptoms (no errors, correct last insert id, and you can query the data right after the insert) are very much the same as when you have not committed the transaction right after the changes. – newtover May 07 '10 at 16:02

score -1 · Answer 3 · answered Jul 29 '10 at 07:24

Missing semicolon in your SQLite statement?

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

I believe it should be:

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

Although this might not be your only problem.

SQLite3 database doesn't actually insert data - iPhone

3 Answers3

Linked