EXCEPTION Clause

Exception Clause:
 
|--EXCEPTION--+-exception_num-+--------------------------------->
              '-exception_var-'
 
   .-,--------------------------------------.
   V                                        |
>----information-- =--+-CLASS_ORIGIN------+-+-------------------|
                      +-CONNECTION_ALIAS--+
                      +-MESSAGE_LENGTH----+
                      +-MESSAGE_TEXT------+
                      +-RETURNED_SQLSTATE-+
                      +-SERVER_NAME-------+
                      '-SUBCLASS_ORIGIN---'
 

The exception_num literal indicates one of the exception values from the number of exceptions that the NUMBER field in the Statement clause returns.

When retrieving exception information, GET DIAGNOSTICS writes 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 following table describes the seven exception information fields.

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 invalid 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

The RETURNED_SQLSTATE keyword returns 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 ISO standard for SQL defines the class, the value of CLASS_ORIGIN is equal to ISO 9075. Otherwise, the value returned by CLASS_ORIGIN is defined by Informix and cannot be ISO 9075. The terms ANSI SQL and ISO SQL are synonymous.

Using the SUBCLASS_ORIGIN Keyword

The SUBCLASS_ORIGIN keyword returns data on the RETURNED_SQLSTATE subclass. (This value is ISO 9075 if the ISO standard defines the subclass.)

Using the MESSAGE_TEXT Keyword

The MESSAGE_TEXT keyword returns the message text of the exception (for example, an error message).

Using the MESSAGE_LENGTH Keyword

The MESSAGE_LENGTH keyword returns the length in bytes of the current message text string.

Using the SERVER_NAME Keyword

The SERVER_NAME keyword returns the the name of the database server associated with a CONNECT or DATABASE statement. GET DIAGNOSTICS updates the SERVER_NAME field when any of the following events occur:

• A CONNECT statement successfully executes.
• A SET CONNECTION statement successfully executes.
• A DISCONNECT statement successfully terminates the current connection.
• A DISCONNECT ALL statement fails.

The SERVER_NAME field is not updated, however, after these events:

• A CONNECT statement fails.
• A DISCONNECT statement fails (but this does not include the DISCONNECT ALL statement).
• A SET CONNECTION statement fails.

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.