USE OF CURSOR TO RETRIVE DATA

sandy_mcs · New User Joined: 01 Apr 2006 Posts: 17 Location: Pune

I WANT TO FETCH MORE THAN 1 ROWS FROM TABLE..IS USE OF CURSOR NECESSARY FOR THAT.HOW CAN I DO THAT..PLZ GIVE ANY EG.
NOTE :-THROUGH EMBEDDED SQL(COBOL+SQL)

THANKS IN ADVANCE..!
BYE...

vidhyanarayanan · New User Joined: 23 Dec 2005 Posts: 52

Hi sandy_mcs,

cursor is mainly to retrieve more than one row of a table....
it involves 4 steps.

* Declare statement
* open statement
* fetch statement
* close statement.

syntax for fetch:
fetch cursor name into
: ws var1
: ws var2

r2k1984 · New User Joined: 21 Jun 2005 Posts: 69 Location: chennai

u will not be able to get many rows at a time in embedded sql.but this can be overcome by using cursor command.
1).first for this u have to createa cursor
2). open the cursor.
3). fetch the values .
4). close the cursor.
these are about cursor.

Jerry · New User Joined: 16 Sep 2005 Posts: 42

Hi Sandy,

Yes, if you need to retrieve more than one row from a table, you would need to use cursor.

You could get the exact syntax from the DB2/UDB V7 Programming Guide manual in this forum.

Correct me, if I am wrong.

Regards,
Ramya.

sandy_mcs · New User Joined: 01 Apr 2006 Posts: 17 Location: Pune

I have written following cobol prog.

IDENTIFICATION DIVISION.
PROGRAM-ID. QUERY3.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
77 EID PIC 9(3).
77 ENAME PIC X(15).
77 EADDR PIC X(15).
EXEC SQL
INCLUDE SQLCA
END-EXEC.
EXEC SQL
INCLUDE EMPLOYEE
END-EXEC.
EXEC SQL
DECLARE CUR3 CURSOR FOR
SELECT ENAME,EADDR FROM LEM0U13.EMPLOYEE
END-EXEC.
PROCEDURE DIVISION.
EXEC SQL
OPEN CUR3
END-EXEC.
PERFORM FETCH-PARA UNTIL SQLCODE = 100.
PERFORM CLOSE-PARA.
STOP RUN.

and I am getting s322 error
I cant get the reason behind this..
I need help...

vidhyanarayanan · New User Joined: 23 Dec 2005 Posts: 52

Hi,
s322 is time out error.....give more time before submitting jcl.....and
try......

sri.mainframes · New User Joined: 16 Feb 2006 Posts: 29 Location: MUMBAI

Hi Sandy,

cursor is simillar to a file.when u issue a declare cursor statement , the cursor holds the table of data that matches the qualifying criteria .

host program can process one record at a time , it can not process more than one row at a time. first Fetch statement of cursor retrieves first record . again if u r issuing fetch statement it retrives the next record.

hope this will clear u

thanks
sri.prince

DavidatK · Posted: Tue Apr 18, 2006 4:49 am

Sandy,

Both vidhyanarayanan and sri.mainframes are correct in their posts.

An S322 is a timeout, which means you are in a loop somewhere, or you are processing a massive amount of data (I don't think this is the case with the EMPLOYEE table).

I see that you did not check the outcome of the open cursor. If this failed for some reason, the "PERFORM FETCH-PARA UNTIL SQLCODE = 100" would be in an endless loop, hence an S322 abend. I'm not saying this is the case, but it could be.

Also, please show us the code for "FETCH-PARA" as the loop may be in there also. If there is an error during the fetch and there is no error processing within the "FETCH-PARA" paragraph, this could be the problem.

What is it that you want to do with the rows when they are fetched? If "FETCH-PARA" only does the fetch I don't see where you are processing the rows.

Dave