There are times when you need to execute a series of SQL statements in order to achieve data consistency. For example, a customer is going to transfer his money from bank account to settle a bill payment. We will need to update both customer bank account and payee account accordingly. Failure to update either custormer account or payee account will cause data inconsistent. A transaction can be constructed to avoid data inconsistency caused by such failure. A transaction is a set of one or more statements that is executed as a unit, so either all of the statements are executed, or none of the statements is executed. This tutorial will guide you how you can implement transaction in the C++ project that was introduced in my previous tutorial. To visit the tutorial, please visit the following link. C++ PostgreSQL Example To begin this tutorial, let take a look of the modified code in the main function from the C++ PostgreSQL Example. The self-explain comments in the C++ source code should tell how and what its code are doing. Recall the C++ PostgreSQL Example in my previous tutorial, the program will create an employee table with 2 columns (Fname & Lname). Both with the length of 30 characters. Let’s assume that Mario cannot survive without Daniel in this example code! 😛 Failure to insert either Mario or Daniel into the employee table will cause data inconsistent.
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
int _tmain(int argc, _TCHAR* argv[])
{
  PGconn     *conn = NULL;

  conn = ConnectDB();
  CreateEmployeeTable(conn);

  // start the transaction block
  PGresult *res = PQexec(conn, "BEGIN");

  bool bCheker1 = InsertEmployeeRec(conn, "Mario", "Hewardt");
  bool bCheker2 = InsertEmployeeRec(conn, "Daniel", "Pravat");

  // check if there is any failure to update
  if( bCheker1 && bCheker2)
  {
    // Commit the update to database
    res = PQexec(conn, "COMMIT");

    FetchEmployeeRec(conn);

    printf("\nPress ENTER to remove all records & table.....\n");

    RemoveAllEmployeeRec(conn);
  }
  else
  {
    // withdraw the update to database
    res = PQexec(conn, "ROLLBACK");
    printf ("Trasaction failed. Rollback now!\n");
  }

  DropEmployeeTable(conn);
  PQclear(res);
  CloseConn(conn);
  return 0;
}
If we run the code now, it will insert both Mario and Daniel into the employee table successfully. Both of their name are within length of 30 characters. However, if we change Daniel name to the following which is more than 30 characters:
1
2
bool bCheker1 = InsertEmployeeRec(conn, "Mario", "Hewardt");
bool bCheker2 = InsertEmployeeRec(conn, "Daniel_asdldalskdjlajdasoudiajdaldasldklajdasdasdd", "Pravat");
If we run the code now, neither will insert into the employee table. Although Mario name is within 30 character, the transaction will treat both query as a single unit. Either failure will not insert its data into the table. Download sample source code: PSQLTest.zip