|
View previous topic :: View next topic
|
| Author |
Message |
vasif
New User
Joined: 11 Feb 2008
Posts: 35
Location: Chennai
|
|
|
|
I am trying to obtain emp names using the multirow fetch using the cursor below:
| Code: |
EXEC SQL
DECLARE C1 CURSOR
WITH ROWSET POSITIONING FOR
SELECT ENAME
FROM EMP
END-EXEC.
|
I have declared the host variable array this way:
| Code: |
05 ENAME.
49 ENAME-LEN PIC S9(4) USAGE COMP OCCURS 10 TIMES.
49 ENAME-TEXT PIC X(20) OCCURS 10 TIMES.
|
and also this way
| Code: |
05 ENAME OCCURS 10 TIMES.
49 ENAME-LEN PIC S9(4) USAGE COMP.
49 ENAME-TEXT PIC X(20).
|
The fetch statement is this:
| Code: |
EXEC SQL
FETCH NEXT ROWSET FROM C1
FOR 5 ROWS INTO :ENAME
END-EXEC.
|
But either way i get the below error:
HOST VARIABLE ARRAY "ENAME" IS EITHER NOT DEFINED OR IS NOT USABLE
I am not able to figure out the error. |
|
| Back to top |
|
 |
vasif
New User
Joined: 11 Feb 2008
Posts: 35
Location: Chennai
|
|
|
|
And i also tried
| Code: |
EXEC SQL
FETCH NEXT ROWSET FROM C1
FOR 5 ROWS INTO :ENAME-TEXT
END-EXEC.
|
Still the same error i get in the above FETCH |
|
| Back to top |
|
|
Dsingh29
Active User
Joined: 16 Dec 2008
Posts: 132
Location: IBM
|
|
|
|
You seem to be doing most of the things right, did you try
FETCH FIRST ROWSET FOR 5 ROWS |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| For VARCHARs used in a multiple row-fetch, the "occurs" should be on the 05 level and not on 49 level. Change it and try again. |
|
| Back to top |
|
|
vasif
New User
Joined: 11 Feb 2008
Posts: 35
Location: Chennai
|
|
|
|
| Using level 10 instead of 49 works, but i have to use the ENAME-TEXT to get the values from the FETCH, how do i get the length of these values? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| is the column datatype actually varchar? |
|
| Back to top |
|
|
Dsingh29
Active User
Joined: 16 Dec 2008
Posts: 132
Location: IBM
|
|
|
|
| AFAIK, you will automatically get the length of the ename in ename-len variable |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Dsingh29 wrote: |
| AFAIK, you will automatically get the length of the ename in ename-len variable |
if the column is varchar. we don't know that yet. |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi,
Change your declaration as below and try
| Code: |
05 ENAME occurs 10 times.
49 ENAME-LEN PIC S9(4) USAGE COMP SYNC.
49 ENAME-TEXT PIC X(20) . |
Regards,
Chandan |
|
| Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
Hi,
See the below code which is working in DB2 8.1.
| Code: |
01 OUTPUT-VARS.
05 NUM PIC X(6) OCCURS 32 TIMES.
05 NAME OCCURS 32 TIMES.
49 NAME-LEN PIC S9(4) USAGE COMP.
49 NAME-TEXT PIC X(12) . |
Here NUM CHAR(6),
NAME VARCHAR(12).
Below shows the Cursor declaration statement.
| Code: |
EXEC SQL
DECLARE C1 SCROLL CURSOR
WITH ROWSET POSITIONING
FOR SELECT EMPNO, FIRSTNME FROM EMPLOYEE1
END-EXEC.
|
Here the Fetch statement
| Code: |
EXEC SQL
FETCH FIRST ROWSET FROM C1 FOR 32 ROWS
INTO :NUM,:NAME
END-EXEC |
Regards
Raghunath |
|
| Back to top |
|
|
|
|
