Adding an Explicit Error Checking Routine

In most applications, it is useful to include a general-purpose exception handling routine to check the result of an SQL statement and to display ALLBASE/SQL error messages if something unexpected happens. ALLBASE/SQL uses the convention of setting the value of SQLCODE to zero when no error occurs, to 100 when no rows were found in a query, or to a negative number whenever an error or serious problem occurs in any SQL statement. You can build a routine that tests SQLCODE following each SQL statement, and you can use the SQLEXPLAIN statement to display error messages when SQLCODE is negative. The following sections show an example.

Coding the Routine

Add the following procedure at the end of your source file:

   /********Insert Status Check routine here***************/



          S100-SQL-STATUS-CHECK.



              EXEC SQL

                  SQLEXPLAIN :SQLMESSAGE

              END-EXEC.



              DISPLAY SQLMESSAGE.



          S100-EXIT.

              EXIT.



   /*******************************************************/

This routine displays the content of the message buffer when SQLCODE is negative. The routine looks up the number in the message catalog, and the message is placed into the host variable :SQLMESSAGE, which you then display to the user. The routine is called in a loop, since more than one error or warning may be returned following the execution of an SQL statement.

Declare SQLMESSAGE along with the other host variables inside the host variable declare section:

          01  SQLMESSAGE              PIC X(256).

Add the following code to test the value of SQLCODE before calling B200-SELECT-TITLE:

          IF SQLCODE = 100 THEN

             DISPLAY "Album Code not in Table."

             GO TO B100-EXIT.

          IF SQLCODE < 0 THEN

             PERFORM S100-SQL-STATUS-CHECK UNTIL SQLCODE = 0

             GO TO B100-EXIT.

          PERFORM B200-SELECT-TITLE THRU B200-EXIT.

After including these lines in your code, preprocess and compile again.

Creating an Error Condition for the Application

Before you can test the S100-SQL-STATUS-CHECK routine, you need to create an error condition that can be reported by the application. Use the following procedure to create the conditions for a runtime error:

Run ISQL.
Connect to MUSICDBE.

Issue the following statements:

   isql=> DROP INDEX AlbCodeIndex;

   isql=> INSERT INTO Albums VALUES

   > (3001, 'Songs', 'cd', 12.95,

   > 'CBS',NULL,NULL,NULL);

   isql=> INSERT INTO Albums VALUES

   > (3001, 'Ballads', 'ca', 10.95,

   > 'RCA',NULL,NULL,NULL);

   isql=> COMMIT WORK;

Exit ISQL.

You create the conditions for a runtime error by dropping the unique index on the Albums table, then entering duplicate key values (two rows with an AlbumCode of 3001) into the table. Inserting a duplicate key value (3001) will result in an error in the SELECT statement in the application, since a SELECT with an INTO clause requires a single-row query result.

Testing the Error Checking Routine

Run your application, using 3001 as the album code. Since the application expects only a single row to be returned for the AlbumCode key, an error results, and the application displays the following SQL message:
More than one row qualifies for SELECT INTO. (DBERR 10002)
Run your application again, using 4001 as the album code. ALLBASE/SQL returns an SQLCODE value of 100, and you see the following message (coded in your program):
Album Code not in Table.




	NOTE: This exercise illustrates why it is worthwhile to enforce the uniqueness of a key value with an index. A unique constraint prevents the kind of error that was artificially created in the application. To reinstate uniqueness, delete the rows that contain AlbumCode 3001 in the Albums table, then recreate AlbCodeIndex as a unique index on the Albums table.

Adding an Explicit Error Checking Routine

Technical documentation

» Table of Contents

» Index

Coding the Routine

Creating an Error Condition for the Application

Testing the Error Checking Routine