get diagnostics

SQL/PL

Get diagnostic info for a statement or condition

Syntax

getdiags_stmt:
          GET [CURRENT | STACKED] DIAGNOSTICS stmt_info[, stmt_info]...
     |    GET [CURRENT | STACKED] DIAGNOSTICS {EXCEPTION | CONDITION} cond_info[, cond_info}...

stmt_info:
          var_name = stmt_item

stmt_item:
          COMMAND_FUNCTION | COMMAND_FUNCTION_CODE
     |    DYNAMIC_FUNCTION | DYNAMIC_FUNCTION_CODE
     |    MORE | NUMBER | ROW_COUNT | TRANSACTION_ACTIVE

cond_info:
          var_name = cond_item

cond_item:
          RETURNED_SQLSTATE | RETURNED_SQLCODE | MESSAGE_LENGTH | MESSAGE_TEXT
     |    SQLCODE_NAME | CONDITION_IDENTIFIER | CONDITION_NUMBER
     |    CURSOR_NAME | ROUTINE_NAME | SOURCE_FILE
     |    STATEMENT_NUMBER | LINE_NUMBER | COLUMN_NUMBER

Description

The GET DIAGNOSTICS statement is used to retrieve information about either the most recently executed statement or the most recently raised exception or condition.

This statement can be used to retrieve information about the most recently executed statement. It is designed to be used in an exception handler to retrieve information about the statement that signaled the exception or condition and/or to retrieve information about the exception or condition itself. The stmt_info assigns to var_name information about the statement whose execution raised the exception or condition. The cond_info assigns to var_name information about the exception or condition itself.

The data types and descriptions for each stmt_item and cond_item are specified in the table below. The var_name being assigned must have been declared with a compatible data type.

Get Diagnostics Information Items
Item	Data Type	Description
COMMAND_FUNCTION	varchar	A string that identifies the executed SQL statement.
COMMAND_FUNCTION_CODE	smallint	The number code of the executed SQL statement.
DYNAMIC_FUNCTION	varchar	A string that identifies the statement being dynamically prepared or executed.
DYNAMIC_FUNCTION_CODE	smallint	The number code of the statement being dynamically prepared or executed.
NUMBER	smallint	Total number of stacked diagnostics
MORE	char	= "Y" if there were more diagnostics than could be stacked, else "N".
ROW_COUNT	bigint	Returns the number of rows affected by the statement.
TRANSACTION_ACTIVE	smallint	Returns 1 if a transaction is active and 0 if not.
RETURNED_SQLSTATE	char(5)	Returns the five character sqlstate value.
RETURNED_SQLCODE	smallint	Error/Status code integer value
MESSAGE_LENGTH	smallint	Returns the number of characters in the message text.
MESSAGE_TEXT	varchar	Returns the message text.
SQLCODE_NAME	varchar	Error code name string
CONDITION_IDENTIFIER	varchar	The name of the user condition that was signaled
CONDITION_NUMBER	smallint	The number of this diagnostic condition (1st on stack == 1).
CURSOR_NAME	varchar	Returns the associated cursor name if there is one.
ROUTINE_NAME	varchar	The name of the procedure or function in which the condition was raised.
SOURCE_FILE	varchar	The name of the source file that contained the procedure or function.
STATEMENT_NUMBER	smallint	The SQL PL p-code statement number where the condition was raised.
LINE_NUMBER	integer	The line number in the source file where the statement is defined.
COLUMN_NUMBER	integer	The column number in the source file where associated with the condition.

Note that DYNAMIC_FUNCTION[_code] only applies when COMMAND_FUNCTION[_code] is for a PREPARE or EXECUTE statement. Items that return character strings will return an empty string when the requested value does not apply (e.g., dynamic function when a PREPARE or EXECUTE statement has not been executed).

The *_function and *_function_code values for each RaimaDB SQL statement that can be used in a stored routine are given in the following table. These values are defined by the SQL standard.

Note that SOURCE_FILE, STATEMENT_NUMBER, LINE_NUMBER, and COLUMN_NUMBER are non-standard condition items. Note also that the SQL standard defines other statement and condition items that have not been implemented in the RaimaDB SQL GET DIAGNOSTICS statement.

SQL Statement Identifiers and Codes
Statement Identifier String	Code
ASSIGNMENT	5
BEGIN END	12
CALL	7
CASE	86
CLOSE CURSOR	9
COMMIT WORK	11
DEALLOCATE PREPARE	16
DELETE CURSOR	18
DELETE WHERE	19
EXECUTE	44
FETCH	45
FOR	46
IF	88
INSERT	50
ITERATE	102
LEAVE	89
LOOP	90
OPEN	53
PREPARE	56
RELEASE SAVEPOINT	57
REPEAT	95
RESIGNAL	91
RETURN	U
ROLLBACK WORK	62
SAVEPOINT	63
SELECT	21
START TRANSACTION	75
UPDATE CURSOR	81
UPDATE WHERE	82
WHILE	97

The GET DIAGNOSTICS statement described above is a subset of the one specified in the SQL standard.

Example

declare continue handler for sqlexception
begin
    get stacked diagnostics 
        cmd_name = COMMAND_FUNCTION, 
        cmd_code = COMMAND_FUNCTION_CODE,
        dyn_name = DYNAMIC_FUNCTION, 
        dyn_code = DYNAMIC_FUNCTION_CODE;
    get stacked diagnostics condition 1 
        errcode = SQLCODE_NAME, 
        errmsg = MESSAGE_TEXT;
end;