Updating Cursor row withour using FOR UPDATE

RDBMS from IBM.
Previous topicNext topic

Topic Author
Chandan Yadav
Website Team
Website Team
Posts: 69
Joined: Wed Jul 31, 2013 10:19 pm
Zodiac: Libra

Updating Cursor row withour using FOR UPDATE

Post by Chandan Yadav » Tue Nov 08, 2016 11:20 am

Hi All,

My cursor query is as below

Code: Select all

EXEC SQL
    DECLARE CUR2 CURSOR FOR
       SELECT COL1, COL2
           FROM TABLE1
      WHERE  COL3 = 'ABC'
END-EXEC.

For table has only 1 row satisfying above criteria with COL1 = 'XYZ'

Below are the steps I am following
1. Open the cursor
2. Fetch the cursor which will eventually give only one row
3. depending on some condition run below update query

Code: Select all

EXEC SQL
    UPDATE TABLE1
       SET  COL1 = 'DEF'
   WHERE COL3='ABC' AND COL1='XYZ'
END-EXEC.
4. Go and fetch the cursor again I was expecting the cursor to return sqlcode 100 but its retruning the row with COL1 = 'DEF'

When I tried same process with using FOR UPDATE and CURRENT OF step 4 above returning sqlcode 100 as expected ans I am aware this is the ideal way to do it which I am following now

I know I am definitely missing something here but not able to figure out what I am missing

My Bind option are as below
ISOLATION LEVEL - CS
CURRENT DATA - YES

Is it something do with CURRENT DATA? I tried to search in manuals but did not find satisfactory answers

Thanks,
Chandan




nicc
Global Moderator
Global Moderator
Posts: 597
Joined: Wed Apr 23, 2014 8:45 pm

Re: Updating Cursor row withour using FOR UPDATE

Post by nicc » Tue Nov 08, 2016 2:56 pm

Locked - already asked on another forum.


Regards
Nic

Previous topicNext topic

Return to “IBM DB2.”