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.