Rollback transactions in Sqlite



While writing my latest app Litho Reader for Narrative technologies, it became necessary to rollback a database transaction on the local database. After looking online I didn’t find a definitive guide on how to implement a rollback transaction using Sqlite.

The code changes are fairly simple to include in your database code.

  • create a connection to the database
  • create a transaction using “Begin Transaction”.
  • execute your query
  • finalize your statement!   This is important because this signals to Sqlite that your query is finished.
  • if the query was successful, then commit the transaction with “Commit Transaction”
  • on error, rollback the transaction with “Rollback transaction”
  • close your database connection.




    char* errorMessage = NULL;
    NSInteger error = SQLITE_OK;
    
    sqlite3 *database = [[SQLiteManager sharedManager] newConnection];
    if (database) {
        // start your database operation with a named transaction, I used the object class name for this example.
        NSString *transaction = [NSString stringWithFormat:@"BEGIN TRANSACTION %@", [SqliteObject class]];
        error = sqlite3_exec(database, [transaction UTF8String], NULL, NULL, &errorMessage);
        
        if (error == SQLITE_OK)
        {
            NSString *query = @"INSERT INTO theTable (value1, value2) VALUES (?,?)";
            sqlite3_stmt *statement = NULL;
            
            error = sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, NULL);
            if (error != SQLITE_OK) {
                NSLog(@"database error code %d %s", sqlite3_errcode(database), sqlite3_errmsg(database));
            }
            else
            {
                sqlite3_bind_text(statement,   1, [value1 UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(statement,   2, [value2 UTF8String], -1, SQLITE_TRANSIENT);
                
                error = sqlite3_step(statement);
                
                // need to finalize the statement before commit according to sqlite
                sqlite3_finalize(statement);
                statement = NULL;
                NSInteger trasactionErr = SQLITE_OK;
                
                if (error == SQLITE_DONE) {
                    transaction = [NSString stringWithFormat:@"COMMIT TRANSACTION %@", [SqliteObject class]];
                    trasactionErr = sqlite3_exec(database, [transaction UTF8String], NULL, NULL, &errorMessage);
                    if (errorMessage) {
                        NSLog(@"exec error %s", errorMessage);
                    }
                }
                else {
                    NSLog(@"database error code %d %s performing ROLLBACK", sqlite3_errcode(database), sqlite3_errmsg(database));
                    transaction = [NSString stringWithFormat:@"ROLLBACK TRANSACTION %@", [SqliteObject class]];
                    trasactionErr = sqlite3_exec(database, [transaction UTF8String], NULL, NULL, &errorMessage);
                    if (errorMessage) {
                        NSLog(@"exec error %s", errorMessage);
                    }
                }
            }
        }
        [[SQLiteManager sharedManager] closeConnection:database];
    }



Sqlite reference on Transactions