The SQLCA diagnostic record

The SQLCA variable is a predefined record containing SQL statement execution information.

The SQLCA record definition

The SQLCA record is defined as follows:

DEFINE SQLCA RECORD
  SQLCODE INTEGER,
  SQLERRM CHAR(71),
  SQLERRP CHAR(8),
  SQLERRD ARRAY[6] OF INTEGER,
  SQLAWARN CHAR(7)
END RECORD

SQLCODE contains the SQL execution code ( 0 = OK, 100 = not row found, <0 = error ).
SQLERRM contains the error message parameter.
SQLERRP is not used at this time.
SQLERRD[1] is not used at this time.
SQLERRD[2] contains the last SERIAL or the native SQL error code.
SQLERRD[3] contains the number of rows processed in the last statement (server dependent).
SQLERRD[4] contains the estimated CPU cost for the query (server dependent).
SQLERRD[5] contains the offset of the error in the SQL statement text (server dependent).
SQLERRD[6] contains the ROWID of the last row that was processed (server dependent).
SQLAWARN contains the ANSI warning represented by a W character at a given position in the string.
SQLAWARN[1] is set to W when any of the other warning characters have been set to W.
SQLAWARN[2-7] have specific meanings, see database server documentation for more details.

Usage

SQLCA stands for SQL Communication Area variable.

The SQLCA can be used to get an SQL execution diagnostic. Error and warning information can be found in this structure.

The SQLCA record is filled after each SQL statement execution.

SQLCA is not designed to be modified by user code, it must be used as a read-only record.

Portability

SQLCA.SQLCODE will be set to a specific IBM^® Informix^® SQL error code, if the database driver can convert the native SQL error to an IBM Informix SQL error. In case of error, SQLCA.SQLERRD[2] will hold the native SQL error produced by the database server.

Other SQLCA record members are specific to IBM Informix databases. For example, after inserting a row in a table with a SERIAL column, SQLCA.SQLERRD[2] contains the new generated serial number.

The SQLCA.SQLERRD[3] member may be set, if supported by the database server and database client API.

Table 1. SQLCA.SQLERRD[n] member support per database server type
Database Server Type	SQLERRD[2] (native error)	SQLERRD[2] (last serial)	SQLERRD[3] (processed rows)
Genero db	Yes	Yes	Yes
IBM DB2^® UDB	Yes	Yes	Yes
IBM Informix	Yes	Yes	Yes
Microsoft™ SQL Server	Yes	Yes	Yes
MySQL	Yes	Yes	Yes
Oracle Database Server	Yes	Yes	Yes
PostgreSQL	Yes	Yes	Yes
Sybase ASE	Yes	Yes	Yes

SQLCA.SQLERRD[n] members not listed in this table must be considered as not portable.

MAIN
  WHENEVER ERROR CONTINUE
  DATABASE stores 
  SELECT COUNT(*) FROM foo   -- Table should not exist!
  DISPLAY SQLCA.SQLCODE, SQLCA.SQLERRD[2]
END MAIN