RE: Selecting records using SQLRPGLE

Jeffrey,

I believe this should do it.

      // Format of returned SQL record.  Dimension = 128000/DS length           
                   
     d RtnDtaSet       ds                  Occurs(xxxx) Based(RtnDtaSet@)       
                   
     d  RtnFld                       12s 0                                      
                   
                                                                                
                   
      // SQL control values                                                     
                   
     d RtnDtaSet@      s               *                               Point to 
1st record
     d Mem@            s               *                               Point to 
SQL memory
     d NbrRows         s             10u 0                             Rows to 
be returned
     d RowCnt          s             10u 0                             Actual 
rows returned
     d sqlStmt         s          32000    Varying                     
Statement to run   
     d Success         c                   '00000'                     
Successful SQL oper
     d EmptySet        c                   '02000'                     No 
records returned

The RtnDtaSet DS is what you will read your result set into.  I haven't had 
success using DIM instead of OCCURS but others have.  The choice is up to you.

I chose to allocate the memory for the DS at run time and use the address to 
walk through the entries.  It could just as easily be accomplished with the 
OCCUR op-code.

The number of rows and memory allocation happen in *INZSR.

         
//------------------------------------------------------------------------------
         // *INZSR - Program initialization                                     
                   
         
//------------------------------------------------------------------------------
         BegSr *INZSR;                                                          
                   
                                                                                
                   
            // Set number of rows to retrieve to number of elements in 
RtnDtaSet structure      
            NbrRows= %Elem(RtnDtaSet);                                          
                
                                                                                
                   
            // Allocate memory for number of records returned from SQL fetch    
                   
            Monitor;                                                            
                   
               Mem@ = %Alloc(%Elem(RtnDtaSet) * %Size(RtnDtaSet));              
                   
               RtnDtaSet@ = Mem@;                                               
                   
                                                                                
                   
            // If allocate fails send escape message                            
                   
            On-Error  00425 :00426;                                             
                   
               spmMsgID = 'LN60110';                                            
                   
               spmMsgF = 'LNMSGF    *LIBL     ';                                
                   
               spmMsgDta = sdsProc + %EditC(sdsStatus :'3');                    
                   
               spmMsgTyp = '*ESCAPE';                                           
                   
               spmStkEnt = '*';                                                 
                   
               spmStkCtr = 3;                                                   
                   
               CallP SndPgmMsg(spmMsgID :spmMsgF :spmMsgDta@ :spmMsgTyp         
                   
                                   :spmStkEnt :spmStkCtr);                      
                   
            EndMon;                                                             
                   
                                                                                
                   
         EndSr;         

The FETCH statement looks like this:
c/Exec SQL                                        
c+ Fetch from C1 for :NbrRows rows into :RtnDtaSet
c/End-Exec                                        

HTH,

Rick
                                                                          
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Jeffrey Young
Sent: Tuesday, June 14, 2005 10:34 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: Selecting records using SQLRPGLE


Rick,
Can you elaborate on what needs to be defined to retreive multiple records on a 
Fetch operation?
The SQL Manual was rather confusing on this.

Thanks,


Privileged and Confidential.  This e-mail, and any attachments there to, is 
intended only for use by the addressee(s) named herein and may contain 
privileged or confidential information.  If you have received this e-mail in 
error, please notify me immediately by a return e-mail and delete this e-mail.  
You are hereby notified that any dissemination, distribution or copying of this 
e-mail and/or any attachments thereto, is strictly prohibited.