Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
How can I get the number of rows updated by an UPDATE SQL statement?
has no ratings.
Published: 04 Sep 2014
Revised: 04 Sep 2014 - 1139 days ago
Last viewed on: 16 Oct 2017 (2618 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

How can I get the number of rows updated by an UPDATE SQL statement? Published by: Chris Proctor on 04 Sep 2014 view comments(4)

Good morning. I have the need to retrieve the number of rows updated by and UPDATE SQL statement. I see there's a ROW_COUNT available, but I'm not sure if that's what I should use. I also can't find an example of the syntax, if that is the way to go. Can someone give me a clue? ;-)

Thanks!

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: chrisp
Premium member *
Portland, OR
Comment on: How can I get the number of rows updated by an UPDATE SQL statement?
Posted: 3 years 1 months 12 days 11 hours 49 minutes ago

I just found something, don't know if it's correct. Will this work following my UPDATE?

  

exec sql

get diagnostics :rcount = row_count;

 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: How can I get the number of rows updated by an UPDATE SQL statement?
Posted: 3 years 1 months 12 days 11 hours 44 minutes ago

In Theory, the row count is returned by the diagnostics statement that you found, but in general, the number of rows updated is returned to the SQLERR array as the first two bytes (as an integer). Do the UPDATE, then look at the SQLERR in debug, as follows:

EVAL  SQLERR:X

And see if it looks like the correct number of rows.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: How can I get the number of rows updated by an UPDATE SQL statement?
Posted: 3 years 1 months 12 days 11 hours 24 minutes ago

Will do. Thanks, Bob!

Posted by: DaleB
Premium member *
Reading, PA
Comment on: How can I get the number of rows updated by an UPDATE SQL statement?
Posted: 3 years 1 months 12 days 11 hours 19 minutes ago
Edited: Thu, 04 Sep, 2014 at 13:52:27 (1139 days ago)

That's it. ROW_COUNT is updated by UPDATE, DELETE, and INSERT. It's in Diagnostics Area (see GET DIAGNOSTICS), or SQLERRD(3) in the SQLCA. ROW_COUNT is just the UPDATE itself; it does not include other rows affected by triggers or referential constraints. If you want the secondary rows affected, then DB2_ROW_COUNT_SECONDARY, which is SQLERRD(5).

If you're embedding it in RPG, The SQLCA is declared automatically, and RPG does the GET DIAGNOSTICS for you. You just need to access the appropriate field.

The SQLCA has an OVERLAY. SQLERRD() array is 10I 0. It OVERLAYs SQLER3 and SQLER5 (etc.), which are 9B0. I'd go with the SQLERRD(), just because I prefer the integer over the older binary.

Exec SQL UPDATE mylib.mytable
           SET field1 = field1 + 1
           WHERE field2 = 'x';
rcount = SQLERRD(3);

Note that the assignment of rcount from SQLERRD(3) is a normal RPG statement, not an Exec-SQL.