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 |
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.
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 thatWHENEVER NOT FOUNDtests 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-
SQLCODEis 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).