Joined: 22 May 2009 Posts: 160 Location: Bangalore
Hi,
I'm connecting to DB2 using the REXX.
I have declared the cursor to fetch the values from the table.
The query should fetch for more than 1 time. But I could see that the ouput is able to get only 1 row.
Code:
/*---------CURSOR DECL,OPEN,FETCH AND CLOSE------------------*/
ADDRESS DSNREXX 'EXECSQL PREPARE S1 FROM :SQLSTMT'
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"
ADDRESS DSNREXX "EXECSQL OPEN C1"
ADDRESS DSNREXX "EXECSQL FETCH C1 INTO :P1,:P2,:P3,:P4,:P5 "
Please can you let me know if there is possibility the same query to Run until the SQLCODE is =100. Once it reaches +100 then it should close the cursor.
Till that time it should go on put the values in P1,P2,P3,P4,P5
Joined: 22 May 2009 Posts: 160 Location: Bangalore
I got the solution
defined multiple FETCH statements
Code:
ADDRESS DSNREXX "EXECSQL FETCH C1 INTO :P1,:P2,:P3,:P4,:P5"
ADDRESS DSNREXX "EXECSQL FETCH C1 INTO :P11,:P12,:P13,:P14,:P15"
ADDRESS DSNREXX "EXECSQL FETCH C1 INTO :P21,:P22,:P23,:P24,:P25"
ADDRESS DSNREXX "EXECSQL FETCH C1 INTO :P31,:P32,:P33,:P34,:P35"
ADDRESS DSNREXX "EXECSQL FETCH C1 INTO :P41,:P42,:P43,:P44,:P45"
ADDRESS DSNREXX "EXECSQL FETCH C1 INTO :P51,:P52,:P53,:P54,:P55"
ADDRESS DSNREXX "EXECSQL FETCH C1 INTO :P61,:P62,:P63,:P64,:P65"
ADDRESS DSNREXX "EXECSQL FETCH C1 INTO :P71,:P72,:P73,:P74,:P75"
ADDRESS DSNREXX "EXECSQL FETCH C1 INTO :P81,:P82,:P83,:P84,:P85"
Now when i try to display using the command ZEDLMSG all the records are presented in 7 lines but my output is to be presented in 12 lines.
Is there a way i expand the ZEDLMSG length and width.
As I know that there are only 9 rows would be fetched i declared the fetch statements 9 times. so wanna know where can I give the recursion statement to perform the query and fetch me the exact number of rows until it finds a SQLCODE +100.
Second Issue:
The Message displayed in the ZEDLMSG is showing me only in the 7 line width but I need the exact n rows to be displayed on the screen.
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
Pandora-Box - "no messages or topics meet criteria" when I click on your link.
Tecnokrat - why are you wanting recursion? What you want is a loop.
Code:
Address DSNREXX
Do i = 1 to 9
EXECSQL FETCH...
assign data to variables so that you can re-use P!...
End
ZEDLMSG is for informational messages e.g. long error messages. It is not for presenting output. You can format some SAY statements to produce the output.
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
I made it 9 because of an earlier statement saying that 9 was the number of rows to fetch. OP had coded 9 fetches and stated that this was the requirement.
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
My own preferred way of your code, suershpathi10, is this:
Code:
"EXECSQL FETCH C1 INTO :P1,:P2,:P3,:P4,:P5"
Do While sqlcode = 0
move P1-P5 to array_variable
"EXECSQL FETCH C1 INTO :P1,:P2,:P3,:P4,:P5"
End
It removes the need for an IF condition to be tested for every row returned and avoids a negative condition controlling the loop. Again, just my preference.