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
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
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