Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

GET DIAGNOSTICS

Description

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

  • The header contains overall information about the last FairCom DB 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 FairCom DB SQL statement. The diagnostics area can potentially contain multiple detail areas corresponding to multiple conditions generated by the FairCom DB SQL statement described by the header.

Note: The FairCom DB 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

| TABLE_NAME

| COLUMN_NAME

| MESSAGE_TEXT

| MESSAGE_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 FairCom DB 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 FairCom DB 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 FairCom DB 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 FairCom DB 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 the individual sections in Error Messages 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 FairCom DB SQL.

SUBCLASS_ORIGIN

Whether the SQLSTATE subclass code is defined by the FairCom DB SQL standard (indicated by the character string ‘ISO 9075’) or by FairCom DB SQL.

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.

MESSAGE_TEXT

The associated message text for the error condition.

MESSAGE_LENGTH

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

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 FairCom DB 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

TOCIndex