Wonder how you can implement an automic transaction in SQLite so that your queries will either completely occurs, or completely fails to occur? In this tutorial, i will demonstrate how you can do that in your C++ code by modifying my previous C++ Sqlite example. My previous C++ Sqlite tutorials can be found at the following links: C++ SQLite Example Let’s start by explaining a little bit about the change in the following code snippet:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
void RunSQLTransaction(sqlite3 *db)
{
  if (!db)
    return;

  const char *pSQL[6];
  char *zErrMsg = 0;

  // Create a new myTable in database
  pSQL[0] = "create table myTable (FirstName varchar(30), LastName varchar(30), Age smallint not null)";

  // Insert first data item into myTable
  pSQL[1] = "insert into myTable (FirstName, LastName, Age) values ('Woody', 'Alan', 45)";
 
  // Insert second data item into myTable
  pSQL[2] = "insert into myTable (FirstName, LastName, Age) values ('Micheal', 'Bay', 38)";

  // Select all data in myTable
  pSQL[3] = "select * from myTable";
 
  // start sqlite transaction block
  sqlite3_exec(db, "BEGIN", 0, 0, 0);

  // execute all the sql statements
  for(int i = 0; i < 4; i++)
  {
    int rc = sqlite3_exec(db, pSQL[i], callback, 0, &zErrMsg);
    if( rc!=SQLITE_OK ) {
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);

      // rollback all update/insert to sqlite
      sqlite3_exec(db, "ROLLBACK", 0, 0, 0);

      break; // break the loop if error occur
    }
  }

  // commit all to sqlite
  sqlite3_exec(db, "COMMIT", 0, 0, 0);
}
I’ve created a new function called “RunSQLTransaction” and the only argument it take is the sqlite database instance that was established in the main function. Also, I’ve modified the table field so that the “Age” column will take smallint & is not nullable. Line #22 in the code snippet: It start the SQLite transaction block. It will change the auto-commit of SQLite to manual.
Line #33 in the code snippet: Error(s) detected and all transaction will rollback to its last stable state. No changes to sqlite database at all.
Line #40 in the code snippet: Not error(s) detected and changes will now commit to the database.
If you run the code now, both record in pSQL[1] & pSQL[2] will be inserted into the database. Because both records are meeting the SQL requirement as per defined in its table. However, if we change the record value to the following:
1
2
3
4
5
  // Insert first data item into myTable
  pSQL[1] = "insert into myTable (FirstName, LastName, Age) values ('Woody', 'Alan', 45)";
 
  // Insert second data item into myTable
  pSQL[2] = "insert into myTable (FirstName, LastName, Age) values ('Micheal', 'Bay', null)";
The second record consist of a age value which is null and it’s not acceptable by the table. Try to run the code now and you will observed that no record will be inserted into the table. Although, first record fulfill the requirement, SQLite transaction will still ignore the insertion in order to make sure that the atomic transaction is valid. If you try to remove the following code from the code snippet:
1
2
3
sqlite3_exec(db, "BEGIN", 0, 0, 0);
sqlite3_exec(db, "ROLLBACK", 0, 0, 0);
sqlite3_exec(db, "COMMIT", 0, 0, 0);
Try to run your code with second record which consist of a null age value. You will observed that the first value will be inserted into the database, whereas, the second record will be ignored. Sqlite3Transaction.zip