FMDB and threaded update/select

FMDB is pretty cool but due to specific iOS behaviour while using threads, it can lead to unexpected results. First rule is – never use FMDB as singleton. Yes, if you’re coming from PHP it can be tempting as single db connection is common there. But consider such scenario:

1. SELECT myfield FROM mytable WHERE id = 1; // let’s say this will give you “0” as result
2. User pushed button in your app, which should change “myfield” to 1 and reload data
3. So in push action you’re doing UPDATE mytable SET myfield = 1 WHERE id = 1;
4. After this you’re calling something like [self reloadData]; and expected value for “myfield” is “1”, but instead you have still “0”

This happens becouse iOS locked your db and has local “snapshot” in which your “myfield” has value “0”. To prevent this all SELECT/UPDATE/INSERT operations should use FMDatabaseQueue. So your update method should looks like this:

-(void)switchMyfield:(NSString*)myid{
    
    FMDatabaseQueue* queue = [FMDatabaseQueue databaseQueueWithPath:@"path/to/db.sqlite"];    
    [queue inDatabase:^(FMDatabase *db) {
        NSDictionary* currentData = [self getRecord:myid];
        if([[currentData objectForKey:@"myfield"] intValue] == 0){
            [db executeUpdateWithFormat:@"UPDATE mytable SET myfield = 1 WHERE id = %@", myid, nil];
        }else{
            [db executeUpdateWithFormat:@"UPDATE mytable SET myfield = 0 WHERE id = %@", myid, nil];
        }
    }];
}

If you need to fetch some date you should use __block and just add columns to list as usual:

-(NSMutableArray*)fetchSomeData{
    __block NSMutableArray* list = [[NSMutableArray alloc] init];
    
    [queue inDatabase:^(FMDatabase *db) {
        NSString *sql = @"SELECT * FROM mytable WHERE myfield > 0";
        FMResultSet *results = [db executeQuery:sql];
        if(!results){
            NSLog(@"%@", [db lastErrorMessage]);
        }else{
            while([results next]) {
                [list addObject:[results resultDictionary]];
            }
        }
    }];
    
    return list;
}