For more powerful error handling, the embedded SQL interface provides a global variable with the name sqlca (SQL communication area) that has the following group item:
01 SQLCA.
02 SQLCAID PIC X(8).
02 SQLABC PIC S9(9).
02 SQLCODE PIC S9(9).
02 SQLERRM.
03 SQLERRML PIC S9(9).
03 SQLERRMC PIC X(150).
02 SQLERRP PIC X(8).
02 SQLERRD PIC S9(9) OCCURS 6.
02 SQLWARN PIC X(8).
02 SQLSTATE PIC X(5).(In a multithreaded program, every thread automatically gets its own copy of sqlca. This works similarly to the handling of the standard C global variable errno.)
sqlca covers both warnings and errors. If multiple warnings or errors occur during the execution of a statement, then sqlca will only contain information about the last one.
If no error occurred in the last SQL statement, SQLCODE will be 0 and SQLSTATE will be "00000". If a warning or error occurred, then SQLCODE will be negative and SQLSTATE will be different from "00000". A positive SQLCODE indicates a harmless condition, such as that the last query returned zero rows. SQLCODE and SQLSTATE are two different error code schemes; details appear below.
If the last SQL statement was successful, then SQLERRD(2) contains the OID of the processed row, if applicable, and SQLERRD(3) contains the number of processed or returned rows, if applicable to the command.
In case of an error or warning, SQLERRMC will contain a string that describes the error. The field SQLERRML contains the length of the error message that is stored in SQLERRMC (the result of FUNCTION STORED-CHAR-LENGTH. Note that some messages are too long to fit in the fixed-size sqlerrmc array; they will be truncated.
In case of a warning, the 3rd character of SQLWARN is set to W. (In all other cases, it is set to something different from W.) If the 2nd character of SQLWARN is set to W, then a value was truncated when it was stored in a host variable. The 1st character of SQLWARN is set to W if any of the other elements are set to indicate a warning.
The fields sqlcaid, sqlcabc, sqlerrp, and the remaining elements of sqlerrd and sqlwarn currently contain no useful information.
The structure sqlca is not defined in the SQL standard, but is implemented in several other SQL database systems. The definitions are similar at the core, but if you want to write portable applications, then you should investigate the different implementations carefully.
Here is one example that combines the use of WHENEVER and sqlca, printing out the contents of sqlca when an error occurs. This is perhaps useful for debugging or prototyping applications, before installing a more "user-friendly" error handler.
EXEC SQL WHENEVER SQLERROR GOTO PRINT_SQLCA END-EXEC.
PRINT_SQLCA.
DISPLAY "==== sqlca ====".
DISPLAY "SQLCODE: " SQLCODE.
DISPLAY "SQLERRML: " SQLERRML.
DISPLAY "SQLERRMC: " SQLERRMC.
DISPLAY "SQLERRD: " SQLERRD(1) " " SQLERRD(2) " " SQLERRD(3)" " SQLERRD(4) " " SQLERRD(5) " " SQLERRD(6).
DISPLAY "SQLSTATE: " SQLSTATE.
DISPLAY "===============".The result could look as follows (here an error due to a misspelled table name):
==== sqlca ==== sqlcode: -000000400 SQLERRML: +000000064 SQLERRMC: relation "pg_databasep" does not exist (10292) on line 93 sqlerrd: +000000000 +000000000 +000000000 +000000000 +000000000 +000000000 sqlstate: 42P01 ===============