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 VARCHAR(71),
  sqlerrp CHAR(7),
  sqlerrd ARRAY[6] OF INTEGER,
  sqlawarn CHAR(7)
END RECORD
  1. sqlcode contains the SQL execution code ( 0 = OK, 100 = not row found, <0 = error ).
  2. sqlerrm contains the error message parameter.
  3. sqlerrp is not used at this time.
  4. sqlerrd[1] is not used at this time.
  5. sqlerrd[2] contains the last SERIAL or the native SQL error code.
  6. sqlerrd[3] contains the number of rows processed in the last statement (server dependent).
  7. sqlerrd[4] contains the estimated CPU cost for the query (server dependent).
  8. sqlerrd[5] contains the offset of the error in the SQL statement text (server dependent).
  9. sqlerrd[6] contains the ROWID of the last row that was processed (server dependent).
  10. sqlawarn contains the ANSI warning represented by a W character at a given position in the string.
  11. sqlawarn[1] is set to W when any of the other warning characters have been set to W.
  12. sqlawarn[2-7] have specific meanings, see database server documentation for more details.

Usage

The "sqlca" acronym stands for the SQL Communication Area variable.

The sqlca record 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, provided that 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. For example, after inserting a row in a table with a SERIAL column, sqlca.sqlerrd[2] will contain the new generated serial number. After an SQL error occurred, sqlca.sqlerrd[2] will contain the native SQL error. Furthermore, the sqlca.sqlerrd[3] member may be set with the number of processed rows, as long as the database client supports this feature. Other sqlca.sqlerrd[n] members must be considered as non portable.

Example

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