GET DIAGNOSTICS

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.

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.

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.

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

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