View previous topic :: View next topic
|
Author |
Message |
darakhshan
New User
Joined: 03 Mar 2008 Posts: 32 Location: India
|
|
|
|
I am facing a problem with the below and i'm just not able to debug it.
i have a cursor from which i need to fetch data, and this i'm performing the para below until end-of-csr is true to achieve it
Code: |
**************************
FETCH-FROM-CURSOR.
**************************
SET NOT-END-OF-CSR TO TRUE.
MOVE ID1 TO ID-ONE
EXEC SQL
FETCH INDVDL
INTO :VAL1
END-EXEC
MOVE SQLCODE TO SQLCODE-CHK
EVALUATE SQLCODE-CHK
WHEN 0
PERFORM DEL-FROM-TABLE
THRU DEL-EXIT
PERFORM INSERT-INTO-TABLE
THRU INSERT-EXIT
WHEN +100
SET END-OF-CSR TO TRUE
WHEN OTHER
SET END-OF-CSR TO TRUE
MOVE SQLCODE OF SQLCA TO SQL-CODE
PERFORM ERROR-ROUTINE-PARA
THRU ERROR-EXIT
MOVE ERR-DISPLAY TO SQL-ERROR-DESC
END-EVALUATE.
FETCH-EXIT.
EXIT. |
the issue i'm facing here is that the control somehow always enters into the "WHEN 0" part.(i checked that by placing displays) although the cursor query when run separately fetches only 2 rows.
the looping is going on and on infinitely, and i'm not able to figure out why. can anyone help? |
|
Back to top |
|
|
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008 Posts: 107 Location: New York
|
|
|
|
Please let us know the definition of SQLCODE-CHK. |
|
Back to top |
|
|
darakhshan
New User
Joined: 03 Mar 2008 Posts: 32 Location: India
|
|
|
|
defined as s9(3). |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
when cursor fetches a row it will return 0 only ...so there is nothing wrong in going to WHEN 0 part ...
Now when its 100 u r setting end-of-cursor to true ..but how are you calling this fetch para in the main section ... post that part of the program ... |
|
Back to top |
|
|
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008 Posts: 107 Location: New York
|
|
|
|
The definition is incorrect.
Actually SQLCODE will be defined as S9(09) COMP which is 5 bytes.
But your definition is S9(03).
So when you get SQLCODE 100, it is getting truncated and always goes to WHEN 0 loop.
Please change the same to S9(09) and try. |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
Suresh,
SQLCODE is defined as S9(9) COMP-5 ....
and S9(3) can accomodate 100 ... i dont think it will get truncated ...
the problem is somewhere outside this .... |
|
Back to top |
|
|
darakhshan
New User
Joined: 03 Mar 2008 Posts: 32 Location: India
|
|
|
|
PERFORM FETCH-FROM-CURSOR UNTIL END-OF-CSR
and that's defined as
01 END-OF-CSR-ONE PIC X(01) VALUE 'N'.
88 END-OF-CSR VALUE 'Y'.
88 NOT-END-OF-CSR VALUE 'N'. |
|
Back to top |
|
|
darakhshan
New User
Joined: 03 Mar 2008 Posts: 32 Location: India
|
|
|
|
also, on displaying the value, i see that only one row is being fetched.. it always fetches the very first row.. and ends up giving an sqlcode of 0. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
I agree with ashimer.
Quote: |
SET NOT-END-OF-CSR TO TRUE
|
this does not make much sense.
don't like perform thru's. they always make me suspect.
are you performing sections or paragraphs. do you have goto's? |
|
Back to top |
|
|
darakhshan
New User
Joined: 03 Mar 2008 Posts: 32 Location: India
|
|
|
|
performing a para.. and no, i have no gotos here.. |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
As you said that it always fetches the first row can you chk where you are opening and closing the cursor and is there any commit in the insert or delete paras ????
This cannot be a strange behaviour by COBOL instead could be some fault in coding .... review your program once again .. |
|
Back to top |
|
|
darakhshan
New User
Joined: 03 Mar 2008 Posts: 32 Location: India
|
|
|
|
i commented out these two
PERFORM DEL-FROM-TABLE
THRU DEL-EXIT
PERFORM INSERT-INTO-TABLE
THRU INSERT-EXIT
and this is not happening..
i have no commits though.. i tried decalring the cursor with hold.. but still the problem persists.. |
|
Back to top |
|
|
darakhshan
New User
Joined: 03 Mar 2008 Posts: 32 Location: India
|
|
|
|
i think this is happening because i'm fetching from the table and then performing insert and delete on the same. can that be the problem? |
|
Back to top |
|
|
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008 Posts: 107 Location: New York
|
|
|
|
That means you are fetching a record from the table, then deleting it and then inserting the same record again in to the table is it? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
It may help if you explain the business requirement that says when a row is "found" you do both a delete and an insert.
More often, i would expect the process to be an insert on a "no hit" and an update or a delete on a "hit". |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
Quote: |
MOVE ID1 TO ID-ONE
EXEC SQL
FETCH INDVDL
INTO :VAL1
END-EXEC
|
Is ID-ONE the key that is being used in the cursor? If it is then that value has to be set before the cursor is opened! |
|
Back to top |
|
|
darakhshan
New User
Joined: 03 Mar 2008 Posts: 32 Location: India
|
|
|
|
This is what i am attempting to do.
My table XYZ has 6 fields- field1, field2, field3, field4, field5, field6. With field1, field2, field3
as inputs, i need to insert a duplicate of the same record into the table XYZ, but i need to change the
value of field6 to 'CURRENT' while inseting this duplicate. However, i am not getting field4, field5 as an input. Also if there are 3 records with this combination in the table, i need to insert 3 duplicate records for each of these.
So in order to insert the duplicate of the said row i need to first fetch field4 and field5
from the same table, and then go ahead with the insert.
My requirement is also that before i insert any record, i should first delete any records with the specified input
having field6 set as 'INVALID'
Example:
TABLE XYZ before operations:
AAA BBB CCC 1 10 OBSOLET
AAA BBB CCC 2 20 OBSOLET
AAA BBB CCC 3 30 OBSOLET
AAA BBB CCC 3 30 INVALID
should now look like
AAA BBB CCC 1 10 OBSOLET
AAA BBB CCC 1 10 CURRENT
AAA BBB CCC 2 20 OBSOLET
AAA BBB CCC 2 20 CURRENT
AAA BBB CCC 3 30 OBSOLET
AAA BBB CCC 3 30 CURRENT
Hence the logic. Hope this explains. |
|
Back to top |
|
|
darakhshan
New User
Joined: 03 Mar 2008 Posts: 32 Location: India
|
|
|
|
Quote: |
Is ID-ONE the key that is being used in the cursor? If it is then that value has to be set before the cursor is opened! |
No, it is not the key that is being used. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
Is there more than one cursor used in the code? Is indvdl updatable?
It might be interesting to put a counhter or display in the open cursor code to make sure the open is only executed once.
Please post the cursor declaration and the delete/insert paragraphs. |
|
Back to top |
|
|
darakhshan
New User
Joined: 03 Mar 2008 Posts: 32 Location: India
|
|
|
|
CURSOR DECLARATION:
EXEC SQL
DECLARE INDVDL CURSOR WITH HOLD FOR
SELECT
FIELD4,
FIELD5
FROM
XYZ
WHERE
FIELD1 = :WS-IN1
AND FIELD2 = :WS-IN2
AND FIELD3 = :WS-IN3
WITH UR
END-EXEC.
DELETE-FROM-TABLE.
EXEC SQL
DELETE FROM XYZ
WHERE
FIELD5 = 'OBSOLET'
AND FIELD1 = :WS-IN1
AND FIELD2 = :WS-IN2
AND FIELD3 = :WS-IN3
END-EXEC.
INSERT-INTO-TABLE.
EXEC SQL
INSERT INTO XYZ
VALUES
(
:WS-IN1,
:WS-IN2,
:WS-IN3,
:VAL1,
:VAL2,
'CURRENT'
)
END-EXEC.
I did put displays.. but the open is happening only once.. only the fetch para is in a loop.. and it fetches the 1st value all the time.. that's why i put a "WITH HOLD" in the cursor, but it did not help.. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
|
|
maybe I overlooked something,
from theposted rows and the code snippets it does not look like You need a cursor
You are getting field1,fielsd2,field3 from somewhere else
so just do a select with a condition on field1 field2 field3 and field6
to get all the other values
if OK
delete the 'INVALID'
insert the 'CURRENT'
but as I said maybe I overlooked something |
|
Back to top |
|
|
darakhshan
New User
Joined: 03 Mar 2008 Posts: 32 Location: India
|
|
|
|
Quote: |
just do a select with a condition on field1 field2 field3 and field6
|
there can be multiple rows with the same field1, field2, field3 combination.. so if there are n rows, i need to do n inserts of 'CURRENT'..
that is the reason i used a cursor.. to fetch the multiple records in a loop.. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
|
|
but You did not show it in the sample rows
You showed only
Quote: |
AAA BBB CCC 3 30 OBSOLET
AAA BBB CCC 3 30 INVALID |
and since I suggested also the fileld6 constraint ..... |
|
Back to top |
|
|
darakhshan
New User
Joined: 03 Mar 2008 Posts: 32 Location: India
|
|
|
|
what i now plan to do to get around with this is fetch the cursor resultset into a table, and then loop separately to insert the same into the database.. i think that will work.. but i am not very good at cobol tables. is the below fine? pls help me.
DECLARATION:
01 TABLE-2.
02 INDVDL-TAB OCCURS 1 TO 20 TIMES
DEPENDING ON WS-COUNT
INDEXED BY WS-INDEX.
03 FIELD4 PIC S9(9) USAGE COMP.
03 FIELD5 PIC S9(11)V USAGE COMP-3.
I WILL COUNT THE NUMBER OF RECORDS THAT ARE PRESENT IN THE TABLE WITH FIELD1, FIELD2, FIELD3 WITH FIELD6 AS 'INVALID' AND MOVE IT INTO WS-COUNT.
FETCHING:
SET WS-INDEX TO 1
PERFORM FETCH-INDVDL-CURSOR
THRU FETCH-EXIT UNTIL WS-INDEX > WS-COUNT
OR SQLCODE-CHK = +100.
AND THEN WITHIN THE PARA:
*********************
FETCH-INDVDL-CURSOR.
*********************
SET WS-INDEX UP BY 1
EXEC SQL
FETCH INDVDL
INTO :INDVDL-TAB
END-EXEC.
MOVE SQLCODE TO SQLCODE-CHK.
IF SQLCODE = 0
CONTINUE
ELSE
MOVE SQLCODE OF SQLCA TO SQL-CODE
PERFORM 9999-ERROR-ROUTINE-PARA
THRU 9999-EXIT
MOVE WS-ERR-DISPLAY TO SQL-ERROR-DESC
END-IF.
ADD 1 TO WS-ROW
FETCH-EXIT.
EXIT.
WHILE INSERTING:
PERFORM INSERT-INTO-TABLE UNTIL WS-INDEX > WS-ROW
*****************
INSERT-INTO-TABLE.
*****************
MOVE FIELD1 (WS-INDEX) TO VAL1
MOVE FIELD2 (WS-INDEX) TO VAL2
EXEC SQL
INSERT INTO XYZ
VALUES
(
:WS-IN1,
:WS-IN2,
:WS-IN3,
:VAL1,
:VAL2,
'CURRENT'
)
END-EXEC.
IF SQLCODE = 0
CONTINUE
ELSE
MOVE SQLCODE OF SQLCA TO SQL-CODE
PERFORM 9999-ERROR-ROUTINE-PARA
THRU 9999-EXIT
MOVE WS-ERR-DISPLAY TO SQL-ERROR-DESC
END-IF. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
|
|
please please, watch Your typing,
You make very difficult for people trying to help to follo w Your line of thought
You started by speaking about &duplicating the rows with the "obsolete" string,
now in the last post You speak about the rows with invalid.... |
|
Back to top |
|
|
|