CODEWITHSUNDEEP
iossqlitesyntaxtransactionssql-update
Gabriel Ortega
Gabriel Ortega

Reputation: 481

SQLite Transaction Syntax for iOS

I want to loop through an array of objects and update corresponding rows in a SQLite database according to ID. I would like to do this in one transaction. I know I can use:

sqlite3_exec(db, "BEGIN", 0, 0, 0);
sqlite3_exec(db, "COMMIT", 0, 0, 0);

However, I am unsure of how to code the update statements inside of the transaction. I need to bind different variables to the statement. Right now the code looks like this: 

-(void)someUpdateMethod
{
    sqlite3 *db;

    //Establish connection to db
    if (sqlite3_open([[self dbFilePath] UTF8String], &db) == SQLITE_OK)
    {
        const char *query = "UPDATE Table SET Value1 = ?, Value2 = ?";

        sqlite3_stmt *compiledStatement = nil;

        sqlite3_exec(db, "BEGIN EXCLUSIVE TRANSACTION", 0, 0, 0);
        for (someObject *obj in uArray)
        {
            // Repeated statement - This is what I'm not sure of...
            if(sqlite3_prepare(db, query, -1, &compiledStatement, NULL) == SQLITE_OK)
            {
                sqlite3_bind_int(compiledStatement, 1, [obj value1]);
                sqlite3_bind_int(compiledStatement, 2, [obj value2]);
            }
            if (sqlite3_step(compiledStatement) != SQLITE_DONE) NSLog(@"DB not updated. Error: %s",sqlite3_errmsg(db));
            if (sqlite3_finalize(compiledStatement) != SQLITE_OK) NSLog(@"SQL Error: %s",sqlite3_errmsg(db));
        }
        if (sqlite3_exec(db, "COMMIT TRANSACTION", 0, 0, 0) != SQLITE_OK) NSLog(@"SQL Error: %s",sqlite3_errmsg(db));
        sqlite3_close(db);
    }
    else
    NSLog(@"sql-error: %s", sqlite3_errmsg(db));
}

Regardless of the Begin and Commit statements the database is being access on each Update. I'm pretty sure that is because of the step statement, but the update does not occur if I remove it. I would like all the updates to be written at once. Is it possible to use sqlite3_exec and still bind variables for each object in the array? Or is there a different way I should be preparing the statement? Any example of what the inside of the transaction should look like would be a great help to me!

Upvotes: 2

Views: 9961

Answers (1)

Doug Currie
Doug Currie

Reputation: 41200

Your approach will work as you want; even though sqlite is accessing the disk, the updates are not visible to other transactions until you commit. So, in that sense, they are "written all at once."

Your code can be a bit more efficient if you move the prepare outside the loop. If you do this, use sqlite3_reset inside the loop, and sqlite3_finalize after the loop.

  sqlite3_exec(db, "BEGIN EXCLUSIVE TRANSACTION", 0, 0, 0);
  if(sqlite3_prepare(db, query, -1, &compiledStatement, NULL) == SQLITE_OK)
  {
    for (someObject *obj in uArray)
    {
        sqlite3_bind_int(compiledStatement, 1, [obj value1]);
        sqlite3_bind_int(compiledStatement, 2, [obj value2]);
        if (sqlite3_step(compiledStatement) != SQLITE_DONE) NSLog(@"DB not updated. Error: %s",sqlite3_errmsg(db));
        if (sqlite3_reset(compiledStatement) != SQLITE_OK) NSLog(@"SQL Error: %s",sqlite3_errmsg(db));
    }
  }
  if (sqlite3_finalize(compiledStatement) != SQLITE_OK) NSLog(@"SQL Error: %s",sqlite3_errmsg(db));
  if (sqlite3_exec(db, "COMMIT TRANSACTION", 0, 0, 0) != SQLITE_OK) NSLog(@"SQL Error: %s",sqlite3_errmsg(db));
  sqlite3_close(db);

Upvotes: 14

Related Questions