GET DIAGNOSTICS ascertains information on an SQL statement executed beforehand in a routine and enters this in a procedure parameter of the type INOUT or OUT or a local variable. The information relates to the statement itself of to the database objects affected by it.
GET DIAGNOSTICS changes neither the content nor the sequence of diagnostics areas. In other words GET DIAGNOSTICS statements which follow each other evaluate the same diagnostic information.
GET DIAGNOSTICS is one of the diagnostic statements, see "Diagnostic information in routines".
GET [CURRENT | STACKED] DIAGNOSTICS
{ statement_info [, statement_info ] ...] |
CONDITION condition_info [, condition_info ]... }
statement_info ::= name1 = ROW_COUNT
condition_info ::= name2 =
{
CONDITION_IDENTIFIER |
RETURNED_SQLSTATE |
MESSAGE_TEXT |
MESSAGE_LENGTH |
MESSAGE_OCTET_LENGTH
}
name1, name2 ::= { local_variable | routine_parameter }
CURRENT
The diagnostic information for the SQL statement most recently executed is output.
Normally this statement is used to output the diagnostic information of an SQL statement executed without error.
However, the SQL statement can also have executed an exception routine with exception handling CONTINUE after an SQLSTATE (see "Local exception routines"), and GET DIAGNOSTICS is the next statement in the routine.A local exception routine has its own diagnostics area. CURRENT outputs the diagnostic information of the SQL statement executed most recently in the exception routine. The diagnostic information of the initiating SQL statement is output with STACKED.
STACKED
The diagnostic information of the SQL statement whose SQLSTATE triggered the exception routine is output.
STACKED may be specified only in a local exception routine.
name1, name2
name1 and name2 are the names of local variables, or procedure or UDF parameters in which the information written after the equals sign is entered.
The data type of name1 or name2 must be compatible with the data type of the information to be entered. The rules in section "Entering values in a procedure parameter (output) or local variable" apply.
name1=ROW_COUNT
name1 is assigned the number of processed rows of the subsequent successfully executed SQL statement: insert_statement, update_searched_statement,
delete_searched_statement, merge_statement. Otherwise the value is undefined.
Data type: DECIMAL(31)
name2=CONDITION_IDENTIFIER
name2 is, if necessary, assigned the name of the condition reported by a SIGNAL or RESIGNAL statement. Otherwise a string with the length 0 is assigned.
Data type: VARCHAR(31)
name2=RETURNED_SQLSTATE
name2 is, if necessary, assigned the value of the reported SQLSTATE. Otherwise a string with the length 0 is assigned.
Data type: VARCHAR(5)
name2=MESSAGE_TEXT
name2 is, if necessary, assigned the message text if MESSAGE_TEXT was specified in the SIGNAL or RESIGNAL statement. Otherwise a string with the length 0 is assigned.
Data type: VARCHAR(120)
name2=MESSAGE_LENGTH
name2 is, if necessary, assigned the length of the message text if MESSAGE_TEXT was specified in the SIGNAL or RESIGNAL statement. Otherwise the value 0 is assigned.
Data type: INTEGER
name2=MESSAGE_OCTET_LENGTH
name2 is, if necessary, assigned the length of the message text in bytes if MESSAGE_TEXT was specified in the SIGNAL or RESIGNAL statement. Otherwise the value 0 is assigned.
Data type: INTEGER
Examples (see also "Diagnostic information in routines" )
Outputting diagnostic information of the last SQL statement:
GET CURRENT DIAGNOSTICS counter1=ROW_COUNT;
Outputting diagnostic information of the SQL statement which triggered the exception routine:
GET STACKED DIAGNOSTICS CONDITION
var1=RETURNED_SQLSTATE,
var2=MESSAGE_LENGTH, var3=MESSAGE_TEXT;
See also
COMPOUND, CREATE FUNCTION, CREATE PROCEDURE, RESIGNAL, SIGNAL