GET DIAGNOSTICS

http://developer.mimer.com

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, page 151.

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 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.

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.

Example
 ...
 GET DIAGNOSTICS EXCEPTION 1 v_sqlstate = RETURNED_SQLSTATE;
 ...
 
Standard Compliance

Standard

Compliance

Comments

X/Open-95
SQL-92

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. See the Mimer SQL Programmer's Manual, Appendix B, Return Codes, page 151.
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 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

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

Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se