|
View previous topic :: View next topic
|
| Author |
Message |
catchyblues
New User
Joined: 28 Dec 2005
Posts: 24
|
|
|
|
Hi,
I'm facing a wierd problem. I'm reading a view which has 10 test records and I'm using a cursor to read through those records one-by-one. I'm able to read from 1st to 10th records without any problem. But after reading the 10th record, the cursor is still positioned at the last record only and not returning a SQL CODE +100 and hence it keeps reading the last record for ever. Facing the same problem even if i have single record in the View. Can anyone tell me where I might be going wrong ?
I'm pasting the sample code which I have used for this.
PERFORM OPEN CURSOR.
IF SQLCODE = 0
PERFORM EC-PROCESS UNTIL W04-CVR-CSR-FLAG = 'Y'
END-IF
EC-PROCESS SECTION.
EXEC SQL
FETCH CVR_CSR
INTO
:TEST-VAR-1,
:TEST-VAR-2
END-EXEC
IF SQLCODE NOT = 0 AND SQLCODE NOT = 100
MOVE SQLSTATE TO W04-OUT-SQLSTATE
GO TO AA-999-EXIT
ELSE
IF SQLCODE = 100
MOVE 'Y' TO W04-CVR-CSR-FLAG
MOVE SQLSTATE TO W04-OUT-SQLSTATE
PERFORM CLOSE-CVR-CSR
ELSE
IF SQLCODE = 0 THEN
MOVE TEST-VAR-2 TO W04-VAR-CID
MOVE TEST-VAR-2 TO W04-VAR-MC
IF W04-VAR-SCH = 2 OR 3 OR 4 OR 9 OR 35 OR 45 THEN
CALL "RTGFPRPC" USING W04-VAR-CID, W04-VAR-PID,
RTGIXML, W04-END-MESSAGE, W04-OUT-MESSAGE,
W04-OUT-SQLCODE, W04-OUT-SQLSTATE
ELSE
IF W04-VAR-SCH = 70 OR 72 OR 79 OR 82 OR 83
OR 84 OR 85 OR 87 OR 91 THEN
CALL "RTGFOTHC" USING W04-VAR-CID,
RTGIXML, W04-END-MESSAGE,
W04-OUT-MESSAGE, W04-OUT-SQLCODE,
W04-OUT-SQLSTATE
END-IF
END-IF
END-IF
END-IF
END-IF |
|
| Back to top |
|
 |
mainframe_96
New User
Joined: 18 Dec 2006
Posts: 13
Location: usa
|
|
|
|
Try Reorganizing the code more like this. Also you are moving test var2 to both fields. I am not sure if you intended to do that.
| Code: |
PERFORM OPEN CURSOR.
You are checking if open cursor was successful
IF SQLCODE = 0
PERFORM Fetch-process until EOF = 'y'
END-IF
Fetch-process.
EXEC SQL
FETCH CVR_CSR
INTO
:TEST-VAR-1,
:TEST-VAR-2
END-EXEC
Checking cursor Fetch sql code
If sqlcode = 0
Perform Move-variables
else
If Sqlcode = 100
Move ?y? to Eof
Display 'end of fetch'
Perform Exit para
else
display 'error and sql codes'
Move-variables.
MOVE TEST-VAR-1 TO W04-VAR-CID .
MOVE TEST-VAR-2 TO W04-VAR-MC .
IF W04-VAR-SCH = 2 OR 3 OR 4 OR 9 OR 35 OR 45 THEN
CALL "RTGFPRPC" USING W04-VAR-CID, W04-VAR-PID,
RTGIXML, W04-END-MESSAGE, W04-OUT-MESSAGE,
W04-OUT-SQLCODE, W04-OUT-SQLSTATE
ELSE
IF W04-VAR-SCH = 70 OR 72 OR 79 OR 82 OR 83
OR 84 OR 85 OR 87 OR 91 THEN
CALL "RTGFOTHC" USING W04-VAR-CID,
RTGIXML, W04-END-MESSAGE,
W04-OUT-MESSAGE, W04-OUT-SQLCODE,
W04-OUT-SQLSTATe
end-if. |
|
|
| Back to top |
|
|
catchyblues
New User
Joined: 28 Dec 2005
Posts: 24
|
|
|
|
I don't think the problem is with the restructuring.....I have even restructured the code but wit no effect....
Because the fetch works fine and also the processing further to it. The problem starts when I reach the last record in the view. After reading the last record, the cursor position is still positioned at tht record only and hence it's fetching the last record infinitely. I using IBM Debugger to debug the program.
Cheers
CB |
|
| Back to top |
|
|
vijayamadhuri
Active User
Joined: 06 Apr 2005
Posts: 180
|
|
|
|
| Can u tell us the SQL CODE U R getting after the last record is read? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
You might add a line of code to display the sqlcode on each iteration.
You might also post the other code involved.
How sure are you that you are actually reading the last row repeatedly or simply re-processing the values already in the variables. |
|
| Back to top |
|
|
catchyblues
New User
Joined: 28 Dec 2005
Posts: 24
|
|
|
|
The sqlcode on the fetch is returning Zero instead of +100. Even after the last record has been reached, when the control comes to that section, the Fetch still retrieves the last record(with SQL Code 0) instead of returning a SQL code +100.
It's only the Fetch on the last record which is a problem. For ex:- if I have 10 matching records in the View, the cursor retrieves each of the 10 records one-by-one. But after the last record has been fetched, the cursor is still positioned at the last recrod and hence fetching that record only. And If i have only one record in the view, the cursor keeps on fetching the same record. Hope I'm making myself clear here....
Repasting the code for your reference
PERFORM OPEN CURSOR.
IF SQLCODE = 0
PERFORM EC-PROCESS UNTIL W04-CVR-CSR-FLAG = 'Y'
END-IF
EC-PROCESS SECTION.
EXEC SQL
FETCH CVR_CSR
INTO
:TEST-VAR-1,
:TEST-VAR-2
END-EXEC
IF SQLCODE NOT = 0 AND SQLCODE NOT = 100
MOVE SQLSTATE TO W04-OUT-SQLSTATE
GO TO AA-999-EXIT
ELSE
IF SQLCODE = 100
MOVE 'Y' TO W04-CVR-CSR-FLAG
MOVE SQLSTATE TO W04-OUT-SQLSTATE
PERFORM CLOSE-CVR-CSR
ELSE
IF SQLCODE = 0 THEN
MOVE TEST-VAR-1 TO W04-VAR-CID
MOVE TEST-VAR-2 TO W04-VAR-MC
IF W04-VAR-SCH = 2 OR 3 OR 4 OR 9 OR 35 OR 45 THEN
CALL "RTGFPRPC" USING W04-VAR-CID
ELSE
IF W04-VAR-SCH = 70 OR 72 OR 79 OR 82 OR 83
OR 84 OR 85 OR 87 OR 91 THEN
CALL "RTGFOTHC" USING W04-VAR-CID,
END-IF
END-IF
END-IF
END-IF
END-IF |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
When you do this
| Quote: |
You might also post the other code involved.
|
we may be able to offer suggestions.
You need to at least post the cursor declaration and all of the cursor processing and the variable definitions referenced by the cursor and other sql code.
You might also check for other references to sqlcode and make sure that nothing is moving zeros to it. After each iteration, you might want to move some constant value to the test-vars to see if the code really reads the last row repeatedly or is running on whatever was left in the variables. |
|
| Back to top |
|
|
catchyblues
New User
Joined: 28 Dec 2005
Posts: 24
|
|
|
|
attaching the text file(rtg010.txt) containing most of the code.......
btw, I'm checking for the sqlcode at the end of the fetch and if it's zero, i'm doin further processing. Also checking for sqlcode 100, to exit the processing......And I'm pretty sure that the fetch is failing at the last record. If it were repeatedly running on the values left-over in the variables, then it wld happen for each and every record in the view. But that's not case as the fetch very much retrieves all the relevant record until the last one.....
I hope i'm clear wit the problem i'm facing.... n thnx a lot for ur patience
cheers
CB
| dick scherrer wrote: |
Hello,
When you do this
| Quote: |
You might also post the other code involved.
|
we may be able to offer suggestions.
You need to at least post the cursor declaration and all of the cursor processing and the variable definitions referenced by the cursor and other sql code.
You might also check for other references to sqlcode and make sure that nothing is moving zeros to it. After each iteration, you might want to move some constant value to the test-vars to see if the code really reads the last row repeatedly or is running on whatever was left in the variables. |
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
I'll try to look thru the source later this evening, but i believe we are not in sync with this
| Quote: |
| And I'm pretty sure that the fetch is failing at the last record. If it were repeatedly running on the values left-over in the variables, then it wld happen for each and every record |
It is my guess that the fetches all work until the end. After all of the rows have been fetched, the program continues to run and is either not getting or is "clearing" the 100 and acting like a row was fetched. If code is put in to force some constant into the ws variables where the database data is placed and those variables are displayed on each iteration, it can be deterined if the last row is repeatedly being fetched/returned somehow or if the code is simply using the values from the last successful read.
You haven't moved anything to the sql control info have you? There's no FETCH CURRENT by chance? Just guesses without having looked at the code. . . |
|
| Back to top |
|
|
catchyblues
New User
Joined: 28 Dec 2005
Posts: 24
|
|
|
|
Hi Scherrer,
Could you get some time to look through my code please ?
cheers
CB |
|
| Back to top |
|
|
prasadvrk
Active User
Joined: 31 May 2006
Posts: 200
Location: Netherlands
|
|
|
|
| Quote: |
IF SQLCODE = 100 THEN
MOVE 'Y' TO W04-CVR-CSR-FLAG
MOVE 'NO VALID COVERS FOUND ON THIS POLICY' TO
W04-OUT-MESSAGE
MOVE SQLSTATE TO W04-OUT-SQLSTATE
MOVE 'Y' TO W04-CVR-CSR-FLAG
EXEC SQL
CLOSE CVR_CSR
END-EXEC
|
catchyblues,
If the SQLCODE =+100, you are closing the cursor which is executing successfully and returning SQLCODE=0, so your original SQLCODE from fetch which was +100 is now over written by 0. I hope this is enough to make you fetch one more time.
Think it over and let me know if my observation helped |
|
| Back to top |
|
|
catchyblues
New User
Joined: 28 Dec 2005
Posts: 24
|
|
|
|
hi,
My cursor wasn't returning a SQLCODE 100 for the fetch and that's the main problem.... If the control comes to the piece of code u've mentioned, then CVR-CSR-FLAG will then set to be 'Y' and the control will not return to the FETCH para as I'm doing the perform process until the flag is set to Y.... i hope i'm rite here....
| prasadvrk wrote: |
| Quote: |
IF SQLCODE = 100 THEN
MOVE 'Y' TO W04-CVR-CSR-FLAG
MOVE 'NO VALID COVERS FOUND ON THIS POLICY' TO
W04-OUT-MESSAGE
MOVE SQLSTATE TO W04-OUT-SQLSTATE
MOVE 'Y' TO W04-CVR-CSR-FLAG
EXEC SQL
CLOSE CVR_CSR
END-EXEC
|
catchyblues,
If the SQLCODE =+100, you are closing the cursor which is executing successfully and returning SQLCODE=0, so your original SQLCODE from fetch which was +100 is now over written by 0. I hope this is enough to make you fetch one more time.
Think it over and let me know if my observation helped |
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| After each iteration, you might want to move some constant value to the test-vars to see if the code really reads the last row repeatedly or is running on whatever was left in the variables. |
Have you done this yet? It will be interesting to see the outcome. |
|
| Back to top |
|
|
catchyblues
New User
Joined: 28 Dec 2005
Posts: 24
|
|
|
|
Hi,
I couldn't get to test it again as i had further updations to make on other programs......
Btw did u get time to read through the code for any obvious mistakes ?
| dick scherrer wrote: |
Hello,
| Quote: |
| After each iteration, you might want to move some constant value to the test-vars to see if the code really reads the last row repeatedly or is running on whatever was left in the variables. |
Have you done this yet? It will be interesting to see the outcome. |
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
When this bubbles back near the top of your priorities, run the test and post what the results are. A few additional testing thoughts follow:
Something i do not see in the code is some kind of test to see if you are in a loop and if so, stop the run. I would think that checking for 1000 (or some better number) and terminating would be a good idea while you are working on this situation. I also do not see any diagnostic displays - without them, how did you determine you looping at end of set?
The code sometimes moves info to "OUT_MESSAGE" and sometimes to "END MESSAGE". Might this cause any confusion?
After this sql
| Code: |
EXEC SQL
FETCH CVR_CSR
INTO
:M-COVR-COVER-ID:W04-VAR-CVRID-NULL-IND,
:W04-MODCODE:W04-VAR-MODCODE-NULL-IND,
:W04-CVRCODE:W04-VAR-CVRCODE-NULL-IND,
:W04-SCHDNO:W04-VAR-SCHDNO-NULL-IND
END-EXEC |
it may help to add a display to show the sqlcode and the "fetched" fields. The display should be placed before the next statement
| Code: |
IF SQLCODE = 0 THEN
MOVE M-COVR-COVER-ID TO W04-VAR-CID |
This test should demonstrate whether a 100 is ever returned or if something else is going on. When you have time to test, someone will be here.
It may also help if you post your sqlca copybook. |
|
| Back to top |
|
|
Bitneuker
CICS Moderator
Joined: 07 Nov 2005
Posts: 1104
Location: The Netherlands at Hole 19
|
|
|
|
| Why not test the SQLCODE for +100 instead of 100? You also test for -8xx. Couldn't find in your download how SQLCODE is defined. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hi George,
It is in the copybook that maybe we'll see. . . |
|
| Back to top |
|
|
catchyblues
New User
Joined: 28 Dec 2005
Posts: 24
|
|
|
|
hi,
i'll try putting display statements for the records fetched and also will check the count at the last fetched record...... will post the test results tomm......
thanks a lot for ur time spent on this.....
thnx once again....
cheers
CB
| dick scherrer wrote: |
Hello,
When this bubbles back near the top of your priorities, run the test and post what the results are. A few additional testing thoughts follow:
Something i do not see in the code is some kind of test to see if you are in a loop and if so, stop the run. I would think that checking for 1000 (or some better number) and terminating would be a good idea while you are working on this situation. I also do not see any diagnostic displays - without them, how did you determine you looping at end of set?
The code sometimes moves info to "OUT_MESSAGE" and sometimes to "END MESSAGE". Might this cause any confusion?
After this sql
| Code: |
EXEC SQL
FETCH CVR_CSR
INTO
:M-COVR-COVER-ID:W04-VAR-CVRID-NULL-IND,
:W04-MODCODE:W04-VAR-MODCODE-NULL-IND,
:W04-CVRCODE:W04-VAR-CVRCODE-NULL-IND,
:W04-SCHDNO:W04-VAR-SCHDNO-NULL-IND
END-EXEC |
it may help to add a display to show the sqlcode and the "fetched" fields. The display should be placed before the next statement
| Code: |
IF SQLCODE = 0 THEN
MOVE M-COVR-COVER-ID TO W04-VAR-CID |
This test should demonstrate whether a 100 is ever returned or if something else is going on. When you have time to test, someone will be here.
It may also help if you post your sqlca copybook. |
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
You're welcome 
As i mentioned, someone will be here. . . |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
So long as the copybook at their location hasn't been "customized" - not likely, i suppose, but possible. . .
For COBOL, (from the same manual) the "standard" is
| Code: |
COBOL:
01 SQLCA.
05 SQLCAID PIC X(8).
05 SQLCABC PIC S9(9) COMP-4.
05 SQLCODE PIC S9(9) COMP-4.
05 SQLERRM.
49 SQLERRML PIC S9(4) COMP-4.
49 SQLERRMC PIC X(70).
05 SQLERRP PIC X(8).
05 SQLERRD OCCURS 6 TIMES
PIC S9(9) COMP-4.
05 SQLWARN.
10 SQLWARN0 PIC X.
10 SQLWARN1 PIC X.
10 SQLWARN2 PIC X.
10 SQLWARN3 PIC X.
10 SQLWARN4 PIC X.
10 SQLWARN5 PIC X.
10 SQLWARN6 PIC X.
10 SQLWARN7 PIC X.
05 SQLEXT.
10 SQLWARN8 PIC X.
10 SQLWARN9 PIC X.
10 SQLWARNA PIC X.
10 SQLSTATE PIC X(5).
|
|
|
| Back to top |
|
|
catchyblues
New User
Joined: 28 Dec 2005
Posts: 24
|
|
|
|
Atlast !!!! i could fix this......
Actually it was the View which i was using created the problem..... The column attributes in the View were created without the Not NULL option and hence the view had lot of NULL rows. So the Fetch was showing the last record which was in the Host variables.
Got over with this problem by checking for the value in the null indicator after the Fetch and exiting the Fetch process when the value is -1(null row) in the null indicator.....
thnx a lot for ur help Scherrer.......
Cheers
CB |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
You're welcome
Good to hear that it is resolved. |
|
| Back to top |
|
|
|
|
