Mimer SQL Reference Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


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 (listed in the Mimer SQL Programmer's Manual).
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 the same as ROUTINE_NAME.
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 VIEW
ALLOCATE DESCRIPTOR
DYNAMIC CLOSE
ALTER DATABANK
DYNAMIC DELETE CURSOR
ALTER DATABANK RESTORE
DYNAMIC FETCH
ALTER IDENT
DYNAMIC OPEN
ALTER SHADOW
DYNAMIC UPDATE CURSOR
ALTER TABLE
ENTER
ASSIGNMENT
EXECUTE
CALL
EXECUTE IMMEDIATE
CLOSE CURSOR
FETCH
COMMENT
GET DESCRIPTOR
COMMIT WORK
GET DIAGNOSTICS
CONNECT
GRANT
CREATE BACKUP
GRANT OBJECT PRIVILEGE
CREATE DATABANK
GRANT SYSTEM PRIVILEGE
CREATE DOMAIN
INSERT
CREATE FUNCTION
LEAVE
CREATE IDENT
LEAVE RETAIN
CREATE INDEX
OPEN
CREATE MODULE
PREPARE
CREATE PROCEDURE
REVOKE
CREATE SCHEMA
REVOKE OBJECT PRIVILEGE
CREATE SEQUENCE
REVOKE SYSTEM PRIVILEGE
CREATE SHADOW
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 DATABANK
SET SESSION START IMPLICIT
DROP DOMAIN
SET SHADOW
DROP FUNCTION
SET TRANSACTION DIAGNOSTIC SIZE
DROP IDENT
SET TRANSACTION ISOLATION LEVEL
DROP INDEX
SET TRANSACTION READ ONLY
DROP MODULE
SET TRANSACTION READ WRITE
DROP PROCEDURE
SET TRANSACTION START EXPLICIT
DROP SCHEMA
SET TRANSACTION START IMPLICIT
DROP SEQUENCE
START TRANSACTION
DROP SHADOW
UPDATE CURSOR
DROP SYNONYM
UPDATE WHERE
DROP TABLE
UPDATE STATISTICS
DROP TRIGGER

Language Elements

target-variable, see Target Variables.

Restrictions

None.

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.

Standard Compliance

Standard
Compliance
Comments
X/Open-95
SQL92
YES
Fully compliant.



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Reference Manual TOC PREV NEXT INDEX