iOS SQLite FMDB Transactions.. Correct usage?

Question

I'm just going to try out using transactions with the FMDB SQLite iOS wrapper.

The documentation is a little vague on transactions but from having a quick look at some functions I have come up with the following logic:

[fmdb beginTransaction];
    // Run the following query
    BOOL res1 = [fmdb executeUpdate:@"query1"];
    BOOL res2 = [fmdb executeUpdate:@"query2"];

if(!res1 || !res2) [fmdb rollback];
else [fmdb commit];

Answer 1

You could also use FMDatabaseQueue to handle your transactions, which is part of fmdb:

[queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:1]];
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:2]];
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:3]];

    if (whoopsSomethingWrongHappened) {
        *rollback = YES;
        return;
    }
    // etc…
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:4]];
}];

Documentation

Answer 2

I wouldn't try to do the second update if the first failed.

bool ret = false;
[fmdb beginTransaction];
ret = [fmdb executeUpdate:@"query1"];
if (ret)
{
    ret = [fmdb executeUpdate:@"query2"];
    if (!ret)
    {
         // report error 2
    }
}

if(ret) 
{
    if (![fmdb commit])
    {
        // panic!
    }
}
else
{
    if (![fmdb rollback])
    {
        // panic!
    }
}

For paranoid robustness you should have a try ... catch block in case anything throws an exception. If you do, you can use it to your advantage.

[fmdb beginTransaction];
@try
{
    if (![fmdb executeUpdate:@"query1"])
    {
        // report error
        @throw someExcpetion;
    }
    if (![fmdb executeUpdate:@"query2"])
    {
        // report error
        @throw someExcpetion;
    }
    [fmdb commit]
}
@catch(NSException* e)
{
    [fmdb rollback];
    // rethrow if not one of the two exceptions above
}

Answer 3

Swift way:

let queue = FMDatabaseQueue(path: databaseURL.path!)

queue.inTransaction() {
    db, rollback in

    result = db.executeUpdate("INSERT INTO client VALUES (NULL, ?)", client.name ?? "")

    if result {
        client.ID = Int(db.lastInsertRowId())
    } else {
        rollback.initialize(true)
        print("\(__FUNCTION__) insert into table failed: \(db.lastErrorMessage())")
    }
}

queue.close()

Answer 4

It seems like a valid usage scenario, to which I might add outputting the values of -lastErrorMessage and -lastErrorCode before you perform a rollback, so that you get a sense of what exactly went wrong.

Better yet, make those calls after each -executeUpdate, so you'll know if an error occured after each statement:

[fmdb beginTransaction];

// Run the following query
BOOL res1 = [fmdb executeUpdate:@"query1"];
if (!res1) {
   NSLog(@"Error %d - %@", [fmdb lastErrorMessage], [fmdb lastErrorCode]);
}

BOOL res2 = [fmdb executeUpdate:@"query2"];
if (!res2) {
   NSLog(@"Error %d - %@", [fmdb lastErrorMessage], [fmdb lastErrorCode]);
}

if(!res1 || !res2) [fmdb rollback];
else [fmdb commit];