database - SQLite 3 C API Transactions

Question

I am using SQLite3 for iPhone development and I'm attempting to wrap a few insert statements into a transaction. Currently I have the below code which is working properly however after reading another question on SO I realized it would be better to have these in one transaction rather than one each. I couldn't find the C API call to begin and commit a transaction. Some of the code is in Objective-C but I don't think that's really relevent to the question.

- (void)saveAnimals {
    //Insert all the animals into the zoo database
    int i;

    const char *sql = "insert into Animal(Zoo_ID, Animal_Num, Animal_Text) Values(?, ?, ?)";
    for (i = 0; i < ([[self animalArray] count] - 1); i++) {

        if(addStmt == nil) {
            if(sqlite3_prepare_v2(database, sql, -1, &addStmt, NULL) != SQLITE_OK)
                NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));
        }
        int animalNum = [[[self animalArray] objectAtIndex:i] animal_Number];
        NSString *animalText = [[NSString alloc] initWithString:[[[self animalArray] objectAtIndex:i] animal_Text]];
        sqlite3_bind_int(addStmt, 1, zoo_ID);   
        sqlite3_bind_int(addStmt, 2, animalNum);    
        sqlite3_bind_text(addStmt, 3, [animalText UTF8String], -1, SQLITE_TRANSIENT);
        [animalText release];
        if(SQLITE_DONE != sqlite3_step(addStmt)) {
            NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));
        }

        //Reset the add statement.
        sqlite3_reset(addStmt);     
    }
}

What I think needs to be done would be taking the sqlite3_prepare_v2 command out of the for loop, start the transaction, go through the for loop, commit the transaction. However, I'm not sure what the calls for "start the transaction" and "commit the transaction" are. And would I still use sqlite3_step? Thanks for your help.

Answer 1

Start a transaction with: sqlite3_exec(db, "BEGIN", 0, 0, 0);

Commit a transaction with: sqlite3_exec(db, "COMMIT", 0, 0, 0);