21222"> 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 variable, called SQLSTATE, that you do not have to declare in your program. SQLSTATE contains the error code that is 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.

Statement Clause

Element Purpose Restrictions Syntax
status_var Host variable that receives status information about the most recent SQL statement Receives information for the specified status field name. Data type must match that of the requested field. Name must conform to language-specific rules for variable names.

When retrieving count and overflow information, GET DIAGNOSTICS can deposit the values of the three statement fields into a corresponding host variable. The host-variable data type must be the same as that of the requested field. These three fields are represented by the following keywords.

Field Name Keyword Field Data Type Field Contents ESQL/C
Host Variable Data Type
MORE Character Y or N char[2]
NUMBER Integer 1 to 35,000 int
ROW_COUNT Integer 0 to 999,999,999 int

Using the MORE Keyword

Use the MORE keyword to determine if the most recently executed SQL statement performed the following actions:

The value of MORE is always N.

Using the NUMBER Keyword

Use the NUMBER keyword to count the number of exceptions that the most recently executed SQL statement placed into the diagnostics area. The NUMBER field can hold a value from 1 to 35,000, depending on how many exceptions are counted.

Using the ROW_COUNT Keyword

Use the ROW_COUNT keyword to count the number of rows the most recently executed statement processed. ROW_COUNT counts the following number of rows:

EXCEPTION Clause

Element Purpose Restrictions Syntax
exception_num Literal integer value that specifies the exception number for a GET DIAGNOSTICS statement The exception_num literal indicates one of the exception values from the number of exceptions returned by the NUMBER field in the Statement clause. Integer value is limited to a range from 1 to 35,000. Literal Number, p. 4-237
exception_var Host variable that specifies an exception number for a GET DIAGNOSTICS statement Variable must contain an integer value limited to a range from 1 to 35,000. Variable data type must be INT or SMALLINT. Variable name must conform to language-specific rules for variable names.
information Host variable that receives EXCEPTION information about the most recent SQL statement Receives information for a specified exception field name. Data type must match that of the requested field. Variable name must conform to language-specific rules for variable names.

When retrieving exception information, GET DIAGNOSTICS deposits the values of each of the seven fields into corresponding host variables. These fields are located in the diagnostics area and are derived from an exception raised by the most recent SQL statement.

The host-variable data type must be the same as that of the requested field. The seven exception information fields are represented by the keywords described in the following table.

Field Name Keyword Field Data Type Field Contents ESQL/C Host Variable
Data Type
RETURNED_SQLSTATE Character SQLSTATE value char[6]
CLASS_ORIGIN Character String char[255]
SUBCLASS_ORIGIN Character String char[255]
MESSAGE_TEXT Character String char[255]
MESSAGE_LENGTH Integer Numeric value int
SERVER_NAME Character String char[255]
CONNECTION_NAME Character String char[255]

The application specifies the exception by number, using either an unsigned integer or an integer host variable (an exact numeric with a scale of 0). An exception with a value of 1 corresponds to the SQLSTATE value set by the most recent SQL statement other than GET DIAGNOSTICS. The association between other exception numbers and other exceptions raised by that SQL statement is undefined. Thus, no set order exists in which the diagnostic area can be filled with exception values. You always get at least one exception, even if the SQLSTATE value indicates success.

If an error occurs within the GET DIAGNOSTICS statement (that is, if an illegal exception number is requested), the Informix internal SQLCODE and SQLSTATE variables are set to the value of that exception. In addition, the GET DIAGNOSTICS fields are undefined.

Using the RETURNED_SQLSTATE Keyword

Use the RETURNED_SQLSTATE keyword to determine the SQLSTATE value that describes the exception.

Using the CLASS_ORIGIN Keyword

Use the CLASS_ORIGIN keyword to retrieve the class portion of the RETURNED_SQLSTATE value. If the International Standards Organization (ISO) standard defines the class, the value of CLASS_ORIGIN is equal to ISO 9075. Otherwise, the value of CLASS_ORIGIN is defined by Informix and cannot be ISO 9075. ANSI SQL and ISO SQL are synonymous.

Using the SUBCLASS_ORIGIN Keyword

Use the SUBCLASS_ORIGIN keyword to define the source of the subclass portion of the RETURNED_SQLSTATE value. If the ISO international standard defines the subclass, the value of SUBCLASS_ORIGIN is equal to ISO 9075.

Using the MESSAGE_TEXT Keyword

Use the MESSAGE_TEXT keyword to determine the message text of the exception (for example, an error message).

Using the MESSAGE_LENGTH Keyword

Use the MESSAGE_LENGTH keyword to determine the length of the current MESSAGE_TEXT string.

Using the SERVER_NAME Keyword

Use the SERVER_NAME keyword to determine the name of the database server associated with the actions of a CONNECT or DATABASE statement.

When the SERVER_NAME Field Is Updated

The GET DIAGNOSTICS statement updates the SERVER_NAME field when the following situations occur:

When the SERVER_NAME Field Is Not Updated

The SERVER_NAME field is not updated when:

The SERVER_NAME field retains the value set in the previous SQL statement. If any of the preceding conditions occur on the first SQL statement that executes, the SERVER_NAME field is blank.

The Contents of the SERVER_NAME Field

The SERVER_NAME field contains different information after you execute the following statements.

Executed Statement SERVER_NAME Field Contents
CONNECT Contains the name of the database server to which you connect or fail to connect Field is blank if you do not have a current connection or if you make a default connection.
SET CONNECTION Contains the name of the database server to which you switch or fail to switch
DISCONNECT Contains the name of the database server from which you disconnect or fail to disconnect If you disconnect and then you execute a DISCONNECT statement for a connection that is not current, the SERVER_NAME field remains unchanged.
DISCONNECT ALL Sets the field to blank if the statement executes successfully If the statement does not execute successfully, the SERVER_NAME field contains the names of all the database servers from which you did not disconnect. However, this information does not mean that the connection still exists.

If the CONNECT statement is successful, the SERVER_NAME field is set to one of the following values:

The DATABASE Statement

When you execute a DATABASE statement, the SERVER_NAME field contains the name of the server on which the database resides.

Using the CONNECTION_NAME Keyword

Use the CONNECTION_NAME keyword to specify a name for the connection used in your CONNECT or DATABASE statements.

When the CONNECTION_NAME Keyword Is Updated

GET DIAGNOSTICS updates the CONNECTION_NAME field when the following situations occur:

When CONNECTION_NAME Is Not Updated

The CONNECTION_NAME field is not updated when the following situations occur:

The CONNECTION_NAME field retains the value set in the previous SQL statement. If any of the preceding conditions occur on the first SQL statement that executes, the CONNECTION_NAME field is blank.

The Contents of the CONNECTION_NAME Field

The CONNECTION_NAME field contains different information after you execute the following statements.

Executed Statement CONNECTION_NAME Field Contents
CONNECT Contains the name of the connection, specified in the CONNECT statement, to which you connect or fail to connect The field is blank if you do not have a current connection or if you make a default connection.
SET CONNECTION Contains the name of the connection, specified in the CONNECT statement, to which you switch or fail to switch
DISCONNECT Contains the name of the connection, specified in the CONNECT statement, from which you disconnect or fail to disconnect If you disconnect, and then you execute a DISCONNECT statement for a connection that is not current, the CONNECTION_NAME field remains unchanged.
DISCONNECT ALL Contains no information if the statement executes successfully If the statement does not execute successfully, the CONNECTION_NAME field contains the names of all the connections, specified in your CONNECT statement, from which you did not disconnect. However, this information does not mean that the connection still exists.

If the CONNECT is successful, the CONNECTION_NAME field is set to the following values:

DATABASE Statement

When you execute a DATABASE statement, the CONNECTION_NAME field is blank.

Using GET DIAGNOSTICS for Error Checking

The GET DIAGNOSTICS statement returns information held in various fields of the diagnostic area. For each field in the diagnostic area that you want to access, you must supply a host variable with a compatible data type.

The following example illustrates how to use the GET DIAGNOSTICS statement to display error information. The example shows an ESQL/C error display routine called disp_sqlstate_err().

Related Information

For a task-oriented discussion of error handling and the SQLSTATE variable, see the Informix Guide to SQL: Tutorial.

For a discussion of concepts related to the GET DIAGNOSTICS statement and the SQLSTATE variable, see the Informix ESQL/C Programmer's Manual.

Informix Guide to SQL: Syntax, Version 9.2
Copyright © 1999, Informix Software, Inc. All rights reserved