Checking for Errors

Although the OLAP DML will signal some SQL errors, it does not automatically signal an error when there is an error in a SQL statement. Instead, the OLAP DML provides support to help you handle errors that are returned.

In your programs, you will need to provide the logic for handling SQL errors. The OLAP DML provides two options, SQLCODE and SQLERRM, whose values reflect the SQLCODE and SQLERRM values set in the database.

SQLCODE Option

SQLCODE contains an integer error code number. Your programs should test the value of SQLCODE after every SQL command to make sure that the command executed successfully. You can also test the value of SQLCODE to determine whether you need to break out of a loop. SQLCODE typically has one of the values shown in Table 10-3.

Table 10-3  Values of SQLCODE

Code	Meaning
0 (zero)	The last SQL operation was successful.
100	All requested rows have been fetched.
-1	An error has occurred.
Any value that is not 0 or not 100	An error has occurred.

:

SQLERRM Option

The SQLERRM option contains the error message associated with the current error code. It identifies the condition that caused an error to occur. You can control whether or not this message is sent automatically to the current outfile. When you are debugging a program, you will probably want all SQL error messages sent to the current outfile so that you can see them immediately. However, when your application is in use, you will want to suppress the error messages and handle the error condition in a way more suited to your application.

SQLMESSAGES Option

The SQLMESSAGES option controls whether SQL messages are sent to the current outfile, which is usually the screen. To send SQL messages to the current outfile, issue the following command.

SQLMESSAGES = yes