Oppolzer - Informatik / Blog

D.S. wrote:

If we are talking about a native procedure than I am correct... just
verified it again. For a native procedure EVERYTHING is server-side.
Here's my test...


P1: BEGIN

DECLARE SQLCODE INTEGER DEFAULT 0;

DECLARE TEST_FLG CHAR(1);

-- Body begins here.

SET P_COUNT = 0;

DELETE FROM DEPT WHERE ADMRDEPT = 'A00';

SET TEST_FLG = 'Y';

GET DIAGNOSTICS P_COUNT = ROW_COUNT;

ROLLBACK;

END P1


When I run the code above my output parameter contains the value 0.

When I comment out the "SET TEST_FLG = 'Y'" statement I get a count of 5
(the actual number deleted).

Using the DIAGNOSTICS and SQLCODE in a native procedure is tricky stuff.
It doesn't behave the way us COBOL people expect. Check out the use of
handlers to trap errors and STACKED DIAGNOSTICS to make sure you are
getting the information you expect.

Cheers.




From: P.V.
Sent: Sunday, October 21, 2012 11:00 AM
To: DB2-L@lists.idug.org
Subject: [DB2-L] - RE: sqlerrd(3) and row_count

David,

I don't believe this is true: only server-side SQL statements (DML, DDL
and DCL) reset the diagnostics; SQL PL statements like IF or SET don't.

In Reply to D.S.:

The diagnostics are reset after ever SQL statement. In SQL PL
EVERYTHING is an SQL statement. The IF statement checking SQLCODE is
resetting the diagnostics.


-----End Original Message-----