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 sqlca.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 sqlca.sqlcode following each SQL statement, and you can use the SQLEXPLAIN statement to display error messages when sqlca.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 Procedure here***************/



   int SQLStatusCheck()

   {

   while(sqlca.sqlcode < 0) {

      EXEC SQL SQLEXPLAIN :SQLMessage;

      printf("%s\n", SQLMessage);

      }

   } 



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

This routine displays the content of the message buffer when sqlca.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. This is done 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:
char SQLMessage[256];

Add the following code to test the value of sqlca.sqlcode before calling the SelectTitles procedure (the SelectTitles call now appears in an else clause):

   if(sqlca.sqlcode == 100) 

      printf("Album Code not in table.\n");

   else if(sqlca.sqlcode<0) 

      SQLStatusCheck();

   else SelectTitles();

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

Creating an Error Condition for the Application

Before you can test the SQLStatusCheck 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 AlbumIndex;

   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, using 4001 as the album code. When you run the application with the AlbumCode of 4001, ALLBASE/SQL returns an sqlca.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 AlbumIndex 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