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.
Table 1. Exception information from the GET DIAGNOSTICS statement
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 HCL OneDB™. 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 HCL OneDB 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 HCL OneDB 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 OneDB Guide to SQL: Syntax.