Insert Query not working (FMDB)

Question

Here is the code which I'm using to insert a record into a sqlite table

FMDatabase *dataBase = [self openDatabase];
    [dataBase open];
    if ([dataBase open] != YES) {
        NSLog(@"DB Error %d: %@", [dataBase lastErrorCode], [dataBase lastErrorMessage]);
    //VERY IMPORTANT
    }

    BOOL success= [dataBase executeUpdate:@"Insert into CrewUnits (pkCrewUnits,fkUnits,fkAgencyVehicles,fkLookupCodes_PrimaryRole, fkLookupCodes_LevelOfCare, PrimaryUnit) values (?, ?, ?, ?, ?, ?);", pkCrewUnits, fkUnits, fkAgencyVehicle, fkLookupCodes_PrimaryRole, fkLookupCodes_LevelOfCare, [NSNumber numberWithInt:1]];
    NSLog(success ?@"YES" :@"NO");
     NSLog(@"Error %d: %@", [dataBase lastErrorCode], [dataBase lastErrorMessage]);
   FMResultSet*resultSet= [dataBase executeQuery:@"select * from CrewUnits"];
    NSLog(@"ResultSet : %@", [resultSet resultDictionary]);

    [dataBase close];

Database Path

- (FMDatabase *)openDatabase
{

    NSLog(@"Open Database");
    NSString *documents_dir = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
    NSString *db_path = [documents_dir stringByAppendingPathComponent:[NSString stringWithFormat:@"HPSix_05BD.db"]];    // DatabasePath
    FMDatabase *db = [FMDatabase databaseWithPath:db_path];
    if (![db open])
        NSLog(@"Failed to open database!!!!!");
    return db;
}

I'm using the same logic to fetch data from table that works fine for me. But I can't insert a record. I don't know what I'm doing wrong here.

Answer 1

Two problems:

1. You are calling open three times. Once in openDatabase and twice in the code that called it.

   So, have your open routine open the database if it can, but return nil if it can't:

   - (FMDatabase *)openDatabase
   {
       NSLog(@"Open Database");
       NSString *documents_dir = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
       NSString *db_path = [documents_dir stringByAppendingPathComponent:[NSString stringWithFormat:@"HPSix_05BD.db"]];    // DatabasePath
       FMDatabase *db = [FMDatabase databaseWithPath:db_path];
       if (![db open]) {
           NSLog(@"Failed to open database!!!!!");
           return nil;
       }
       return db;
   }
   

   Then check that result:

   FMDatabase *dataBase = [self openDatabase];
   if (!dataBase) {
       // quit; note, no point in checking error message as it only returns meaningful messages if you have an open database
   }
   

2. After calling executeQuery, you have to call next.

   FMResultSet *resultSet = [dataBase executeQuery:@"select * from CrewUnits"];
   if ([resultSet next]) {
       NSLog(@"ResultSet : %@", [resultSet resultDictionary]);
   } else {
       NSLog(@"No data found");
   }