2.21 GET DIAGNOSTICS

Description

Retrieves information about the execution of the previous SQL statement. GET DIAGNOSTICS extracts information from the SQL diagnostics area, a data structure that contains information about the execution status of the most recent SQL statement. There are two components to the diagnostics area:

The header contains overall information about the last SQL statement as a whole

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.

Note: The SQL diagnostics area currently supports only one detail area.

There are two forms of the GET DIAGNOSTICS statement, one that extracts header information (GET DIAGNOSTICS), and one that extracts detail information (GET DIAGNOSTICS EXCEPTION number).

Syntax

GET DIAGNOSTICS 

  :param = header_info_item [ , :param = header_info_item ] ... 

GET DIAGNOSTICS EXCEPTION number 

  :param = detail_info_item [ , :param = detail_info_item ] ... 

header_info_item ::

  { NUMBER 

  | MORE 

  | COMMAND_FUNCTION 

  | DYNAMIC_FUNCTION 

  | ROW_COUNT }

detail_info_item ::

  { 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 }

Arguments

:parameter
A host-language variable to receive the information returned by the GET DIAGNOSTICS statement. The host-language program must declare parameter to be compatible with the SQL data type of the information item.

header_info_item
One of the following keywords, which returns associated information about the diagnostics area or the SQL statement:


NUMBER	The number of detail areas in the diagnostics area. Currently, NUMBER is always 1. NUMBER is type NUMERIC with a scale of 0.
MORE	Whether the diagnostics area contains information on all the conditions resulting from the statement. MORE is a one-character string with a value of Y (all conditions are detailed in the diagnostics area) or N (all conditions are not detailed).
COMMAND_FUNCTION	If the statement was a static SQL statement, contains the character-string code for the statement (as specified in the SQL-92 standard). If the statement was a dynamic statement, contains either the character string 'EXECUTE' or 'EXECUTE IMMEDIATE'.
DYNAMIC_FUNCTION	For dynamic SQL statements only (as indicated by 'EXECUTE' or 'EXECUTE IMMEDIATE' in the COMMAND_FUNCTION item), contains the character-string code for the statement (as specified in the SQL-92 standard).
ROW_COUNT	The number of rows affected by the SQL statement.

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

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


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. See Table B-2: Error Codes and Messages on page B-2 for a list of SQLSTATE values.
CLASS_ORIGIN	Whether the SQLSTATE class code is defined by the SQL standard (indicated by the character string 'ISO 9075') or by c-treeSQL.
SUBCLASS_ORIGIN	Whether the SQLSTATE subclass code is defined by the SQL standard (indicated by the character string 'ISO 9075') or by c-treeSQL.
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	If the error condition involves a table, the name of the table.
COLUMN_NAME	If the error condition involves a column, the name of the affected columns.
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 issues multiple GET DIAGNOSTICS statements to retrieve different items of information about the same SQL statement.

Example

GET DIAGNOSTICS :num = NUMBER, :cmdfunc = COMMAND_FUNCTION 

GET DIAGNOSTICS EXCEPTION :num

:sstate = RETURNED_SQLSTATE, :msgtxt = MESSAGE_TEXT

Authorization


SQL Compliance	SQL-92
Environment	Embedded SQL
Related Statements	WHENEVER