Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
Updating the result set in a stored procedure
has no ratings.
Published: 07 Jan 2013
Revised: 23 Jan 2013 - 2314 days ago
Last viewed on: 25 May 2019 (3222 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.

Updating the result set in a stored procedure Published by: Chris Proctor on 07 Jan 2013 view comments(1)

Good morning! I have a stored procedure where I am selecting records with a status of '0'. Once the stored procecedure has selected the records with '0' status, I now have to change the status to '1' (processed).

Now, I know that if I use "where current of myCursor" for the UPDATE command, they will not get updated because they are locked by the cursor. On the other hand, if I just say UPDATE status to '1' where "status = '0'" it will update the records, but they will no longer be returned in the result set because the status is no longer '0'.

Is there a way of getting around this? Perhaps calling another stored procedure, or submitting something to update the status?

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

COMMENTS

(Sign in to Post a Comment)
Posted by: Ringer
Premium member *
Comment on: Updating the result set in a stored procedure
Posted: 6 years 4 months 16 days 23 hours 38 minutes ago

> if I use "where current of myCursor" for the UPDATE command, they will not

> get updated because they are locked by the cursor. 

Why? If you have the row locked, you can update the record.

Did you add FOR UPDATE OF STATUS to the SELECT statement? That fetched row is locked until you fetch the next record, hit end of file, close the cursor, or the program ends and was compiled with CLOSQLCSR=*ENDMOD.

Chris Ringer