objective-c sqlite3 database changes not persistent

Question

I have an SQLite database for my app. To retrieve the entities from the db I use this method:

- (sqlite3*) openDatabaseNamed:(NSString*)databaseName
{
    if(![databaseName isEqualToString:kTopInternationalDatabaseName] &&
       ![databaseName isEqualToString:kTop500RODatabaseName]){
        NSAssert(nil, @"Database does not exist!");
    }
    sqlite3 * dataBase;
    NSString * path;
    path =  [[NSBundle mainBundle] pathForResource:databaseName ofType:@"sqlite3"];
    if (sqlite3_open([path UTF8String], &dataBase) != SQLITE_OK) {
        NSString * errorString = [NSString stringWithFormat:@"[SQLITE] Unable to open database <%@> ",databaseName];
        NSAssert(nil,errorString);
    }
    return dataBase;
}

- (NSArray *) getAllEntitiesForDatabaseNamed:(NSString*)databaseName
{
  (...)
    sqlite3 * database  = [self openDatabaseNamed:databaseName];

    NSMutableArray *retval = [[NSMutableArray alloc] init];
    NSString *query = [NSString stringWithFormat:@"SELECT * FROM %@",databaseName];

    NSArray *properties = [entityClass classProperties];

    if (sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, nil)
        == SQLITE_OK) {
        while (sqlite3_step(statement) == SQLITE_ROW) {


            for (int i=2; i<countLimit; i++){

                chars = (char *) sqlite3_column_text(statement,i+1);
                if(chars != NULL)
                {
                    containerString = [NSString stringWithUTF8String:chars];
                    if(containerString && containerString.length>0){
                        [entityModel setValue:containerString forKey:properties[i]];
                    }
                }
            }
            [retval addObject:entityModel];
        }
        sqlite3_finalize(statement);
    }
    sqlite3_close(database);
    return retval.copy;
}

Everything works as expected. For setting a custom field for an entity to a specific value in the database I use this method:

- (void)setEntity:(EntityModel *)entity favorite:(BOOL)favorite
{
    NSString *query = [NSString stringWithFormat:@"UPDATE %@ SET favorite = %i WHERE position = '%i';",kDatabaseName,favorite?1:0,entity.positionInTop];

    sqlite3_stmt *statement;
    sqlite3 * database = [self openDatabaseNamed:kTop500RODatabaseName];

    if (sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, nil)
        == SQLITE_OK) {
        sqlite3_step(statement);
        sqlite3_finalize(statement);
    }
    sqlite3_close(database);
}

What is happening is a little weird. If I use the update method and in the same lifecycle of the app query for all entities using getAllEntitiesForDatabaseNamed the changes I did with setEntity:Favorite: persist. On the other hand, if I use the update method, then turn off the app and restart it, the changes I did using setEntity:Favorite: are lost. Any idea on why is that?

PS: I also tried using sqlite3_exec and still the results are the same:

if (sqlite3_exec(database, [query UTF8String], NULL, NULL, NULL) != SQLITE_OK) {
    // deal with error...
    NSLog(@" -- ERROR!");
}

Rob · Accepted Answer · 2013-09-24 16:46:22Z

4

The problem is that you're opening the database in the bundle, which is read only (on a device, at least). You should, instead, check to see if the database exists in your Documents folder, if not, copy it there from the bundle, and then open the database from the Documents folder.

Thus, you might do something like:

NSString *bundlePath = [[NSBundle mainBundle] pathForResource:databaseName ofType:@"sqlite"];

NSString *documentsFolder = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
NSString *documentsPath   = [[documentsFolder stringByAppendingPathComponent:databaseName] stringByAppendingPathExtension:@"sqlite"];

NSFileManager *fileManager = [NSFileManager defaultManager];

if (![fileManager fileExistsAtPath:documentsPath]) {
    NSError *error = nil;
    BOOL success = [fileManager copyItemAtPath:bundlePath toPath:documentsPath error:&error];
    NSAssert(success, @"%s: copyItemAtPath failed: %@", __FUNCTION__, error);
}

Having done that, you can now proceed to open the database at documentsPath.

edited Sep 24, 2013 at 16:46

answered Sep 24, 2013 at 11:44

Rob

441k74 gold badges851 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Teo Over a year ago

Can you elaborate a little more please?

Teo Over a year ago

I believe though that the assert is wrong and that it will crash the app when the copy is successful.

P-Rod Over a year ago

Is there something else that might cause this issue? I'm having the same problem. The sqlite3_prepare statement returns 1 however I have verified that I am pulling the database file from the Documents directory. Do I need to set permissions of some sort? I'm able to access the db with the firefox sqlite_manager tool and my SQL statement works there.

P-Rod Over a year ago

It seems like I'm missing something with regard to the original database file in the main bundle. If I copy my database.sqlite to the Documents directory I don't have a problem. But if I let the file manager do this I get an empty file.

P-Rod Over a year ago

So somehow I managed to have my db file not be included with bundle. To fix this I went to the "Build Phases" and added the .sqlite file to the "copy Bundle Resources"

Collectives™ on Stack Overflow

objective-c sqlite3 database changes not persistent

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related