SQLite behaviour on INSERT on iPhone

Question

I'm currently doing the following as part of my iPhone application

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsPath = [paths objectAtIndex:0];
NSString *filePath = [documentsPath stringByAppendingPathComponent:@"cities.sqlite"];

sqlite3 *database;

if(sqlite3_open([filePath UTF8String], &database) == SQLITE_OK) {
   const char *sqlStatement = "insert into table (name, description, image) VALUES (?, ?, ?)";
   sqlite3_stmt *compiledStatement;
   if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)    {
      sqlite3_bind_text( compiledStatement, 1, [name UTF8String], -1, SQLITE_TRANSIENT);
      sqlite3_bind_text( compiledStatement, 2, [description UTF8String], -1, SQLITE_TRANSIENT);
      NSData *dataForImage = UIImagePNGRepresentation(image);
      sqlite3_bind_blob( compiledStatement, 3, [dataForImage bytes], [dataForImage length], SQLITE_TRANSIENT);

   }
   if(sqlite3_step(compiledStatement) != SQLITE_DONE ) {
      NSLog( @"Error: %s", sqlite3_errmsg(database) );
   } else {
      NSLog( @"Insert into row id = %d", sqlite3_last_insert_rowid(database));
   }
   sqlite3_finalize(compiledStatement);
}
sqlite3_close(database);

What's confusing me is that if I take out the section,

   if(sqlite3_step(compiledStatement) != SQLITE_DONE ) {
      NSLog( @"Error: %s", sqlite3_errmsg(database) );
   } else {
      NSLog( @"Insert into row id = %d", sqlite3_last_insert_rowid(database));
   }

the the INSERT isn't saved to the database and gets lost. I'm presuming I'm missing something obvious here?

Answer 1

I would totally recommend using a wrapper instead of dealing with compiling statements yourself... A decent list of options is available here: http://cocoaheads.byu.edu/resources/sqlite

Answer 2

Of course it won't get inserted. You need to call sqlite3_step to actually execute your statement.

Check the documentation out.

It's a SQLITE thing, not an iPhone specific thing.

From the documentation:

After a prepared statement has been prepared using either sqlite3_prepare_v2() or sqlite3_prepare16_v2() or one of the legacy interfaces sqlite3_prepare() or sqlite3_prepare16(), this function must be called one or more times to evaluate the statement.

Answer 3

You're only inserting one row here so there's a lot of boilerplate code. Think what would happen if you wanted to insert multiple rows:

• You'd still need a single prepare statement
• You'd still need a single finalise statement
• You'd need one step statement for each row you want to add. You can kind of think of "step" as being "execute" (or "get next row" if you're looking at a SELECT statement)

By the way, you probably don't want to "step" if the prepared statement failed.