Statement information

The GET DIAGNOSTICS statement returns information about the most-recently executed SQL statement.

This form of the GET DIAGNOSTICS statement has the following general syntax:
EXEC SQL get diagnostics statement_fields;
The following table summarizes the statement_fields of the diagnostics area.
Table 1. Statement information from the GET DIAGNOSTICS statement
Field-name keyword ESQL/C data type Description
NUMBER mint This field holds the number of exceptions that the diagnostics area contains for the most-recently executed SQL statement. NUMBER is in the range of 1 to 35,000. Even when an SQL statement is successful, the diagnostics area contains one exception.
MORE char[2] This field holds either an N or a Y (plus a null terminator). An N character indicates that the diagnostics area contains all of the available exception information. A Y character indicates that the database server has detected more exceptions than it can store in the diagnostics area. Now, the database server always returns an N because the database server can store all exceptions.
ROW_COUNT mint When the SQL statement is an INSERT, UPDATE, or DELETE, this field holds a numeric value that specifies the number of rows that the statement has inserted, updated, or deleted. ROW_COUNT is in the range of 0 to 999,999,999.

For any other SQL statement, the value of ROW_COUNT is undefined.

The following figure shows a GET DIAGNOSTICS statement that retrieves statement information for a CREATE TABLE statement into the host variables :exception_count and :overflow.
Figure 1: Using GET DIAGNOSTICS to return statement information
EXEC SQL BEGIN DECLARE SECTION;
   mint exception_count;
   char overflow[2];
EXEC SQL END DECLARE SECTION;
;

EXEC SQL create database db;

EXEC SQL create table tab1 (col1 integer);
EXEC SQL get diagnostics :exception_count = NUMBER,
   :overflow = MORE;

Use the statement information to determine how many exceptions the most-recently executed SQL statement has generated.

For more information about the statement fields of the diagnostics area, see “The Statement Clause” in the GET DIAGNOSTICS statement in the HCL OneDB™ Guide to SQL: Syntax.