View previous topic :: View next topic
|
Author |
Message |
simha_it
New User
Joined: 05 May 2005 Posts: 30
|
|
|
|
Hi,
Here I am giving step by step what I am doing now. At the end I will explain about the problem.
1. I am issuing one cursor on table A with 'For Update' clause (where Status = 'N').
2. calling one third party module (say X) which internally issuing 'Commint' up on successful call (this is not in our control. we r just isuing a call).
3. Inserting one row on table A
4. Updating same table A with status = 'D'
while updating the cursor, we are getting an error saying that, cursor you are using in UPDATE or DELETE is not Opened (bcoz issuing commit in step 2).
I have modified the cursor with WITH HOLD option and kept 'For Update' clause as it is. And executed above 4 steps. But now we are getting different error (SQL CODE = -508 which means cursor used in UPDATE or Delete is not pointing to a proper row) while updating the table A. I hope this is clear...
Can anyone suggest how to achieve this.
Note: I can't avoid Commit (in step 2)
Thanks
simha |
|
Back to top |
|
|
Soumik Das
New User
Joined: 06 Aug 2011 Posts: 25 Location: India
|
|
|
|
Show us some of the parts of the code. That will help us analyze it further.
Anyway, why don't you commit after the update.
Judging by the steps you discussed you can do something like this
declare cursor with hold for select (use the select to get the non-unique keys)
open cursor
fetch cursor
do while(sqlcode NE 0)
insert entry
update entry (using the non-unique keys)
issue commit
fetch cursor
end do
close cursor
This is the way I have been doing it. Works pretty well |
|
Back to top |
|
|
simha_it
New User
Joined: 05 May 2005 Posts: 30
|
|
|
|
After insert, I was not able to do an update. It is throwing an error during the update itself. If I use With hold option, I am getting SQL CODE = -508. If I don't use with hold option, getting SQL CODE = -507 (cursor not open). As I mentioned earlier, after fetch I am making call to third party module which is issuing a commit. |
|
Back to top |
|
|
Soumik Das
New User
Joined: 06 Aug 2011 Posts: 25 Location: India
|
|
|
|
It really looks strange you know!!
Why would you want to insert something in the same table just before an update?
It seems to me:
a) you are not checking for SQLCODE=100 as a loop-end-test condition OR
b) the insert is causing trouble OR
c) the table has no unique index defined.
Atleast paste pieces of the dump where you find the SQLCODE.
Sometime it shows information more than what we keep looking for . |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
you issue a commit after completion of a logical unit of work (this would be a cycle)
actually several -
a logical unit of work is:- fetch cursor
- singelton insert
- update current cursor
after a single cycle
(actually should be many 100's if not 1000's)
you then issue the call for the commit.
you do not commit after a fetch and before an insert.
what does status contain in the inserted row. hopefully not 'N'. |
|
Back to top |
|
|
Soumik Das
New User
Joined: 06 Aug 2011 Posts: 25 Location: India
|
|
|
|
Oh yes thanks dbz..I missed this crucial part
declare cursor with hold for select (use the select to get the non-unique keys)
open cursor
fetch cursor
do while(sqlcode NE 0)
insert entry
update entry (using the non-unique keys)
if UOW_done then issue commit
fetch cursor
end do
close cursor
should re-check stuff after I type... |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Does this mean the issue is solved or is there still some problem?
Quote: |
should re-check stuff after I type... |
Yup, so should we all |
|
Back to top |
|
|
|