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