SQL Reference

GET DIAGNOSTICS Statement

The GET DIAGNOSTICS statement obtains information about the previous SQL statement invoked.

Syntax

>>-GET DIAGNOSTICS--SQL-variable-name--=----+-ROW_COUNT-----+--><
                                            '-RETURN_STATUS-'
 

Description

SQL-variable-name

Identifies the variable that is the assignment target. The variable must be an integer variable. SQL variables can be defined in a compound statement.

ROW_COUNT

Identifies the number of rows associated with the previous SQL statement that was invoked. If the previous SQL statement is a DELETE, INSERT, or UPDATE statement, ROW_COUNT identifies the number of rows deleted, inserted, or updated by that statement, excluding rows affected by either triggers or referential integrity constraints. If the previous statement is a PREPARE statement, ROW_COUNT identifies the estimated number of result rows in the prepared statement.

RETURN_STATUS

Identifies the status value returned from the stored procedure associated with the previously executed SQL statement, provided that the statement was a CALL statement invoking a procedure that returns a status. If the previous statement is not such a statement, the value returned has no meaning and could be any integer.

Rules

• The GET DIAGNOSTICS statement does not change the contents of the diagnostics area (SQLCA). If an SQLSTATE or SQLCODE special variable is declared in the SQL procedure, these are set to the SQLSTATE or SQLCODE returned from issuing the GET DIAGNOSTICS statement.

Examples

In an SQL procedure, execute a GET DIAGNOSTICS statement to determine how many rows were updated.

     CREATE PROCEDURE sqlprocg (IN deptnbr VARCHAR(3)) 
        LANGUAGE SQL 
        BEGIN
           DECLARE SQLSTATE CHAR(5);
           DECLARE rcount INTEGER;
           UPDATE CORPDATA.PROJECT
              SET PRSTAFF = PRSTAFF + 1.5
              WHERE DEPTNO = deptnbr;
           GET DIAGNOSTICS rcount = ROW_COUNT;
-- At this point, rcount contains the number of rows that were updated.
    ...
        END

Within an SQL procedure, handle the returned status value from the invocation of a stored procedure called TRYIT that could either explicitly RETURN a positive value indicating a user failure, or encounter SQL errors that would result in a negative return status value. If the procedure is successful, it returns a value of zero.

   CREATE PROCEDURE TESTIT ()
     LANGUAGE SQL
     A1:BEGIN
        DECLARE RETVAL INTEGER DEFAULT 0;
        ...
        CALL TRYIT;
        GET DIAGNOSTICS RETVAL = RETURN_STATUS;
        IF RETVAL <> 0 THEN
           ...
           LEAVE A1;
        ELSE
           ...
        END IF;
      END A1