Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

HOST VARIABLE ARRAY "ENAME" IS EITHER NOT DEFINED

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vasif

New User


Joined: 11 Feb 2008
Posts: 35
Location: Chennai

PostPosted: Tue May 29, 2012 9:10 am    Post subject: HOST VARIABLE ARRAY "ENAME" IS EITHER NOT DEFINED
Reply with quote

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
View user's profile Send private message

vasif

New User


Joined: 11 Feb 2008
Posts: 35
Location: Chennai

PostPosted: Tue May 29, 2012 9:12 am    Post subject:
Reply with quote

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
View user's profile Send private message
Dsingh29

Active User


Joined: 16 Dec 2008
Posts: 132
Location: IBM

PostPosted: Tue May 29, 2012 10:38 am    Post subject: Reply to: HOST VARIABLE ARRAY "ENAME" IS EITHER NO
Reply with quote

You seem to be doing most of the things right, did you try

FETCH FIRST ROWSET FOR 5 ROWS
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Tue May 29, 2012 11:48 am    Post subject:
Reply with quote

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
View user's profile Send private message
vasif

New User


Joined: 11 Feb 2008
Posts: 35
Location: Chennai

PostPosted: Tue May 29, 2012 3:23 pm    Post subject: Reply to: HOST VARIABLE ARRAY "ENAME" IS EITHER NO
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue May 29, 2012 3:26 pm    Post subject:
Reply with quote

is the column datatype actually varchar?
Back to top
View user's profile Send private message
Dsingh29

Active User


Joined: 16 Dec 2008
Posts: 132
Location: IBM

PostPosted: Tue May 29, 2012 4:28 pm    Post subject:
Reply with quote

AFAIK, you will automatically get the length of the ename in ename-len variable
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue May 29, 2012 4:36 pm    Post subject:
Reply with quote

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
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Tue May 29, 2012 4:47 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 186
Location: chennai

PostPosted: Tue May 29, 2012 10:42 pm    Post subject: Reply to: HOST VARIABLE ARRAY "ENAME" IS EITHER NO
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Amount field is getting corrupted whe... thesumitk SYNCSORT 5 Tue Oct 18, 2016 8:20 pm
No new posts hot to get details when "EXEC CI... Andi1982 CICS 11 Tue Sep 20, 2016 5:01 pm
No new posts Format to pipe delimited with variabl... pshongal SYNCSORT 6 Wed Sep 14, 2016 2:48 pm
No new posts Getting list of tables defined under ... kishpra DB2 2 Wed Aug 24, 2016 10:36 am
No new posts Multi row fetch - "for read only... Nileshkul DB2 3 Sun Aug 14, 2016 12:52 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us