Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


GET DIAGNOSTICS


Gets statement or exception information from the diagnostics area.



where statement-information is:



and exception-info is:



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, although it does set 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 EXCEPTION form, which retrieves exception information for the most recently executed SQL statement. The ordinal number of the exception to be returned is specified immediately following the keyword EXCEPTION.

statement-information Information Items

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

Information item
Data type
Description
COMMAND_FUNCTION
VARCHAR(128)
A string identifying the preceding embedded SQL statement executed.
DYNAMIC_FUNCTION
VARCHAR(128)
A string identifying the preceding prepared SQL statement executed.
MORE
CHAR(1)
Indicates if there are any exceptions for which no exception information has been stored.
N if all detected exceptions are stored in the diagnostics area, otherwise Y.
NUMBER
INTEGER
The number of exception 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.

exception-info Information Items

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

Information item
Data type
Description
CATALOG_NAME
VARCHAR(128)
The catalog name of the schema containing the table on which the violated constraint is defined, always an empty string ("").
CLASS_ORIGIN
VARCHAR(128)
The defining source of the two first characters (the class portion) of the SQLSTATE value.
COLUMN_NAME
VARCHAR(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 exception is not in the table on which the constraint is defined, this will be an empty string ("").
CONDITION_IDENTIFIER
VARCHAR(128)
The value specified for condition-name in the DECLARE CONDITION statement declaring the exception as a named condition. This will be the empty string ("") if the exception has not been declared as a named condition.
CONDITION_NUMBER
INTEGER
The ordinal number of the exception on the diagnostics exception stack.
CONNECTION_NAME
VARCHAR(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
VARCHAR(128)
The catalog name of the schema containing the violated constraint, always an empty string ("").
CONSTRAINT_SCHEMA
VARCHAR(128)
The name of the schema containing the violated constraint.
CONSTRAINT_NAME
VARCHAR(128)
The name of the violated constraint.
CURSOR_NAME
VARCHAR(128)
The name of the cursor which is in an invalid state, when the exception: 24000 - `Invalid Cursor State' is raised.
MESSAGE_LENGTH
INTEGER
The length of the message text for the specified exception.
MESSAGE_OCTET_LENGTH
INTEGER
Currently the same as MESSAGE_LENGTH.
MESSAGE_TEXT
VARCHAR(254)
The descriptive message text for the specified exception.
NATIVE_ERROR
INTEGER
The internal Mimer SQL return code relating to the exception. See the Mimer SQL Programmer's Manual, Appendix B, Return Codes.
PARAMETER_NAME
VARCHAR(128)
The name of the routine parameter causing the exception.
RETURNED_SQLSTATE
CHAR(5)
Value of SQLSTATE for the specified exception.
ROUTINE_CATALOG
VARCHAR(128)
The catalog name of the schema containing the function or procedure in which the exception was raised, always an empty string ("").
ROUTINE_SCHEMA
VARCHAR(128)
The name of the schema containing the function or procedure in which the exception was raised.
ROUTINE_NAME
VARCHAR(128)
The name of the function or procedure in which the exception was raised.
SCHEMA_NAME
VARCHAR(128)
The name of the schema containing the table on which the violated constraint is defined. If the data change operation causing the exception is not in the table on which the constraint is defined, this will be an empty string ("").

SERVER_NAME

VARCHAR(128)

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

SPECIFIC_NAME
VARCHAR(128)
Currently a unique name generated by the system.
SUBCLASS_ORIGIN
VARCHAR(128)
The defining source of the three last characters (the subclass portion) of the SQLSTATE value.
TABLE_NAME
VARCHAR(128)
The name of the table on which the violated constraint is defined. If the data change operation causing the exception is not in the table on which the constraint is defined, this will be an empty string ("").
TRIGGER_CATALOG
VARCHAR(128)
The catalog name of the schema containing the table supporting the trigger in which the exception was raised, always an empty string ("").
TRIGGER_SCHEMA
VARCHAR(128)
The name of the schema containing the table supporting the trigger in which the exception was raised.
TRIGGER_NAME
VARCHAR(128)
The name of the trigger in which the exception 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 TABLE

ALLOCATE DESCRIPTOR

DROP TRIGGER

ALTER DATABANK

DROP VIEW

ALTER DATABANK RESTORE

DYNAMIC CLOSE

ALTER IDENT

DYNAMIC DELETE CURSOR

ALTER SHADOW

DYNAMIC FETCH

ALTER TABLE

DYNAMIC OPEN

ASSIGNMENT

DYNAMIC UPDATE CURSOR

CALL

ENTER

CLOSE CURSOR

EXECUTE

COMMENT

EXECUTE IMMEDIATE

COMMIT WORK

FETCH

CONNECT

GET DESCRIPTOR

CREATE BACKUP

GET DIAGNOSTICS

CREATE COLLATION

GRANT

CREATE DATABANK

GRANT OBJECT PRIVILEGE

CREATE DOMAIN

GRANT SYSTEM PRIVILEGE

CREATE FUNCTION

INSERT

CREATE IDENT

LEAVE

CREATE INDEX

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 VIEW

SET DATABASE

DEALLOCATE DESCRIPTOR

SET DESCRIPTOR

DEALLOCATE PREPARE

SET SESSION DIAGNOSTIC SIZE

DELETE CURSOR

SET SESSION ISOLATION LEVEL

DELETE WHERE

SET SESSION READ ONLY

DESCRIBE

SET SESSION READ WRITE

DISCONNECT

SET SESSION START EXPLICIT

DROP COLLATION

SET SESSION START IMPLICIT

DROP DATABANK

SET SHADOW

DROP DOMAIN

SET TRANSACTION DIAGNOSTIC SIZE

DROP FUNCTION

SET TRANSACTION ISOLATION LEVEL

DROP IDENT

SET TRANSACTION READ ONLY

DROP INDEX

SET TRANSACTION READ WRITE

DROP MODULE

SET TRANSACTION START EXPLICIT

DROP PROCEDURE

SET TRANSACTION START IMPLICIT

DROP SCHEMA

START TRANSACTION

DROP SEQUENCE

UPDATE CURSOR

DROP SHADOW

UPDATE STATISTICS

DROP STATEMENT

UPDATE WHERE

DROP SYNONYM

Language Elements

target-variable, see Target Variables.

Notes

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

Example

 ...
 exec sql GET DIAGNIOSTICS :cnt = NUMBER;
 for (int i = 1; i <= cnt; i++) {
     exec sql GET DIAGNOSTICS EXCEPTION :i
         :sqlstatestr = RETURNED_SQLSTATE,
         :errmsgstr = MESSAGE_TEXT,
         :errmsglen = MESSAGE_LENGTH;
     ...
 }
 ...

Standard Compliance

Standard
Compliance
Comments
SQL-99
Features outside core
Feature F121, "Basic diagnostics management".

Mimer SQL extension
The support for NATIVE_ERROR is a Mimer SQL extension.


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX