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