The GET DIAGNOSTICS statements are used to get and handle errors and warnings at SQL runtime. There are three GET DIAGNOSTICS statements:
GET DIAGNOSTICS NUMBER
GET DIAGNOSTICS EXCEPTION MESSAGE_TEXT
GET DIAGNOSTICS ROW_COUNT
|
Embedded Mode P |
Dynamic Mode |
Interactive Mode |
The GET DIAGNOSTICS statements are used in combination with the SQLCODE variable to handle runtime SQL errors or warnings. The host variable in the GET DIAGNOSTICS NUMBER statement is of type integer and indicates how many errors or warnings are present for the executed SQL statement. The GET DIAGNOSTICS EXCEPTION MESSAGE TEXT statement has 3 host variables, a character string which will contain the error or warning text message, an integer containing the length of the host variable receiving the error or warning text message and an integer counter containing the message number to be retrieved.
GET DIAGNOSTICS statements should be used if the SQLCODE is not equal 0 and has one of the following values:
+100 indicating that no data were found.
< 0 indicating that a run-time SQL error occurred, or
> 0 indicating that SQL warnings have occurred.
The GET DIAGNOSTICS NUMBER statement should be used first to determine how many errors or warnings were received. This number can then be used as the step number in an application loop in which the GET DIAGNOSTICS MESSAGE TEXT statement is used to get the error or warning message text.
The GET DIAGNOSTICS ROW_COUNT statement may be used immediately after an INSERT, UPDATE, DELETE, SELECT INTO or FETCH statement to determine the number of rows affected by the preceding statement. The host variable to receive the row count should be of type integer.
None.
None.
Example
EXEC SQL BEGIN DECLARE SECTION;
int conditionCount;
int errNumber = 0;
int errLen = 512;
char errBuf [512];
EXEC SQL END DECLARE SECTION;
EXEC SQL GET DIAGNOSTICS :conditionCount = NUMBER;
for (errNumber = 1; errNumber <= conditionCount; errNumber++)
{
memset (errBuf, '\0', sizeof(errBuf));
EXEC SQL GET DIAGNOSTICS EXCEPTION
:errNumber
:errBuf = MESSAGE_TEXT,
:errLen = MESSAGE_LENGTH;
printf ("%s\n", errBuf);
EXEC SQL BEGIN DECLARE SECTION;
int rowCount;
EXEC SQL END DECLARE SECTION;
EXEC SQL GET DIAGNOSTICS :rowCount = ROW_COUNT;