Inserting into sqlite3 database

Question

In my app, I need to save the user local as well as online, but the problem comes when I want to insert the user into its local database (sqlite3).

The user gets to select a char, also a username, this is put through a method to my dbhandler object, like shown.

 -(BOOL) registerUser : (NSString *) username : (int) character {

sqlite3 *db;

if(sqlite3_open([dbpath UTF8String], &db) == SQLITE_OK)
{  
    NSString *formatetStmt = [NSString stringWithFormat:@"INSERT INTO user VALUES (null, '%@', %d, 0, 1, 5, 1, 1, 1, 1, 1 ,1)", username, character];
    sqlite3_stmt *comstmt;

    if(sqlite3_prepare_v2(db, [formatetStmt UTF8String], -1, &comstmt, NULL) == SQLITE_OK)
    {                       
        sqlite3_finalize(comstmt);

        if(sqlite3_step(comstmt) == SQLITE_DONE) {
            NSLog(@"SUCCESS");
            sqlite3_finalize(comstmt);
            return YES;
        }
        else { 
            NSLog(@"NO SUCCES");
            return NO;
        }
        sqlite3_reset(comstmt);    
    }
}

NSAssert1(0, @"addMyObjectIntoDatabase: failed to prepare statement with err '%s'", sqlite3_errmsg(db));

NSLog(@"FAIL");
sqlite3_close(db);
return NO;

}

}

When the code I executed, it says NO SUCCESS, however I do not get a error, and nothing is added to the db.

Ami doing anything wrong here?

Answer 1

SQLite is a C library that is not very desirable to work with directly and I recommend using an Objective-C wrapper for this such as FMDB or even Core Data.

Here are some issues with your example.

1. You never close the database unless opening the database or preparing the statement fails

2. You should bind the username and character using ? to prevent SQL injection rather than a formatted string.

3. You are finalizing the statement before using it. Finalizing happens when you are done with the statement before you close the database.

4. You do not know the error code because you are not storing it from sqlite3_step which is an int.

5. You are returning from the method prematurely and not closing the database. You should probably set a success flag, then make sure your database actually opened, then close it then return the flag at the end.

---

-(BOOL) registerUser : (NSString *) username : (int) character {
    //If using the same database for more operations often you may want to just keep
    //the db open in a centralized location while the app is running
    sqlite3 *db;

    if(sqlite3_open([dbpath UTF8String], &db) == SQLITE_OK)
    {  
        //You should bind these values to prevent SQL Injection
        NSString *formatetStmt = [NSString stringWithFormat:@"INSERT INTO user VALUES (null, '%@', %d, 0, 1, 5, 1, 1, 1, 1, 1 ,1)", username, character];
        sqlite3_stmt *comstmt;

        if(sqlite3_prepare_v2(db, [formatetStmt UTF8String], -1, &comstmt, NULL) == SQLITE_OK)
        {                       
            // You are finalizing a statement before using it move this
            // to after your done with the statement
            sqlite3_finalize(comstmt);
            //Try int status = sqlite3_step(comstmt)
            if(sqlite3_step(comstmt) == SQLITE_DONE) {
                NSLog(@"SUCCESS");
                sqlite3_finalize(comstmt);
                return YES;
            }
            else { 
                NSLog(@"NO SUCCES");
                return NO;
            }
            sqlite3_reset(comstmt); //<- Never gets called because of return
                                    // statement but you should finalize here
        }
    }

    NSAssert1(0, @"addMyObjectIntoDatabase: failed to prepare statement with err '%s'", sqlite3_errmsg(db));

    NSLog(@"FAIL");
    sqlite3_close(db); // <-Never gets called unless statement fails to prepare
    return NO;

}