Re: SQL Stored Procedure ROW_COUNT

On 17-May-2016 10:01 -0500, dlclark wrote:

On 17-May-2016 09:33 -0500, CRPence wrote:

As Rob suggested, move the second GET DIAGNOSTIC to after the
first. Or better IMO:

Consider, instead of doing two separate GET DIAGNOSTIC requests for
the one DELETE statement, issue just one, immediately following the
DELETE statement; e.g.:

<<SNIPped example>>

Cannot combine ROW_COUNT with GET CURRENT CONDITION. Results in the
following error message.

MSG ID SEV RECORD TEXT
SQL0104 30 132 Position 55 Token ROW_COUNT was not valid.

The SNIPped example showed how to get the effective ROW_COUNT value, i.e. the same value as that stmt-info-item, from the SQLERRD(3); notably, use of the supported condition-information-item of DB2_SQLERRD3, because obtaining the statement-information-item of ROW_COUNT requires a different syntax; the example is reinserted here, but with a comment on the assignment to variable CNT2:

DELETE…
;
GET CURRENT DIAGNOSTICS CONDITION 1
CURSTATE = RETURNED_SQLSTATE
, CNT2 = DB2_SQLERRD3 /* <-- what ROW_COUNT would be */
;

Or [IMO less desirable], the second of two separate GET… statements can be moved directly after the first GET… statement that immediately follows the DELETE statement, so as to be performed consecutively after the DELETE:

DELETE…
;
GET CURRENT DIAGNOSTICS CONDITION 1 CURSTATE = RETURNED_SQLSTATE;
GET DIAGNOSTICS CNT2 = ROW_COUNT;

Is it really necessary to include all of the following text; all
of which will be included in the archives? Please consider adding
the signature delimiter somewhere before the corporate gibberish.

I understand you're complaining about my signature, but I don't know
what "signature delimiter" means.

The convention for the signature delimiter [as documented, IIRC, in either or both of, every mailing and the monthly **admin reminders for the email-list], is a single line on which there are only the three characters [where the term "dash" is synonymous with "hyphen", and the term "space" is synonymous with "blank"]:
dash, dash, and space

[https://en.wikipedia.org/wiki/Signature_block]
"… a signature block … should be delimited from the body of the message by a single line consisting of exactly two hyphens, followed by a space, followed by the end of line (i.e., in C-notation: "-- \n"). …"
[http://ilias.ca/blog/2005/04/a-proper-signature/]
"…
RFC Standard
[ftp://ftp.demon.co.uk/pub/news/doc/so1036.txt]

"signatures”: short closing texts, automatically added
to the end of articles by posting agents, identifying
the poster and giving his network addresses etc.
If a poster or posting agent does append a signature
to an article, the signature SHOULD be preceded with a
delimiter line containing (only) two hyphens (ASCII 45)
followed by one blank (ASCII 32).

…"