Transaction logging

The best way to prepare for any kind of error during a modification is to use transaction logging. In the event of an error, you can tell the database server to put the database back the way it was. The following example is based on the example in the section Direct deletions, which is extended to use transactions:

EXEC SQL begin work;                 /* start the transaction*/
EXEC SQL delete from items
     where order_num = :onum;
del_result = sqlca.sqlcode;          /* save two error */
del_isamno = sqlca.sqlerrd[1];       /* code numbers */
del_rowcnt = sqlca.sqlerrd[2];       /* and count of rows */
if (del_result < 0)                  /* problem found: */
   EXEC SQL rollback work;           /* put everything back */
else                                 /* everything worked OK:*/
   EXEC SQL commit work;             /* finish transaction */

A key point in this example is that the program saves the important return values in the sqlca structure before it ends the transaction. Both the ROLLBACK WORK and COMMIT WORK statements, like other SQL statements, set return codes in the sqlca structure. However, if you want to report the codes that the error generated, you must save them before executing ROLLBACK WORK. The ROLLBACK WORK statement removes all of the pending transaction, including its error codes.

The advantage of using transactions is that the database is left in a known, predictable state no matter what goes wrong. No question remains about how much of the modification is completed; either all of it or none of it is completed.

In a database with logging, if a user does not start an explicit transaction, the database server initiates an internal transaction prior to execution of the statement and terminates the transaction after execution completes or fails. If the statement execution succeeds, the internal transaction is committed. If the statement fails, the internal transaction is rolled back.