GET DIAGNOSTICS EXCEPTION

Retrieves information about the execution of the previous SQL statement from the SQL diagnostics area. The diagnostics area is a data structure that contains information about the execution status of the most recent SQL statement. Specifically, GET DIAGNOSTICS EXCEPTION extracts information about the SQL statement as a whole from the SQL diagnostics area's detail component.

The detail area contains information for a particular condition (an error, warning, or success condition) associated with execution of the last SQL statement. The diagnostics area can potentially contain multiple detail areas corresponding to multiple conditions generated by the SQL statement described by the header. The SQL diagnostics area currently supports only one detail area.

Note: The GET DIAGNOSTICS statement extracts header information.

Syntax

GET DIAGNOSTICS EXCEPTION number  :param = detail_info_item[, :param = detail_info_item] , ... ;

Parameters

EXCEPTION number

Specifies that GET DIAGNOSTICS EXCEPTION extracts detail information. number specifies which of multiple detail areas GET DIAGNOSTICS extracts. Currently, number must be the integer 1.

param

Receives the information returned by the GET DIAGNOSTICS EXCEPTION statement. The host‑language program must declare a param compatible with the SQL data type of the information item.

detail_info_item

One of the following keywords, which returns associated information about the particular error condition:

CONDITION_NUMBER
  |  RETURNED_SQLSTATE
  |  CLASS_ORIGIN
  |  SUBCLASS_ORIGIN
  |  ENVIRONMENT_NAME
  |  CONNECTION_NAME
  |  CONSTRAINT_CATALOG
  |  CONSTRAINT_SCHEMA
  |  CONSTRAINT_NAME
  |  CATALOG_NAME
  |  SCHEMA_NAME
  |  TABLE_NAME
  |  COLUMN_NAME
  |  CURSOR_NAME
  |  MESSAGE_TEXT
  |  MESSAGE_LENGTH
  |  MESSAGE_OCTET_LENGTH
CONDITION_NUMBER

The sequence of this detail area in the diagnostics area. Currently, CONDITION_NUMBER is always 1.

RETURNED_SQLSTATE

The SQLSTATE value that corresponds to the condition.

CLASS_ORIGIN

The general type of error. For example, connection exception or data exception.

SUBCLASS_ORIGIN

The specific error. Usually the same as the message text.

ENVIRONMENT_NAME

Not currently supported.

CONNECTION_NAME

Not currently supported.

CONSTRAINT_CATALOG

Not currently supported.

CONSTRAINT_SCHEMA

Not currently supported.

CONSTRAINT_NAME

Not currently supported.

CATALOG_NAME

Not currently supported.

SCHEMA_NAME

Not currently supported.

TABLE_NAME

The name of the table, if the error condition involves a table.

COLUMN_NAME

The name of the affected columns, if the error condition involves a column.

CURSOR_NAME

Not currently supported.

MESSAGE_TEXT

The associated message text for the error condition.

MESSAGE_LENGTH

The length in characters of the message in the MESSAGE_LENGTH item.

MESSAGE_OCTET_LENGTH

Not currently supported.

Notes

The GET DIAGNOSTICS statement itself does not affect the contents of the diagnostics area. This means applications can issue multiple GET DIAGNOSTICS statements to retrieve different items of information about the same SQL statement.

Example

The GET DIAGNOSTICS EXCEPTION example extracts detailed information into host variables that are defined in the DECLARE SECTION of an embedded SQL program:

GET DIAGNOSTICS EXCEPTION :num :sstate = RETURNED_SQLSTATE,
     :msgtxt = MESSAGE_TEXT ;