Sqlite inserts get lost after app restart

Question

This is the insert code. It works fine until i close the app and start it again, then all changes are gone. I don't see any error, could it be some iphone specific issue?

const char *sql = "INSERT INTO offers "
                  "(OfferId, AddressId, ShortDescription, LongDescription, TimeStart, TimeEnd, Created, LastEdit) "
                  " VALUES (?, ?, ?, ?, ?, ?, ?, ?)";


sqlite3_stmt *stmt;
int returnValue = sqlite3_prepare(db, sql, -1, &stmt, NULL);
if(returnValue == SQLITE_OK) {
    for(NSDictionary *offer in offers) {
        sqlite3_bind_int(stmt, 1, [[offer valueForKey:@"OfferId"] intValue]);
        sqlite3_bind_int(stmt, 2, [[offer valueForKey:@"AddressId"] intValue]);
        sqlite3_bind_text(stmt, 3, [[offer valueForKey:@"ShortDescription"] UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(stmt, 4, [[offer valueForKey:@"LongDescription"] UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_int(stmt, 5, [[offer valueForKey:@"TimeStart"] intValue]);
        sqlite3_bind_int(stmt, 6, [[offer valueForKey:@"TimeEnd"] intValue]);
        sqlite3_bind_int(stmt, 7, [[offer valueForKey:@"Created"] intValue]);
        sqlite3_bind_int(stmt, 8, [[offer valueForKey:@"LastEdit"] intValue]);

        if(sqlite3_step(stmt) != SQLITE_DONE)
            NSLog(@"instertOffers -> sql-error: %s", sqlite3_errmsg(db));
        else
            NSLog(@"added offer with id: %@", [offer valueForKey:@"OfferId"]);

        sqlite3_reset(stmt);
    }
} else {
    NSLog(@"instertOffers -> sql-error: %s", sqlite3_errmsg(db));
}

sqlite3_finalize(stmt);
sqlite3_close(db);

Answer 1

Is the SQLite database file in the AppBundle, if so you will need to copy it to the document directory if you want to make changes. You can't write to any files in your app bundle.

Answer 2

To write your DB to file manager:

NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    NSString *dbPath = [self getDatabasePath];
    BOOL success = [fileManager fileExistsAtPath:dbPath]; 

    if(!success) {

        NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"yourDB.sqlite"];
        success = [fileManager copyItemAtPath:defaultDBPath toPath:dbPath error:&error];

        if (!success) 
            NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
    }

To fetch your DB from file manager:

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
    NSString *documentsDir = [paths objectAtIndex:0];
    return [documentsDir stringByAppendingPathComponent:@"yourDB.sqlite"];

By this way your changes will be saved, next time you open your DB. I would rather suggest use FMDatabase wrapper, written in Obj C over sqlite. Here is the link for FMDatabase: http://code.google.com/p/flycode/source/browse/trunk/fmdb

hope it helps!!