iOS - SQLite insertion not inserting data

Question

- (id)init {
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *databasePath = [documentsDirectory stringByAppendingPathComponent:@"idataBase.sqlite3"];
    NSLog(@"Doc path: %@",databasePath);
    bool databaseAlreadyExists = [[NSFileManager defaultManager] fileExistsAtPath:databasePath];
    if (!databaseAlreadyExists){
        NSString *databasePathFromApp = [[NSBundle mainBundle] pathForResource:@"idataBase" ofType:@"sqlite3"];
        NSLog(@"Main bundle path: %@",databasePathFromApp);
        [[NSFileManager defaultManager] copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];
    }
    if (sqlite3_open([databasePath UTF8String], &_database) == SQLITE_OK){
        NSLog(@"Database opened");
    }dd
    return self;
}

-(void) setData:(NSDictionary *)data{
    NSLog(@"In Data %@",data);
    NSString *hostAddress = [data objectForKey:@"hostAddress"];
    NSString *userName = [data objectForKey:@"userName"];
    NSString *passKey = [data objectForKey:@"passKey"];
    NSString *listName = [data objectForKey:@"listName"];
    NSString *q = [NSString stringWithFormat:@"INSERT INTO user_info (host_name,user_name,password,text_name) VALUES (\"%@\", \"%@\", \"%@\", \"%@\")",hostAddress,userName,passKey,listName];
    NSLog(@"%@",q);
    sqlite3_stmt    *statement = NULL;
    const char *insertQuery = [q UTF8String];
    sqlite3_prepare_v2(_database, insertQuery, -1, &statement, NULL);
    if(sqlite3_step(statement) == SQLITE_OK){
        NSLog(@"Data Inserted");
    }
    sqlite3_finalize(statement);
    sqlite3_close(_database);

}

I am using the above code for inserting data. Database connection is opened in init method. But I cannot insert data in database. sqlite3_step(statement) never returns SQLITE_OK.

Answer 1

You say:

sqlite3_step(statement) never returns SQLITE_OK.

Correct, if successful, it returns SQLITE_DONE (when doing an insert/update/delete query). See sqlite3_step documentation.

And if you don't get SQLITE_DONE, then you should check sqlite3_errmsg to determine why. If it's reporting "library routine called out of sequence" error, that probably means that sqlite3_prepare_v2 failed, its effectively saying that it's seeing sqlite3_step without a successful sqlite3_prepare_v2 call.

So, you should also check the result of sqlite3_prepare_v2 (as that is, more frequently, the source of the problem) and make sure that is SQLITE_OK, and if not, look at sqlite3_errmsg immediately, before calling sqlite3_step. If you have an error from sqlite3_prepare_v2 and ignore the error and just call sqlite3_step, you'll lose the meaningful error message that you could have checked before calling sqlite3_step, replacing the meaningful error message with a "library routine called out of sequence" error.

---

This is pure conjecture, but given your other questions about issues in copying a database, I might wager that at some time in your testing, you ran a rendition of your code that failed to copy the database, but still proceeded to call sqlite3_open. And sqlite3_open will create a blank database if it doesn't find one. Thus, I wouldn't be surprised if the database was blank and therefore the error message from sqlite3_prepare_v2 is going to report that the table is not found. If my conjecture is correct (that sqlite3_prepare_v2 is failing and calling sqlite3_errmsg is reporting that the table is not found), I'd suggest removing the app from your simulator/device (to remove any blank databases sitting in Documents folder) and try running the app again. I'd also make sure that your code does not attempt to open the database if the copy from the bundle failed (or else you'll risk creating a blank database again). I might also suggest replacing your sqlite3_open code with:

if (sqlite3_open_v2([databasePath UTF8String], &_database, SQLITE_OPEN_READWRITE, NULL) == SQLITE_OK)
    NSLog(@"Database opened");

This is just like sqlite3_open, but this will not create a blank database if it doesn't find one.

---

So, the routine, checking all the SQLite return codes and using ? placeholders in the SQL might look like:

- (void)insertUserInfo:(NSDictionary *)dictionary
{
    NSString *hostAddress = dictionary[@"hostAddress"];
    NSString *userName    = dictionary[@"userName"];
    NSString *passKey     = dictionary[@"passKey"];
    NSString *listName    = dictionary[@"listName"];

    const char *sql = "INSERT INTO user_info (host_name,user_name,password,text_name) VALUES (?, ?, ?, ?)";

    sqlite3_stmt *statement = NULL;

    if (sqlite3_prepare_v2(_database, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"prepare failed: %s", sqlite3_errmsg(_database));
        return;
    }

    if (![self bindString:hostAddress column:1 statement:statement])
        return;

    if (![self bindString:userName column:2 statement:statement])
        return;

    if (![self bindString:passKey column:3 statement:statement])
        return;

    if (![self bindString:listName column:4 statement:statement])
        return;

    if (sqlite3_step(statement) == SQLITE_DONE) {
        NSLog(@"Data Inserted");
    } else {
        NSLog(@"Insert failed: %s", sqlite3_errmsg(_database));
    }
    sqlite3_finalize(statement);
}

Where the method to bind a string value to a column might look like:

- (BOOL)bindString:(NSString *)value column:(NSInteger)column statement:(sqlite3_stmt *)statement
{
    if (value) {
        if (sqlite3_bind_text(statement, column, [value UTF8String], -1, SQLITE_TRANSIENT) != SQLITE_OK) {
            NSLog(@"bind column %d to %@ failed: %s", column, value, sqlite3_errmsg(_database));
            sqlite3_finalize(statement);
            return NO;
        }
    } else {
        if (sqlite3_bind_null(statement, column) != SQLITE_OK) {
            NSLog(@"bind column %d to null failed: %s", column, sqlite3_errmsg(_database));
            sqlite3_finalize(statement);
            return NO;
        }
    }

    return YES;
}