Important: SQLSTATE is a more effective way to detect and handle error messages than the SQLCODE variable because SQLSTATE supports multiple exceptions. SQLSTATE is also more portable because it conforms to ANSI and X/Open standards. ESQL/C supports the sqlca structure and SQLCODE for backward compatibility and for Informix-specific exceptions. (For more information, see page 11-27.)

After the database server executes an SQL statement, it sets SQLSTATE with a value that indicates the success or failure of the statement. From this value, your program can determine if it needs to perform further diagnostics. If SQLSTATE indicates a problem, you can use the GET DIAGNOSTICS statement to obtain more information.

This section describes how to use the SQLSTATE variable and the GET DIAGNOSTICS statement to perform exception handling. It describes the following topics:

• Using the GET DIAGNOSTICS statement to access fields of the diagnostics area
• Understanding the format of the SQLSTATE values
• Using SQLSTATE to check for the different types of exceptions

The GET DIAGNOSTICS statement returns information that is held in the fields of the diagnostics area. The diagnostics area is an internal structure that the database server updates after it executes an SQL statement. Each application has one diagnostics area. Although GET DIAGNOSTICS accesses the diagnostics area, it never changes the contents of this area.

To access a field in the diagnostics area, supply a host variable to hold the value and the field keyword to specify the field that you want to access:

:host_var = FIELD_NAME

The fields of the diagnostics area fall into two categories:

• Statement information describes the overall result of the SQL statement, in particular the number of rows that it has modified and the number of exceptions that result.
• Exception information describes individual exceptions that result from the SQL statement.

EXEC SQL get diagnostics statement_fields;

Figure 11-1
Statement Information from the GET DIAGNOSTICS Statement

Field-Name Keyword	ESQL/C Data Type	Description
NUMBER	int	This field holds the number of exceptions that the diagnostics area contains for the most-recently executed SQL statement. NUMBER is in the range of 1 to 35,000. Even when an SQL statement is successful, the diagnostics area contains one exception.
MORE	char[2]	This field holds either an N or a Y (plus a null terminator). An N character indicates that the diagnostics area contains all of the available exception information. A Y character indicates that the database server has detected more exceptions than it could store in the diagnostics area. At present, the database server always returns an N because the database server can store all exceptions.
ROW_COUNT	int	When the SQL statement is an INSERT, UPDATE, or DELETE, this field holds a numeric value that specifies the number of rows that the statement has inserted, updated, or deleted. ROW_COUNT is in the range of 0 to 999,999,999. For any other SQL statement, the value of ROW_COUNT is undefined.

Figure 11-2 shows a GET DIAGNOSTICS statement that retrieves statement information for a CREATE TABLE statement into the host variables :exception_count and :overflow.

EXEC SQL BEGIN DECLARE SECTION; int exception_count; char overflow[2]; EXEC SQL END DECLARE SECTION; ... EXEC SQL create database db; EXEC SQL create table tab1 (col1 integer); EXEC SQL get diagnostics :exception_count = NUMBER, :overflow = MORE;

Figure 11-2 Using GET DIAGNOSTICS to Return Statement Information

Use the statement information to determine how many exceptions the most-recently executed SQL statement has generated. For more information, see "Multiple Exceptions".

For more information on the statement fields of the diagnostics area, see "The Statement Clause" in the GET DIAGNOSTICS statement in the Informix Guide to SQL: Syntax.

Exception Information

EXEC SQL get diagnostics exception except_num exception_fields;

Figure 11-3 summarizes the exception_fields of the diagnostics area.

Figure 11-3
Exception Information from the GET DIAGNOSTICS Statement

(1 of 2)

Field Name Keyword	ESQL/C Data Type	Description
RETURNED_SQLSTATE	char[6]	This field holds the SQLSTATE value that describes the current exception. For information about the values of this field, see "Using the SQLSTATE Variable".
INFORMIX_SQLCODE	int	This field holds the Informix-specific status code. This code is also available in the global SQLCODE variable. For more information, see "Using the SQLCODE Variable".
CLASS_ORIGIN	char[255]	This field holds a variable-length character string that defines the source of the class portion of SQLSTATE. If Informix defines the class code, the value is "IX000". If the International Standards Organization (ISO) defines the class code, the value of CLASS_ORIGIN is "ISO 9075". If a user-defined routine has defined the message text of the exception, the value of CLASS_ORIGIN is "U0001".
SUBCLASS_ORIGIN	char[255]	This field holds a variable-length character string that contains the source of the subclass portion of SQLSTATE. If ISO defines the subclass, the value of SUBCLASS_ORIGIN is "ISO 9075". If Informix defines the subclass, the value is "IX000". If a user-defined routine has defined the message text of the exception, the value is "U0001".
MESSAGE_TEXT	char[8191]	This field holds a variable-length character string that contains the message text to describe this exception. This field can also contain the message text for any ISAM exceptions or a user-defined message from a user-defined routine.
MESSAGE_LENGTH	int	This field holds the number of characters that are in the text of the MESSAGE_TEXT string.
SERVER_NAME	char[255]	This field holds a variable-length character string that holds the name of the database server that is associated with the actions of a CONNECT or DATABASE statement. This field is blank when no current connection exists. For more information about the SERVER_NAME field, see "Identifying an Explicit Connection".
CONNECTION_NAME	char[255]	This field holds a variable-length character string that holds the name of the connection that is associated with the actions of a CONNECT or DATABASE statement. This field is blank when no current connection exists. Otherwise, it contains the name of the last successfully established connection. For more information about the CONNECTION_NAME field, see "Identifying an Explicit Connection".

Use the exception information to save detailed information about an exception. The code fragment in Figure 11-4 retrieves exception information on the first exception of a CREATE TABLE statement.

EXEC SQL BEGIN DECLARE SECTION; char class_origin_val[255]; char subclass_origin_val[255]; char message_text_val[8191]; int messlength_val; EXEC SQL END DECLARE SECTION; EXEC SQL create database db; EXEC SQL create table tab1 (col1 integer); EXEC SQL get diagnostics exception 1 :class_origin_val = CLASS_ORIGIN, :subclass_origin_val = SUBCLASS_ORIGIN, :message_text_val = MESSAGE_TEXT, :messlength_val = MESSAGE_LENGTH;

Figure 11-4 Example of Using GET DIAGNOSTICS to Return Exception Information

For more information on the exception fields, see the GET DIAGNOSTICS statement in the Informix Guide to SQL: Syntax.

Using the SQLSTATE Variable

After the database server executes an SQL statement, the database server automatically updates the SQLSTATE variable as follows:

• The database server stores the exception value in the RETURNED_SQLSTATE field of the diagnostics area.
• ESQL/C copies the value of the RETURNED_SQLSTATE field to the global SQLSTATE variable.

EXEC SQL get diagnostics exception 1 :SQLSTATE = RETURNED_SQLSTATE;

Tip: At runtime, ESQL/C automatically copies the value of the RETURNED_SQLSTATE field into the global SQLSTATE variable. Therefore, you do not usually need to access the RETURNED_SQLSTATE field directly. For more information, see "Multiple Exceptions".

The SQLSTATE variable holds the ANSI-defined value for the exception. Each SQLSTATE value has an associated Informix-specific status code. You can obtain the value of this Informix-specific status code from either of the following:

• The INFORMIX_SQLCODE field of the diagnostics area
• The SQLCODE variable (page 11-32)

• How to use the class and subclass portions of the SQLSTATE value
• The list of exception values that SQLSTATE can store

SQLSTATE can contain only digits and capital letters. The class code is unique but the subclass code is not. The meaning of the subclass code depends on the associated class code. The initial character of the class code indicates the source of the exception code, which Figure 11-6 summarizes.

Figure 11-6
Initial SQLSTATE Class-Code Values

Initial Class-Code Value	Source of Exception Code	Notes
0 to 4 A to H	X/Open and ANSI/ISO	The associated subclass codes also begin in the range 0 to 4 or A to H.
5 to 9	Defined by the implementation	Subclass codes are also defined by the implementation.
I to Z	INFORMIX-Universal Server INFORMIX-ESQL/C	Any of the Informix-specific error messages (those that the X/Open or ANSI/ISO reserved range does not support) have an SQLSTATE value of "IX000". If a user-defined routine returns an error message that has been defined by the routine, the SQLSTATE value is "U0001".

List of SQLSTATE Class Codes

Figure 11-7
Class and Subclass Codes for SQLSTATE

(1 of 4)

Class	Subclass	Meaning
00	000	Success
01 01 01 01 01 01 01	000 002 003 004 005 006 007	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
01 01 01 01 01 01 01 01 01 01 01	I01 I03 I04 I05 I06 I07 I08 I09 I10 I11 U01	Database has transactions ANSI-compliant database selected Database on any Database server other than INFORMIX-SE selected Float to decimal conversion used Informix extension to ANSI-compliant standard syntax After a DESCRIBE, a prepared UPDATE/DELETE statement does not have a WHERE clause An ANSI keyword has been used as cursor name Number of items in select list is not equal to number of items in INTO list Database server is running in secondary mode DATASKIP feature is turned on User-defined warning returned by a user-defined routine
02	000	No data found or End of data reached
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 database 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 012 019 024 025 027	Data exception String data, right truncation Null value, no indicator parameter Numeric value out of range Error in assignment Division by zero Invalid escape character Unterminated string Invalid escape sequence Data exception trim error
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 error message
IX	000	Informix reserved error message
U0	001	User-defined error returned by a user-defined routine

The ANSI or X/Open standards define all SQLSTATE values except the following:

• A "IX000" runtime error indicates an Informix-specific error message.
• A "U0001" runtime error indicates a user-defined error message.
• The "01Ixx" warnings indicate Informix-specific warnings.
• The "01U01" warning indicates a user-defined warning message.

Checking for Exceptions with SQLSTATE

Figure 11-8
Exceptions That SQLSTATE Returns

Exception Condition	SQLSTATE Value
Success	"00000"
Success, but no rows found	"02000"
Success, but warnings generated	Class code = "01" Subclass code = "000" to "006" (for ANSI and X/Open warnings) Subclass code = "I01" to "I11" (for Informix-specific warnings) Subclass code = "U01" (for user-defined warnings)
Failure, runtime error generated	Class code > "02" (for ANSI and X/Open errors) Class code = "IX" (for Informix-specific errors) Class code = "U0" (for user-defined errors)

For a general introduction to these four conditions, see "Types of Diagnostic Information". To determine the cause of an exception in SQLSTATE, use the GET DIAGNOSTICS statement.

To determine the cause of an exception in SQLSTATE

2. For each exception, use the EXCEPTION clause of GET DIAGNOSTICS to obtain detailed information about the exception.

Success in SQLSTATE

Tip: After a CONNECT, SET CONNECTION, DATABASE, CREATE DATABASE, or START DATABASE statement, the SQLSTATE variable has a class value of "01" and an Informix-specific subclass value to provide information about the database and connection. For more information, see Figure 11-10.

The getdiag sample program on page 11-57 uses the sqlstate_err() function to compare the first two characters of SQLSTATE with the string "00" to check for successful execution of an SQL statement. The sqlstate_exception() function shown in Figure 11-19 checks for a success in SQLSTATE with the system strncmp() function.

NOT FOUND in SQLSTATE

Figure 11-9
SQLSTATE Values That Are Set When SQL Statements Do Not Return Any Rows

(1 of 2)

SQL Statement That Generates the Indicated SQLSTATE Result	Result for ANSI-Compliant Database	Result for non-ANSI-Compliant Database
FETCH statement: the last qualifying row has already been returned (the end of data has been reached).	"02000"	"02000"
SELECT statement: no rows match the SELECT criteria.	"02000"	"02000"
DELETE and DELETE...WHERE statement (not part of multistatement PREPARE): no rows match the DELETE criteria.	"02000"	"00000"
INSERT INTO tablename SELECT statement (not part of multistatement PREPARE): no rows match the SELECT criteria.	"02000"	"00000"
SELECT... INTO TEMP statement (not part of multistatement PREPARE): no rows match the SELECT criteria.	"02000"	"00000"
UPDATE and UPDATE...WHERE statement (not part of multistatement PREPARE): no rows match the UPDATE criteria.	"02000"	"00000"

Figure 11-9 shows that the value that the NOT FOUND condition generates depends, in some cases, on whether the database is ANSI compliant.

To check for the NOT FOUND condition, your code needs to verify only
the class code of SQLSTATE. The subclass code is always "000". The
getdiag sample program on page 11-57 uses the sqlstate_err() function to
perform exception handling. To check for a warning in an SQL statement, sqlstate_err() compares the first two characters of SQLSTATE with the
string "02".

Warnings in SQLSTATE

• An ANSI or X/Open warning message has a subclass code in the range "000" to "006".

• An Informix-specific warning message has a subclass code in the range "I01" to "I11" (see Figure 11-10).

• A user-defined warning message from a user-defined routine has a subclass code of "U01".

Figure 11-10 lists the Informix-specific warning messages and the SQL statements and conditions that generate the warning.

Figure 11-10
SQL Statements That Set an Informix-Specific Warning for a Given Condition

(1 of 2)

Warning Value	SQL Statement	Warning Condition
"01I01"	CONNECT CREATE DATABASE DATABASE SET CONNECTION	Your application opened a database that uses transactions.
"01I03"	CONNECT CREATE DATABASE DATABASE SET CONNECTION	Your application opened an ANSI-compliant database.
"01I04"	CONNECT CREATE DATABASE DATABASE SET CONNECTION	Your application opened a database that uses the Universal Server or OnLine database server.
"01I05"	CONNECT CREATE DATABASE DATABASE SET CONNECTION	Your application opened a database that is on a host database server that requires float-to-decimal conversion for FLOAT columns (or smallfloat-to-decimal conversions for SMALLFLOAT columns).
"01I06"	All statements	The statement just executed contains an Informix extension to SQL (only when the DBANSIWARN environment variable is set).
"01I07"	PREPARE DESCRIBE	A prepared UPDATE or DELETE statement has no WHERE clause. The operation will affect all rows of the table.
"01I09"	FETCH SELECT...INTO EXECUTE...INTO	The number of items in the select list does not equal the number of host variables in the INTO clause.
"01I10"	CONNECT CREATE DATABASE DATABASE SET CONNECTION	The database server is currently running in secondary mode. The database server is a secondary server in a data-replication pair; therefore, the database server is available only for read operations.
"01I11"	Other statements (when your application activates the DATASKIP feature)	A data fragment (a dbspace) has been skipped during query processing.

For a list of the ANSI and X/Open warning messages, see "List of SQLSTATE Class Codes".

To check for a warning, your code only needs to verify the first two characters of SQLSTATE. However, to identify the particular warning, you need to examine the subclass code. You might also want to use the GET DIAGNOSTICS statement to obtain the warning message from the MESSAGE_TEXT field.

For example, the block of code in Figure 11-11 determines what kind of database a CONNECT statement has opened.

int trans_db, ansi_db, online_db = 0; ... msg = "CONNECT stmt"; EXEC SQL connect to 'stores7'; if(!strncmp(SQLSTATE, "02", 2)) /* < 0 is an error */ err_chk(msg); if (!strncmp(SQLSTATE, "01", 2)) { if (!strncmp(SQLSTATE[2], "I01", 3)) trans_db = 1; if (!strncmp(SQLSTATE[2], "I03", 3)) ansi_db = 1; if (!strncmp(SQLSTATE[2], "I04", 3)) online_db = 1; }

Figure 11-11 Using SQLSTATE to Check for Informix-Specific Warnings

The code fragment in Figure 11-11 checks SQLSTATE with the system strncmp() function. The getdiag sample program (page 11-57) uses the sqlstate_err() function to check the success of an SQL statement by comparing the first two characters of SQLSTATE with the string "01". For more information about the values of SQLSTATE that the CONNECT, CREATE DATABASE, DATABASE, and SET CONNECTION statements set, see "Determining Features of the Database Server".

Runtime Errors in SQLSTATE

If the SQL statement generates an error that the ANSI or X/Open standards do not support, SQLSTATE might contain either of the following values:

• An SQLSTATE value of "IX000" indicates an Informix-specific error.

• An SQLSTATE value of "U0001" indicates a user-defined error message from a user-defined routine.

Multiple Exceptions

To find out how many exceptions the database server has reported for an SQL statement, retrieve the value of the NUMBER field from the statement information of the diagnostics area. The following GET DIAGNOSTICS statement retrieves the number of exceptions that the database server generated and stores the number in the :exception_num host variable.

EXEC SQL get diagnostics :exception_num = NUMBER;

EXEC SQL get diagnostics :exception_count = NUMBER, :overflow = MORE; printf("NUMBER: %d\n", exception_count); printf("MORE : %s\n", overflow); for (i = 1; i <= exception_count; i++) { EXEC SQL get diagnostics exception :i :sqlstate = RETURNED_SQLSTATE, :class = CLASS_ORIGIN, :subclass = SUBCLASS_ORIGIN, :message = MESSAGE_TEXT, :messlen = MESSAGE_LENGTH; printf("SQLSTATE: %s\n",sqlstate); printf("CLASS ORIGIN: %s\n",class); printf("SUBCLASS ORIGIN: %s\n",subclass); printf("TEXT: %s\n",message); printf("MESSAGE LENGTH: %d\n",messlen); }

Figure 11-12 Reporting Multiple Exception Conditions

Do not confuse the RETURNED_SQLSTATE value with the SQLSTATE global variable. The SQLSTATE variable provides a general status value for the most-recently executed SQL statement. The RETURNED_SQLSTATE value is associated with one particular exception that the database server has encountered. For the first exception, SQLSTATE and RETURNED_SQLSTATE have the same value. However, for multiple exceptions, you must access RETURNED_SQLSTATE for each exception.

To define a host variable in your application that receives the RETURNED_SQLSTATE value, you must define it as a character array with a length of six (five for the field plus one for the null terminator). You can assign this variable whatever name you wish.

The following statements define such a host variable and assign it the name sql_state:

EXEC SQL BEGIN DECLARE SECTION;

	char sql_state[6];

EXEC SQL END DECLARE SECTION;