GET DIAGNOSTICS

Gets statement or condition information from the diagnostics area.

get_diagnostics.png

 

where statement-information is:

statement_information.png

 

and condition-info is:

condition_info.png

 

Usage

Embedded, Procedural.

Description

Selected status information from the diagnostics area is retrieved. The diagnostics area holds information about the most recently executed SQL statement. There is only one diagnostics area for each application, independent of the number of connections that the application holds. Observe that the GET DIAGNOSTICS statement itself does not change the diagnostics area, apart from setting SQLSTATE.

The GET DIAGNOSTICS statement can be in two forms: the first form retrieves statement information about the most recent SQL statement executed. The second form of GET DIAGNOSTICS is the CONDITION form, which retrieves condition information for the most recently executed SQL statement. The ordinal number of the condition to be returned is specified immediately following the keyword CONDITION.

statement-information Information Items

The information items for statement-information are described in the following table:

Information item

Data type

Description

COMMAND_FUNCTION

NCHAR VARYING(128)

A string identifying the preceding embedded SQL statement executed.

DYNAMIC_FUNCTION

NCHAR VARYING(128)

A string identifying the preceding prepared SQL statement executed.

MORE

CHAR(1)

Indicates if there are any conditions for which no condition information has been stored.

N if all detected conditions are stored in the diagnostics area, otherwise Y.

NUMBER

INTEGER

The number of condition messages stored for the most recently executed SQL statement.

ROW_COUNT

INTEGER

The number of rows inserted, updated or deleted if the last statement was INSERT, searched UPDATE or searched DELETE.

TRANSACTION_ACTIVE

INTEGER

Indicates if a transaction is active or not.

0 = transaction not active

1 = transaction is active.

condition-info Information Items

The information items for condition-info are described in the following table:

Information item

Data type

Description

CATALOG_NAME

NCHAR VARYING(128)

The catalog name of the schema containing the table on which the violated constraint is defined, always an empty string ("").

CLASS_ORIGIN

NCHAR VARYING(128)

The defining source of the two first characters (the class portion) of the SQLSTATE value.

COLUMN_NAME

NCHAR VARYING(128)

The name of the table column on which the violated constraint is defined. If the constraint involves more than one column or the data change operation causing the condition is not in the table on which the constraint is defined, this will be an empty string ("").

CONDITION_IDENTIFIER

NCHAR VARYING(128)

The value specified for condition-name in the DECLARE CONDITION statement declaring the condition as a named condition. This will be the empty string ("") if the condition has not been declared as a named condition.

CONDITION_NUMBER

INTEGER

The ordinal number of the condition on the diagnostics condition stack.

CONNECTION_NAME

NCHAR VARYING(128)

The connection name specified in a CONNECT, DISCONNECT or SET CONNECTION statement. The name of the current connection for all other statements.

CONSTRAINT_CATALOG

NCHAR VARYING(128)

The catalog name of the schema containing the violated constraint, always an empty string ("").

CONSTRAINT_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the violated constraint.

CONSTRAINT_NAME

NCHAR VARYING(128)

The name of the violated constraint.

CURSOR_NAME

NCHAR VARYING(128)

The name of the cursor which is in an invalid state, when the condition: 24000 - 'Invalid Cursor State' is raised.

ERROR_LENGTH

INTEGER

The length in characters of the relevant part the SQL statement, starting at ERROR_POSITION.

ERROR_POSITION

INTEGER

The position in the SQL statement where the specified condition occurred.

Value < 1 means unknown position.

MESSAGE_LENGTH

INTEGER

The length of the message text for the specified condition.

MESSAGE_OCTET_LENGTH

INTEGER

Currently the same as MESSAGE_LENGTH.

MESSAGE_TEXT

NCHAR VARYING(254)

The descriptive message text for the specified condition.

NATIVE_ERROR

INTEGER

The internal Mimer SQL return code relating to the condition. See the Mimer SQL Programmer's Manual, Return Codes.

PARAMETER_NAME

NCHAR VARYING(128)

The name of the routine parameter causing the condition.

RETURNED_SQLSTATE

CHAR(5)

Value of SQLSTATE for the specified condition.

ROUTINE_CATALOG

NCHAR VARYING(128)

The catalog name of the schema containing the function or procedure in which the condition was raised, always an empty string ("").

ROUTINE_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the function or procedure in which the condition was raised.

ROUTINE_NAME

NCHAR VARYING(128)

The name of the function or procedure in which the condition was raised.

SCHEMA_NAME

NCHAR VARYING(128)

The name of the schema containing the table on which the violated constraint is defined. If the data change operation causing the condition is not in the table on which the constraint is defined, this will be an empty string ("").

SERVER_NAME

NCHAR VARYING(128)

The database name specified in a CONNECT, DISCONNECT or SET CONNECTION statement. The current database name for all other statements.

SPECIFIC_NAME

NCHAR VARYING(128)

Specific name of the procedure or function in which the condition was raised.

SUBCLASS_ORIGIN

NCHAR VARYING(128)

The defining source of the three last characters (the subclass portion) of the SQLSTATE value.

TABLE_NAME

NCHAR VARYING(128)

The name of the table on which the violated constraint is defined. If the data change operation causing the condition is not in the table on which the constraint is defined, this will be an empty string ("").

TRIGGER_CATALOG

NCHAR VARYING(128)

The catalog name of the schema containing the table supporting the trigger in which the condition was raised, always an empty string ("").

TRIGGER_SCHEMA

NCHAR VARYING(128)

The name of the schema containing the table supporting the trigger in which the condition was raised.

TRIGGER_NAME

NCHAR VARYING(128)

The name of the trigger in which the condition was raised.

Values for COMMAND_FUNCTION and DYNAMIC_FUNCTION

The COMMAND_FUNCTION and DYNAMIC_FUNCTION information items can contain any of the following values:

ALLOCATE CURSOR

DROP STATEMENT

ALLOCATE DESCRIPTOR

DROP SYNONYM

ALTER DATABANK

DROP TABLE

ALTER DATABANK RESTORE

DROP TRIGGER

ALTER IDENT

DROP TYPE

ALTER SHADOW

DROP VIEW

ALTER STATEMENT

DYNAMIC CLOSE

ALTER TABLE

DYNAMIC DELETE CURSOR

ALTER TYPE

DYNAMIC FETCH

ASSIGNMENT

DYNAMIC OPEN

CALL

DYNAMIC UPDATE CURSOR

CLOSE CURSOR

ENTER

COMMENT

EXECUTE

COMMIT WORK

EXECUTE IMMEDIATE

CONNECT

FETCH

CREATE BACKUP

GET DESCRIPTOR

CREATE COLLATION

GET DIAGNOSTICS

CREATE DATABANK

GRANT

CREATE DOMAIN

GRANT OBJECT PRIVILEGE

CREATE FUNCTION

GRANT SYSTEM PRIVILEGE

CREATE IDENT

INSERT

CREATE INDEX

LEAVE

CREATE METHOD

LEAVE RETAIN

CREATE MODULE

OPEN

CREATE PROCEDURE

PREPARE

CREATE SCHEMA

REVOKE

CREATE SEQUENCE

REVOKE OBJECT PRIVILEGE

CREATE SHADOW

REVOKE SYSTEM PRIVILEGE

CREATE STATEMENT

ROLLBACK WORK

CREATE SYNONYM

SELECT

CREATE TABLE

SET CONNECTION

CREATE TRIGGER

SET DATABANK

CREATE TYPE

SET DATABASE

CREATE VIEW

SET DESCRIPTOR

DEALLOCATE DESCRIPTOR

SET SESSION DIAGNOSTIC SIZE

DEALLOCATE PREPARE

SET SESSION ISOLATION LEVEL

DELETE CURSOR

SET SESSION READ ONLY

DELETE WHERE

SET SESSION READ WRITE

DESCRIBE

SET SESSION START EXPLICIT

DISCONNECT

SET SESSION START IMPLICIT

DROP COLLATION

SET SHADOW

DROP DATABANK

SET TRANSACTION DIAGNOSTIC SIZE

DROP DOMAIN

SET TRANSACTION ISOLATION LEVEL

DROP FUNCTION

SET TRANSACTION READ ONLY

DROP IDENT

SET TRANSACTION READ WRITE

DROP INDEX

SET TRANSACTION START EXPLICIT

DROP METHOD

SET TRANSACTION START IMPLICIT

DROP MODULE

START TRANSACTION

DROP PROCEDURE

UPDATE CURSOR

DROP SCHEMA

UPDATE STATISTICS

DROP SEQUENCE

UPDATE WHERE

DROP SHADOW

 

Language Elements

target-variable, see Target Variables.

Notes

The condition requested by the GET DIAGNOSTICS CONDITION form must be one of the conditions that exist in the diagnostics area, i.e. the condition number must be in the range from 1 up to the value of NUMBER.

Example

…

exec sql GET DIAGNOSTICS :cnt = NUMBER;

for (int i = 1; i <= cnt; i++) {

   exec sql GET DIAGNOSTICS CONDITION :i

       :sqlstatestr = RETURNED_SQLSTATE,

       :errmsgstr = MESSAGE_TEXT,

       :errmsglen = MESSAGE_LENGTH;

   …

}

…

Standard Compliance

Standard

Compliance

Comments

SQL-2016

Features outside core

Feature F121, “Basic diagnostics management”.

 

Mimer SQL extension

The support for NATIVE_ERROR, ERROR_LENGTH and ERROR_POSITION is a Mimer SQL extension.