Error handling and diagnostics

The SQL Communication Area

The SQLCA is a data structure updated by each executable SQL statement. It is included in the WORKING-STORAGE SECTION with:

           EXEC SQL INCLUDE SQLCA END-EXEC.

Its main fields are:

Field Description
SQLCODE 0 on success, a positive value for warnings and “no record”, a negative value for errors
SQLSTATE the 5-character SQLSTATE code of the SQL standard
SQLERRML, SQLERRMC length and text of the error message
SQLERRD(3) number of rows processed
SQLWARN0 … warning flags
NoteTo be completed

Copy the actual layout of copy/SQLCA.cpy.

SQLCODE and SQLSTATE

When the database reports an error, GixSQL sets SQLCODE to the error code of the database when there is one (for example -942 for Oracle’s ORA-00942) and SQLSTATE to the corresponding SQLSTATE. When the error comes from GixSQL itself, SQLCODE is one of the negative codes listed in Error codes.

NoteTo be verified

SQLCODE mapping for each driver (PostgreSQL has no numeric codes).

The “no record” condition

When a SELECT INTO finds no row, or a FETCH reaches the end of a cursor, SQLSTATE is 02000 and SQLCODE is 100. Oracle uses 1403 instead of 100; to get this value:

  • precompile with --no-rec-code 1403, so that WHENEVER NOT FOUND tests this value;
  • run with GIXSQL_NOREC_CODE=1403, so that the runtime sets it.

Both settings must be consistent.

WHENEVER

The WHENEVER statement tells the precompiler to generate a test after each following SQL statement, up to the next WHENEVER for the same condition:

           EXEC SQL WHENEVER SQLERROR GO TO SQL-ERROR END-EXEC.
           EXEC SQL WHENEVER NOT FOUND GO TO END-OF-FETCH END-EXEC.
           EXEC SQL WHENEVER SQLWARNING CONTINUE END-EXEC.

Conditions:

SQLERROR
SQLCODE is negative;
NOT FOUND
the “no record” condition;
SQLWARNING
a warning is set.

Actions:

CONTINUE
no test (default);
GO TO label, GOTO label
jump to the paragraph or section label;
PERFORM label
perform the paragraph or section label.

WHENEVER is a declaration: it applies to the statements that follow it in the source, not in the execution order.

Logging

When an error is not clear from the SQLCA, the runtime log gives the details of each call, including the SQL statements sent to the database (see Logging).