Execute the Select and do FETCH in a loop?

Chris Ringer

 How to load an array using SQL. Note that you need both a constant and a variable for the elements/rows value (constant for DIM, and variable for SQL statements). I've set the array element count high, but that is example only you can reduce this by just changing the constant once. You can change the sql declare statement to be whatever you need it to be.

 *  Array limits                                  
d MaxElem         c                   9999        
d MaxRows         s             10i 0 inz(MaxElem)

d ldMiscChg       s               n                                  
d cntMiscChg      s             10i 0                                
d arrMiscChg      ds                  likeds(tmpMiscChg) dim(MaxElem)

 *************************************************************
 *  LoadMiscChgArray (local)                                  
 *************************************************************
p LoadMiscChgArray...                                         
p                 b                                           
d                 pi                                          
 /free                                                        
    exec sql declare  MiscChg cursor for                       
             select   [list fields]
             from     MiscChgF                                
             order by (list key sequence);
                                                               
    exec sql open MiscChg;                                     
                                                              
    exec sql fetch from MiscChg                                
             for   :MaxRows rows                              
             into  :arrMiscChg;                               
                                                               
    cntMiscChg = sqler3;                                      
                          
    exec sql close MiscChg;
                           
    return;               
 /end-free                
p LoadMiscChgArray...     
p                 e       

Well, yes, if you are doing embedded SQL, it's easy. You don't necessarily need a loop, though. You can SELECT directly into an array. So it depends on your array dimension vs. how many rows you expect. See SQL Embedded Programming, chapter on ILE RPG, there's a specific section on using host structure arrays.

If you're not using embedded SQL, after your read, do a SetGT using that value before you do the next read. You need an index with suitable high order keys, but otherwise this is pretty straightforward.

Aha! Kudos to Neil. This is  basically what you'll find in SQL Embedded Programming, but with more detail.

I guess I was hoping to just be able to do this in one simple step, but it doesn't seem to work:

d locArray        s              6s 0 Dim(200) Inz

/free

Exec Sql Select distinct location into :locArray from myfile;

I also tried defining the array as a subfield of a DS like this and inserting into it, but I get the error that the SQL returned more than one row.

d locDS           ds          1200    Inz     
d  locArray       s              6s 0 Dim(200)

I think I need to play with neilrh's example when I get a few minutes.  Thanks.

You can add For 200 Rows. To your select stmt.

I had to do something just like this again so I thought I'd share my simple test that works for loading values from a file into an array.  Thanks for the help.

      *==============================================================*                              
      *  Program Name: testsqlary                                    *                              
      *==============================================================*                              
                                                                                                    
      /Copy MYLIB/QCpySrc,Hspecs                                                                 
                                                                                                    
      /Copy MYLIB/QCpySrc,ProtoUtil                                                              
      /Copy MYLIB/QCpySrc,ConstSQL                                                               
                                                                                                    
     d maxElem         c                   200                                                      
     d maxRows         s             10i 0 Inz(maxElem)                                             
     d classCount      s              5i 0 Inz                                                      
     d i               s              5i 0 Inz                                                      
                                                                                                    
     d classArrDS      ds                  Qualified Dim(maxElem) Inz                               
     d  class                         6s 0                                                          
                                                                                                    
      /Free                                                                                         
                                                                                                    
       Exec sql
         declare MyCursor cursor for
           select distinct SQCLASS
             from fstoreqta;
                                                                                                    
       Exec sql open MyCursor;                                                                      
       Exec sql fetch from MyCursor for :MaxRows rows into :classArrDS;                             
       classCount = sqler3;                                                                         
       Exec sql close MyCursor;                                                                     
                                                                                                    
       joblog('Elements fetched = %s.':%char(classCount));                                          
                                                                                                    
       For i = 1 to classCount;                                                                     
         joblog('Element %s = %s.':%char(i):%char(classArrDS(i).class));                            
       EndFor;                                                                                      
                                                                                                    
       *inlr = *on ;                                                                                
                                                                                                    
      /End-Free