Error Handling › Common Error Subroutines › Using SQLCA for SQL

Using SQLCA for SQL

When the FOR construct accesses SQL objects, the CA Ideal error procedure processing is invoked. If the $ERROR-TYPE value is DB2 or SQL, you can evaluate the SQLCODE from the SQLCA with the CA Ideal error procedure. For example, an error procedure for DB2 can contain:

SELECT $ERROR-TYPE
    WHEN 'DB2'
        SET $RC = $SQLCODE
        LIST ERROR
        LIST $SQL-LAST-STMT 
        BACKOUT
        QUIT RUN
    . . .

$SQL-LAST-STMT returns the statement number and program name of the last statement executed.

When using SQL to access the database, be aware that errors encountered in SQL do not invoke execution of the CA Ideal error procedures. Code the necessary SQL, as in:

EXEC SQL
   WHENEVER SQLERROR DO SQL-ERROR
END-EXEC

You can code the error procedure named SQL-ERROR as:

<<SQL-ERROR>> PROCEDURE
    SET $RC = $SQLCODE
    LIST $SQL-LAST-STMT 
    BACKOUT
    QUIT RUN
ENDPROC