get diagnostics
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.
| 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 RDM 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 RDM SQL GET DIAGNOSTICS statement.
| 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 sqlexceptionbegin 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;