Exception information
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.
The following table summarizes the exception_fields information
of the diagnostics area.
| 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 The SQLSTATE variable. |
| INFORMIX_SQLCODE | int4 | This field holds the status code specific to Informix®. This code is also available in the global SQLCODE variable. For more information, see 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 | mint | 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 Identify 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 SET CONNECTION statement. This field is blank
when no current connection or no explicit connection exists. Otherwise,
it contains the name of the last successfully established connection.
For more information about the CONNECTION_NAME field, see Identify an explicit connection. |
Use the exception information to save detailed information
about an exception. The code fragment in the following table retrieves
exception information about the first exception of a CREATE TABLE
statement.
Figure 1. Example
of using GET DIAGNOSTICS to return exception information
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 about the exception fields, see the GET DIAGNOSTICS statement in the HCL Informix Guide to SQL: Syntax.