Using the SQLSTATE Error Status Code

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page SQL Statements > GET DIAGNOSTICS >

Using the SQLSTATE Error Status Code

When an SQL statement executes, an error status code is automatically generated. This code represents success, failure, warning, or no data found. This error status code is stored in a built-in variable called SQLSTATE.

Class and Subclass Codes

The SQLSTATE status code is a five-character string that can contain only digits and uppercase letters.

The first two characters of the SQLSTATE status code indicate a class. The last three characters of the SQLSTATE code indicate a subclass. Figure 1 shows the structure of the SQLSTATE code. This example uses the value 08001, where 08 is the class code and 001 is the subclass code. The value 08001 represents the error unable to connect with database environment.

Figure 1. Structure of the SQLSTATE Code

begin figure description - This figure is described in the surrounding text. - end figure description

The following table is a quick reference for interpreting class code values.

SQLSTATE Class Code Value: Outcome
00: Success
01: Success with warning
02: No data found
> 02: Error or warning

Support for the ANSI/ISO Standard for SQL

All status codes returned to the SQLSTATE variable are ANSI-compliant except in the following cases:

SQLSTATE codes with a class code of 01 and a subclass code that begins with an I are Informix-specific warning messages.
SQLSTATE codes with a class code of IX and any subclass code are Informix-specific error messages.
SQLSTATE codes whose class code begins with a digit in the range 5 to 9 or with an uppercase letter in the range I to Z indicate conditions that are currently undefined by the ANSI/ISO standard for SQL. The only exception is that SQLSTATE codes whose class code is IX are Informix-specific error messages.

List of SQLSTATE Codes

This table describes the class codes, subclass codes, and the meaning of all valid warning and error codes associated with the SQLSTATE variable.

Class	Subclass	Meaning
00	000	Success
01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01	000 002 003 004 005 006 007 I01 I03 I04 I05 I06 I07 I08 I09 I10 I11	Success with warning Disconnect error. Transaction rolled back NULL value eliminated in set function String data, right truncation Insufficient item descriptor areas Privilege not revoked Privilege not granted Database has transactions ANSI-compliant database selected IBM Informix database server selected Float to decimal conversion was used Informix extension to ANSI-compliant syntax UPDATE or DELETE statement does not have a WHERE clause An ANSI keyword was used as a cursor name Cardinalities of the projection list and of the INTO list are not equal Database server running in secondary mode Dataskip is turned on
02	000	No data found
07 07 07 07 07 07 07 07 07	000 001 002 003 004 005 006 008 009	Dynamic SQL error USING clause does not match dynamic parameters USING clause does not match target specifications Cursor specification cannot be executed USING clause is required for dynamic parameters Prepared statement is not a cursor specification Restricted data type attribute violation Invalid descriptor count Invalid descriptor index
08 08 08 08 08 08 08 08	000 001 002 003 004 006 007 S01	Connection exception Database server rejected the connection Connection name in use Connection does not exist Client unable to establish connection Transaction rolled back Transaction state unknown Communication failure
0A 0A	000 001	Feature not supported Multiple server transactions
21 21 21	000 S01 S02	Cardinality violation Insert value list does not match column list Degree of derived table does not match column list
22 22 22 22 22 22 22 22 22 22	000 001 002 003 005 027 012 019 024 025	Data exception String data, right truncation NULL value, no indicator parameter Numeric value out of range Error in assignment Data exception trim error Division by zero (0) Invalid escape character Unterminated string Invalid escape sequence
23	000	Integrity constraint violation
24	000	Invalid cursor state
25	000	Invalid transaction state
2B	000	Dependent privilege descriptors still exist
2D	000	Invalid transaction termination
26	000	Invalid SQL statement identifier
2E	000	Invalid connection name
28	000	Invalid user-authorization specification
33	000	Invalid SQL descriptor name
34	000	Invalid cursor name
35	000	Invalid exception number
37	000	Syntax error or access violation in PREPARE or EXECUTE IMMEDIATE
3C	000	Duplicate cursor name
40 40	000 003	Transaction rollback Statement completion unknown
42	000	Syntax error or access violation
S0 S0 S0 S0 S0	000 001 002 011 021	Invalid name Base table or view table already exists Base table not found Index already exists Column already exists
S1	001	Memory allocation failure
IX	000	Informix reserved error message

Using SQLSTATE in Applications

You can use a built-in variable, called SQLSTATE, which you do not have to declare in your program. SQLSTATE contains the status code, essential for error handling, which is generated every time your program executes an SQL statement. SQLSTATE is created automatically. You can examine the SQLSTATE variable to determine whether an SQL statement was successful. If the SQLSTATE variable indicates that the statement failed, you can execute a GET DIAGNOSTICS statement to obtain additional error information.

For an example of how to use an SQLSTATE variable in a program, see Using GET DIAGNOSTICS for Error Checking.