Purposes of Status Checking

Handling Runtime Errors and Warnings

A program is said to be robust if it anticipates common runtime errors and handles them gracefully. In online applications, robust programs may allow the user to decide what to do when an error occurs rather than just terminating. This approach is useful, for example, when a deadlock occurs.

If a deadlock occurs, sqlcode is set to -14024 and an SQLEXPLAIN call retrieves the following message:

   Deadlock detected.  (DBERR 14024)

ALLBASE/SQL rolls back the transaction containing the SQL command that caused the deadlock. You may want to either give the user the option of restarting the transaction, automatically re-execute the transaction a finite number of times before notifying the user of the deadlock, or re-execute the transaction until the deadlock is resolved.

Maintaining Data Consistency

Two or more data values, rows, or tables are said to be consistent if they agree in some way. Changes to such interdependent values are either committed or rolled back at the same time in order to retain data consistency. In other words, the set of operations that form a transaction are considered as an atomic operation; either all or none of the operations are performed on the database. Status checking in this case determines whether to commit or roll back work.

For example, in the sample database (SampleDBE), each order is defined by rows in two tables: one row in the PurchDB.Orders table and one or more rows in the PurchDB.OrderItems table. A transaction that deletes orders from the database has to delete all the rows for a specific order from both tables to maintain data consistency. A program containing such a transaction should commit work to the database only if it is able to delete the row from the PurchDB.Orders table and delete all the rows for the same order from the PurchDB.OrderItems table:

   EXEC SQL BEGIN WORK;

   EXEC SQL DELETE FROM PurchDB.OrderItems

                  WHERE OrderNumber = :OrderNumber;



       If this command succeeds, the program

       submits the following command.



   EXEC SQL DELETE FROM PurchDB.Orders

                  WHERE OrderNumber = :OrderNumber;



     If this command succeeds, the program

       submits a COMMIT WORK command.  If this

       command does not succeed, the

       program submits a ROLLBACK WORK command.

       This ensures that the previous delete won't

       remove part of the information about this

       order when the rest of the information could not

       be deleted.

Checking the Most Recently Executed Command

Depending on which ALLBASE/SQL command was most recently executed, you can make checks to insure that the command executed in a manner appropriate to the program's context. The following section, "Using the sqlca," gives explanations based on each sqlca element. Later in this chapter, the section "Explicit Status Checking Techniques" provides examples based on specific programming tasks.