Updating Cursor row withour using FOR UPDATE

[Chandan Yadav]

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]

Locked - already asked on another forum.