INFORMIX-ESQL/C Programmer's Manual Exception Handling

Informix recommends that you obtain diagnostic information about SQL statements with the SQLSTATE variable and the GET DIAGNOSTICS statement.

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 about the sqlca structure, see Exception Handling with the sqlca Structure

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

This section briefly summarizes how to use the GET DIAGNOSTICS statement within an ESQL/C program. For a full description of the GET DIAGNOSTICS statement, refer to the Informix Guide to SQL: Syntax.

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

Make sure that the data types of the host variable and the diagnostics field are compatible.

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.

The GET DIAGNOSTICS statement returns information about the most-recently executed SQL statement. This form of the GET DIAGNOSTICS statement has the following general syntax:

EXEC SQL get diagnostics statement_fields;

Figure 11-1 summarizes the statement_fields of the diagnostics area.

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;

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

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.

The GET DIAGNOSTICS statement also returns information about the exceptions that the most-recently executed SQL statement has generated. Each exception has an exception number. To obtain information about a particular exception, use the EXCEPTION clause of the GET DIAGNOSTICS statement, as follows:

EXEC SQL get diagnostics exception except_num exception_fields;

The except_num argument can be a literal number or a host variable. An except_num of one (1) corresponds to the SQLSTATE value that the most-recently executed SQL statement sets. After this first exception, the order in which the database server fills the diagnostics area with exception values is not predetermined. For more information, see Multiple Exceptions.

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

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

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

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

SQLSTATE is a five-character string that the database server sets after it executes each SQL statement. The ESQL/C header file, sqlca.h, declares SQLSTATE as a global variable. Since the ESQL/C preprocessor automatically includes sqlca.h in an ESQL/C program, you do not need to declare SQLSTATE.

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.

These updates to the SQLSTATE variable are equivalent to the execution of the following GET DIAGNOSTICS statement immediately after an SQL statement:

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 value in SQLSTATE is the status of the most-recently executed SQL statement before the GET DIAGNOSTICS statement executed. If the database server encounters an error when it executes the GET DIAGNOSTICS statement, it sets SQLSTATE to "IX001" and sets SQLCODE (and sqlca.sqlcode) to the value of the error number that corresponds to the error; the contents of the diagnostics area are undefined.

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

• The INFORMIX_SQLCODE field of the diagnostics area
• The SQLCODE variable (Using the SQLCODE Variable)

The next two sections provide the following information about the format of the SQLSTATE value:

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

To determine the success of an SQL statement, your ESQL/C program must be able to interpret the value in the SQLSTATE variable. SQLSTATE consists of a two-character class code and a three-character subclass code. In Figure 11-5, IX is the class code and 000 is the subclass code. The value "IX000" indicates an Informix-specific error.

Figure 11-5 The Structure of the SQLSTATE Code with the Value IX000

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.

List of SQLSTATE Class Codes

Figure 11-7 lists the valid SQLSTATE class and subclass values. This figure lists the first entry for each class code in bold.

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

• A "IX000" runtime error indicates an Informix-specific error message.
• A "IX001" runtime error indicates the GET DIAGNOSTiCS statement failed.
• 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.

For more information on non-standard error values, see Runtime Errors in SQLSTATE. For more information on non-standard warning values, see Warnings in SQLSTATE.

After an SQL statement executes, the SQLSTATE value can indicate one of the four conditions that Figure 11-8 shows.

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

1. Use GET DIAGNOSTICS to obtain the statement information such as the number of exceptions that the database server has generated.
2. For each exception, use the EXCEPTION clause of GET DIAGNOSTICS to obtain detailed information about the exception.

The following sections discuss how SQLSTATE indicates each condition.

When the database server executes an SQL statement successfully, it sets SQLSTATE to "00000" (class = "00", subclass = "000"). To check for successful execution, your code needs to verify only the first two characters of 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 on page 11-22.

The getdiag sample program on page 11-50 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 on page 11-43 checks for a success in SQLSTATE with the system strncmp() function.

When a SELECT or FETCH statement encounters NOT FOUND (or END OF DATA), the database server sets SQLSTATE to "02000" (class = "02"). Figure 11-9 lists the conditions that cause SQL statements to yield NOT FOUND.

Figure 11-9 on page 11-20 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-50 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".

When the database server executes an SQL statement successfully, but encounters a warning condition, it sets the class code of SQLSTATE to "01". The subclass code then indicates the cause of the warning. This warning can be either of the following types:

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

The CLASS_ORIGIN and SUBCLASS_ORIGIN exception fields of the diagnostics area have a value of "ISO 9075" to indicate ANSI or X/Open as the source of the warning.

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

The CLASS_ORIGIN and SUBCLASS_ORIGIN exception fields of the diagnostics area have a value of "IX000" to indicate an Informix-specific exception as the source of the warning.

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

The CLASS_ORIGIN and SUBCLASS_ORIGIN exception fields of the diagnostics area have a value of "U0001" to indicate a user-defined routine as the source of the warning.

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

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;

	}

The code fragment in Figure 11-11 checks SQLSTATE with the system strncmp() function. The getdiag sample program (page 11-50) 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.

When an SQL statement results in a runtime error, the database server stores a value in SQLSTATE whose class code is greater than "02". The actual class and subclass codes identify the particular error. Figure 11-7 on page 11-15 lists the class and subclass codes for SQLSTATE. To retrieve the error message text, use the MESSAGE_TEXT field of the GET DIAGNOSTICS statement. The CLASS_ORIGIN and SUBCLASS_ORIGIN exception fields have a value of "ISO 9075" to indicate the source of the error.

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.

The SQLCODE variable contains the Informix-specific error code, and the MESSAGE_TEXT field contains the error message text and any ISAM message text. The CLASS_ORIGIN and SUBCLASS_ORIGIN exception fields have a value of "IX000" to indicate the source of the error.

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

The MESSAGE_TEXT field contains the error message text. The CLASS_ORIGIN and SUBCLASS_ORIGIN exception fields have a value of "U0001" to indicate the source of the error.

If the GET Diagnostics statement fails, SQLstate contains a value of ix001. No other failure returns this value. The sqlcode indicates the specific error that caused the failure.

The database server can generate multiple exceptions for a single SQL statement. A significant advantage of the GET DIAGNOSTICS statement is its ability to report multiple exception conditions.

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;

Once you know the number of exceptions that occurred, you can initiate a loop to report each of them. Execute GET DIAGNOSTICS within this loop and use the number of exceptions to control the loop. Figure 11-12 illustrates one way to retrieve and report multiple exception conditions after an SQL statement.

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;

A database system that is compliant with X/Open standards must report any X/Open exceptions before it reports any Informix-specific errors or warnings. Beyond this, however, the database server does not report the exceptions in any particular order. The getdiag sample program (page 11-50) includes the disp_sqlstate_err() function to display multiple exceptions.