Updating Cursor row withour using FOR UPDATE
Posted: Tue Nov 08, 2016 11:20 am
Hi All,
My cursor query is as below
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
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
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.
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