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-2003	Features outside core	Feature F121, "Basic diagnostics management".
	Mimer SQL extension	The support for NATIVE_ERROR is a Mimer SQL extension.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se