Oppolzer - Informatik / Blog


Blog-Hauptseite      Neuester Artikel      Älterer Artikel      Neuerer Artikel      Älterer gleiche Kategorie      Neuerer gleiche Kategorie

DB2-L - Unklares Verhalten einer DB2-Stored Proc bezüglich ROW_COUNT

Subject:

sqlerrd(3) and row_count

From:

Bernd Oppolzer <bernd.oppolzer@T-ONLINE.DE>

Reply-To:

DB2 List <db2-l@lists.idug.org>

Date:

2012.10.22 04:14:00


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-----

Blog-Hauptseite      Neuester Artikel      Älterer Artikel      Neuerer Artikel      Älterer gleiche Kategorie      Neuerer gleiche Kategorie