Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

GET DIAGNOSTICS

Gets statement or condition information from the diagnostics area.



where statement-information is:



and condition-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, 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, Appendix C, 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, method 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, method or procedure in which the condition was raised.
ROUTINE_NAME
NCHAR VARYING(128)
The name of the function, method 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, method 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-2011
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.

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