SQLRPGLE Select Statement for db2 in iSeries (AS400) - Fetch using Cursor

In order to create an RPGLE source member that can use embedded SQL, you must use a source type of SQLRPGLE. To compile an SQLRPGLE source member, use the "Create SQL ILE RPG Object" (CRTSQLRPGI) command. If you are using PDM, option 14 will create a bound program, and option 15 will create a *MODULE object. If you are issuing the command manually, use the OBJTYPE parameter to indicate *PGM or *MODULE.

Click here for SQL Select statement Syntax cheat sheet and examples

 

Sample SQLRPGLE program to Read data from db2 using Select and Fetch SQL Statements

d DataDS          ds                                   
d $cmp                           2s 0                  
d $empId                         5s 0                  
d $empName                      30a                    
d $empDate                       8s 0                  
d $empSalary                    11s 2                  
                                                       
 /free                                                 
                                                       
  //Set SQL options                                    
  exec sql                                             
   SET OPTION                                          
       commit=*none,                                   
       datfmt=*iso;                                    
                                                       
  //Read one Record from the SQL table using unique keys
  exec sql                                             
    Select d1cmp,d1id,d1name,d1sdat,d1saly into :DataDS 
    from qgpl/mydata where d1cmp = 1 and d1id = 102;    
                                                       
    if sqlcod = 0;                                     
    dsply $cmp;                                        
    dsply $empId;                                       
    dsply $empName;                             
    dsply $empDate;                             
    dsply $empSalary;                           
    else;                                       
    dsply sqlcod; //do something                
    endif;                                      
                                                
  //Read more than one record from the SQL table
                                                
  //Declare the cursor                          
  exec sql                                      
    Declare C1 cursor for                       
      Select d1cmp,d1id,d1name,d1sdat,d1saly    
      from qgpl/mydata where d1cmp = 1;          
                                                
  //Open the cursor                             
  exec sql                                      
    Open C1;                                    
                                                
  //Fetch data in a loop                        
  Dou sqlcod <> 0;                              
  exec sql                                      
    Fetch C1 INTO :DataDS;                      
                          
    If (sqlcod <> 0);     
      Leave;              
    Endif;                
                          
    dsply $cmp;           
    dsply $empId;         
    dsply $empName;       
    dsply $empDate;       
    dsply $empSalary;     
                          
  Enddo;                  
                          
  //Close the cursor      
  exec sql                
    Close C1;             
                          
  *inlr = *on;            
                          
 /end-free       

Host Variables

Host variables are always preceded in SQL by a colon. Since your RPGLE program is the "host" of these SQL statements, the term host variable refers to any variable declared in the RPGLE program. We can use these variables when we execute SQL as parameters or as return values.