You are here:

Error Handling With SQLCA

SQLCA is a structure (collection of variables) that is updated after each SQL statement executes and can be used for error processing.

Record definition:

DEFINE sqlca RECORD
    sqlcode INTEGER,
    sqlerrm STRING,
    sqlerrd ARRAY[6] OF INTEGER,
    sqlawarn CHAR(8)
  END RECORD

Field	Type	Value	Value Description
sqlcode	INT	0	SQL statement executed successfully
		>=0, < 100	After a DESCRIBE statement, represents the type of SQL statement that is described.
		100	A not found result (for ANSI-compliant databases). NOT FOUND can also occur in an ANSI-compliant database after an INSERT INTO / SELECT, UPDATE, DELETE, or SELECT... INTO TEMP statement fails to access any rows. For non-ANSI-compliant databases, the value 100 will be returned for the following: FETCH statement: the last qualifying row has already been returned (the end of data was reached). SELECT statement: no rows match the SELECT criteria. For non-ANSI-compliant databases, errors after other statements will return 0.
		<0	Error of a statement execution. The number will indicate the error. For the error codes and descriptions, refer here: Informix Error Messages.
sqlerrm	STRING		This parameter is used to replace a %s token in the actual error message. If an error message requires no parameter, this field is blank.
sqlerrd	ARRAY[6] OF INTEGER	[0]	After a successful PREPARE statement for a SELECT, UPDATE, INSERT, or DELETE statement, or after a select cursor is opened, this field will show the estimated number of rows affected.
		[1]	If the sqlcode field has an error code, the sqlerrd[1] will contain zero or an additional error code. After a successful insert operation of a single row, this field contains the value of any SERIAL value generated for that row.
		[2]	After a successful multirow insert, update, or delete operation, this field will contain the number of rows that were processed. If a multirow insert, update, or delete operation that ends with an error, this field contains the number of rows that were successfully processed before the error was detected.
		[3]	After a successful PREPARE statement for a SELECT, UPDATE, INSERT, or DELETE statement, or after a select cursor was opened, this field contains the estimated weighted sum of disk accesses and total rows processed.
		[4]	If there's a syntax error in a PREPARE, EXECUTE IMMEDIATE, DECLARE, or a static SQL statement, this field contains the offset in the statement text where the error was detected.
		[5]	After a successful fetch of a selected row, or a successful insert, update, or delete operation, this field contains the rowid (physical address) of the last row that was processed. Depending on how the database server processes a query, the rowid value may correspond to the row that the database server returns to the user (for the SELECT statements in particular).
sqlwarn	CHAR(8)	sqlwarn0	Set to W when any other warning field is set to W. If this field is blank, other fields do not need to be checked.
		sqlwarn1	At database opening: Is set to W when the database now open uses a transaction log. Other operations: Is set to W if a column value is truncated when it is fetched into a host variable with a FETCH or a SELECT...INTO statement. On a REVOKE ALL statement, set to W when not all seven table-level privileges are revoked.
		sqlwarn2	At database opening: Is set to W when the database now open is ANSI-compliant. Other operations: Set to W when a FETCH or SELECT statement returns null value of the aggregate function (SUM, AVG, MIN, MAX).
		sqlwarn3	At database opening: Is set to W if the database is the IBM Informix Dynamic Server. Other operations: On a SELECT...INTO, FETCH...INTO, or EXECUTE...INTO statement, set to W when the number of items in the select list is not the same as the number of host variables given in the INTO clause to receive them. On a GRANT ALL statement, set to W when not all seven table-level privileges are granted.
		sqlwarn4	At database opening: Is set to W when the database server stores the FLOAT data type in DECIMAL form (done when the host system lacks support for FLOAT data types). Other operations: Set to W after a DESCRIBE statement if the prepared statement contains a DELETE statement or an UPDATE statement without a WHERE clause.
		sqlwarn5	Other operations: Set to W following execution of a statement that does not use ANSI-standard SQL syntax.
		sqlwarn6	At database opening: Is set to W when the application is connected to a database server that is running in secondary mode. The database server is a secondary server in a data-replication pair (the database server is available only for read operations). Other operations: Set to W when a data fragment (a dbspace) has been skipped during query processing (when the DATASKIP feature is on).
		sqlwarn7	At database opening: Set to W when client DB_LOCALE does not match the database locale.