|
View previous topic :: View next topic
|
| Author |
Message |
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
I have below operation, in which I code 'COMMIT' in the main program, and prgram run successfully:
MAIN program:
1. declare C1 SENSITIVE SCROLL CURSOR WITH HOLD FOR
SELECT ...
FROM..
FOR UPDATE
2. OPEN C1
3. FETCH ABSOLUTE 3 FROM C1
4. UPDATE ... WHERE CURRENT OF C1
5. COMMIT WORK
6. FETCH NEXT RECORD FROM C1
7. UPDATE ... WHERE CURRENT OF C1
8. COMMIT WORK.
while when I code 'COMMIT' in a subprogram, and the main program calls the subprogram, SQLCODE -508 error occurred when perform POSITIONED-UPDATE against the second fetched row.
MAIN program:
1. declare C1 SENSITIVE SCROLL CURSOR WITH HOLD FOR
SELECT ...
FROM..
FOR UPDATE
2. OPEN C1
3. FETCH ABSOLUTE 3 FROM C1
4. UPDATE ... WHERE CURRENT OF C1
5. call subprogram to commit
6. FETCH NEXT RECORD FROM C1
7. UPDATE ... WHERE CURRENT OF C1 ==>SQLCODE -508 error here!!!
8. call subprogram to commit
subprogram:
COMMIT WORK
I examed the description of SQLCODE -508, but quite don't understand why.
Can anybody please help on this issue?
Is there limitation for commit in a subprogram? |
|
| Back to top |
|
 |
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
| sorry, I forgot to state that I have other SQL STATEMENT which performs UPDATE against some table other than the table that the main program is using. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| After calling SUBPROGRAM did you check if the cursor was active? |
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
| how to check if the cursor is active or not? |
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
I found if I I code like below, SQLCODE -508 error also occurrs.
MAIN program:
1. declare C1 SENSITIVE SCROLL CURSOR WITH HOLD FOR
SELECT ...
FROM..
FOR UPDATE
2. OPEN C1
3. FETCH ABSOLUTE 3 FROM C1
4. UPDATE ... WHERE CURRENT OF C1
5. FETCH NEXT RECORD FROM C1
6. COMMIT WORK
7. UPDATE ... WHERE CURRENT OF C1 ==>SQLCODE -508 error here!!!
why cursor does not position currectly after FETCH operation? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
Did you test sqlcode after
6. FETCH NEXT RECORD FROM C1
? |
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
| GuyC wrote: |
Did you test sqlcode after
6. FETCH NEXT RECORD FROM C1
? |
I evaluated SQLCODE immediately after each SQL statement, even COMMIT. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
If this code issues COMMITs at verious places, i expect there will be ugly surprises for a Long time.
I believe if this is implemented as production, it will cause lots of problems because of "timing". I would expect that sometimes data will be inconsistent. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Also Thumb rule to do is never commit in sub programs. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
Thanks GuyC,
I tried with STATIC option, it works.
But something I quite don't understand:
| Quote: |
Declaring a cursor as SENSITIVE DYNAMIC has the following effects:
•When the application executes positioned UPDATE and DELETE statements with the cursor, those changes are visible. In addition, when the application executes insert, update, or delete operations (within the application but outside the cursor), those changes are visible.
|
According to the description, SENSITIVE DYNAMIC should also work? Am I right? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
No, Dynamic means that any changes to the DB2 table are visible in the cursor. it says nothing about where the cursor is positioned after a commit. the next paragraph does :
| Quote: |
After a commit operation, the position of a held cursor depends on its type:
•A non-scrollable cursor that is held is positioned after the last retrieved row and before the next logical row. The next row can be returned from the result table with a FETCH NEXT statement.
•A static scrollable cursor that is held is positioned on the last retrieved row. The last retrieved row can be returned from the result table with a FETCH CURRENT statement.
•A dynamic scrollable cursor that is held is positioned after the last retrieved row and before the next logical row. The next row can be returned from the result table with a FETCH NEXT statement. DB2® returns SQLCODE +231 for a FETCH CURRENT statement. |
if it isn't ON a row you would get -508 when attempting an update current. |
|
| Back to top |
|
|
|
|
