sqlite_prepare_v2 does not return SQLITE_OK

Question

I have been trying to save highscore into database and have been failing for past week, and I have no clue why it is not working. I keep receiving "Problem with prepare statement" and refuses to insert info into database. I have checked with database manager to make sure there is not a typo with sql statement, and when query is run on manager, it works fine - it's just the iphone that's giving me the problem. If anyone could please look over quickly and see something wrong with it and could let me know, I would really appreciate it!

- (NSMutableArray *) saveLocal {
    NSLog(@"save local database");
    @try {
        [self checkDB];
        sqlite3_stmt *sqlStatement2;


        NSString *sqlS = [NSString stringWithFormat:@"INSERT INTO localHighscore (difficulty, score, uname, puzzles, multiplier, oneshots, hints) VALUES (%i,%i,\"%@\",%i,%i,%i,%i)",[[MySingleton sharedMySingleton] goDifficulty],[[MySingleton sharedMySingleton] goScore],_player, [[MySingleton sharedMySingleton] goPuzzles], [[MySingleton sharedMySingleton] goMultiplier], [[MySingleton sharedMySingleton] goOneshots], [[MySingleton sharedMySingleton] goHints]];
        NSLog(@"%@",sqlS);
        const char *sql = [sqlS UTF8String];


        if(sqlite3_prepare_v2(localHighscore, sql, -1, &sqlStatement2, NULL) == SQLITE_OK)
        {
            sqlite3_step(sqlStatement2);
            sqlite3_reset(sqlStatement2);
            sqlite3_finalize(sqlStatement2);
            NSLog(@"save complete");
        } else {
            NSLog(@"Problem with prepare statement");
        }  
        sqlite3_close(localHighscore);
    }@catch (NSException *exception) {
        NSLog(@"An exception occured: %@", [exception reason]);
    }@finally{
        NSLog(@"DB Loaded!");
    }
}

and here is checkDB method which checks if database exists and creates one if it does not

- (void)checkDB {
    NSString *docsDir;
    NSArray *dirPaths;

    // Get the documents directory
    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    docsDir = [dirPaths objectAtIndex:0];

    // Build the path to the database file
    databasePath = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent: @"localHighscore.sqlite"]];

    NSFileManager *filemgr = [NSFileManager defaultManager];    
    if ([filemgr fileExistsAtPath: databasePath ] == NO)
    {
    const char *dbpath = [databasePath UTF8String];
        NSLog(@"file was not found");
        if (sqlite3_open(dbpath, &localHighscore) == SQLITE_OK)
        {
            NSLog(@"db open");
            char *errMsg;
            const char *sql_stmt = "CREATE TABLE IF NOT EXISTS localHighscore(pk INTEGER PRIMARY KEY AUTOINCREMENT, difficulty TINYINT, score MEDIUMINT, uname VARCHAR(255), puzzles TINYINT, multiplier TINYINT, oneshots TINYINT, hints TINYINT)";

            if (sqlite3_exec(localHighscore, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
            {
                NSLog(@"Failed to create table");
            }
            sqlite3_close(localHighscore);            
        } else {
            NSLog(@"Failed to open/create database");
        }
    }
    [filemgr release];
}

Thanks in advance for the help!

Answer 1

A couple of thoughts:

1. You don't appear to call sqlite3_open before trying to use the database.

2. Whenever you get an error, you should look at sqlite3_errmsg, e.g.

   if (sqlite3_exec(localHighscore, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
   {
       NSLog(@"Failed to create table: %s", sqlite3_errmsg(localHighscore));
   }
   

3. Probably unrelated to your problem, but you should generally not build a SQL statement using stringWithFormat (at least if you have any text fields). Use ? placeholders in your SQL and then use sqlite3_bind_xxx functions.

   const char *sql = "INSERT INTO localHighscore (difficulty, score, uname, puzzles, multiplier, oneshots, hints) VALUES (?,?,?,?,?,?,?)";
   
   if(sqlite3_prepare_v2(localHighscore, sql, -1, &sqlStatement2, NULL) == SQLITE_OK)
   {
       if (sqlite3_bind_int(sqlStatement2, 1, [[MySingleton sharedMySingleton] goDifficulty]) != SQLITE_OK) {
           NSLog(@"bind 1 failed: %s", sqlite3_errmsg(localHighscore));
       }
   
       if (sqlite3_bind_int(sqlStatement2, 2, [[MySingleton sharedMySingleton] goScore]) != SQLITE_OK) {
           NSLog(@"bind 2 failed: %s", sqlite3_errmsg(localHighscore));
       }
   
       if (sqlite3_bind_text(sqlStatement2, 3, [_player UTF8String], -1, NULL) != SQLITE_OK) {
           NSLog(@"bind 3 failed: %s", sqlite3_errmsg(localHighscore));
       }
   
       // repeat this bind process for each variable
   
       if (sqlite3_step(sqlStatement2) != SQLITE_DONE) {
           NSLog(@"step failed: %s", sqlite3_errmsg(localHighscore));
       }
   
       // reset not needed (doesn't hurt, but not needed unless you're going to re-use it
       // sqlite3_reset(sqlStatement2);
   
       sqlite3_finalize(sqlStatement2);
       NSLog(@"save complete");
   } else {
       NSLog(@"Problem with prepare statement: %s", sqlite3_errmsg(localHighscore));
   }  
   sqlite3_close(localHighscore);
   

   If you find this syntax unwieldy, then maybe consider using FMDB, which simplifies your SQL interaction. But be very wary of stringWithFormat with SQL (if the inserted string had a quotation mark, the sqlite3_prepare will fail, theoretically, your app is exposed to SQL injection attacks, etc.).

4. As an aside, you should not [filemgr release], as you don't own it.

Answer 2

I saw that the "sqlite3_prepare_v2 ()" function, returns a 'generic error' (error code = 1) when the SQL statement contains conditions like "booleanfield=false" instead of "booleanfield=0". The same SQL statement executed in the SQL box of SQLiteStudio program gives good results using indifferently the first or the second form of the comparison.