Create Stored Procedure for RPGLE programs

SQL Stored Procedures can be used to expose your back-end business logic to the outside world without rewriting the code again and/or providing the access to your database to Third party Vendors. Here I am going to start with sample RPGLE program and then write a wrapper program to make it a Stored Procedure. You can use the same logic with CL program also.

RPGLE sample program

Member      Type        Text     
MYNAME      RPGLE       My name  

The program takes 4 parameters:

• Input First Name 20 char
• Input Last Name 20 char
• Input Age 3,0 Zoned Numeric
• Output is a Message putting together all the input values 100 char

d $first_name     s             20                                           
d $last_name      s             20                                           
d $my_age         s              3s 0                                        
d $message        s            100                                           
                                                                             
c     *entry        plist                                                    
c                   parm                    $first_name                      
c                   parm                    $last_name                       
c                   parm                    $my_age                          
c                   parm                    $message                         
                                                                             
 /free                                                                       
                                                                             
    $message = 'My name is ' + %trim($first_name) + ' ' +                    
                               %trim($last_name) + ' age ' +                 
                               %trim(%editc($my_age:'Z')) + ' years !';      
                                                                             
    *inlr = *on;                                                             
                                                                             
 /end-free   

Before we can create the Stored Procedure we need to warp this program using a SQLRPGLE program so that we can take the inputs and send the information in a ResultSet

Member      Type        Text       
MYNAMEQ     SQLRPGLE    My name Stored Procedure Wrapper

d $first_name     s             20                      
d $last_name      s             20                      
d $my_age         s              3s 0                   
d $message        s            100                      
                                                        
d MyName          PR                  ExtPgm('MYNAME')  
d                               20                      
d                               20                      
d                                3s 0                   
d                              100                      
                                                        
d @results        ds                  occurs(1)         
d  @message                    100                      
                                                        
d @var            ds                                    
d @n                             9b 0                   
                                                        
c     *entry        plist                               
c                   parm                    $first_name 
c                   parm                    $last_name  
c                   parm                    $my_age     
                                                        
 /free                                                  
                                                    
    @n = 0;                                         
                                                    
    MyName($first_name:$last_name:$my_age:$message);
    @n = @n + 1;                                    
    %occur(@results) = @n;                          
    @message = $message;                            
                                                    
  EXEC SQL                                          
       SET RESULT SETS ARRAY :@RESULTS FOR :@N ROWS;
                                                    
    *inlr = *on;                                    
                                                    
 /end-free        

What is the difference between this wrapper program and the original RPGLE?

• It's an SQLRPGLE source not a RPGLE source
• It has only the Input parameters
• The output is pushed into the ResutlSet

Note: Lets say you are reading order details for an Order number then you will increment the variable @n and keep adding the details to the data structure. Here is an example:

d @results        ds                  occurs(100)           
d  @line                         4s 0                     
d  @item                        20                        
d  @desc                        50                        
d  @qty                          8s 0                     

   //Dountil End of file                             
      //Read file based on key information           
      @n = @n + 1;                                   
        %occur(@results) = @n;                       
           @line = $file_line;                       
           @item = $file_item;                       
           @desc = $file_desc;                       
           @qty  = $file_qty ;                       
   //Enddo                                           
                                                     
  EXEC SQL                                           
       SET RESULT SETS ARRAY :@RESULTS FOR :@N ROWS; 

Now lets create the Stored Procedure

Your can type STRSQL from command line or use I-Series Operations Navigator

CREATE PROCEDURE {library}/MYNAMEQ(IN FIRST_NAME CHAR ( 20), IN LAST_NAME 
CHAR ( 20), IN AGE NUMERIC (3, 0)) RESULT SETS 1 LANGUAGE RPGLE NOT 
DETERMINISTIC CONTAINS SQL CALLED ON NULL INPUT EXTERNAL NAME       
{library}/MYNAMEQ PARAMETER STYLE GENERAL             

 To Drop a procedure use command Drop {procedure_name}

Few handy Tips when creating Stored Procedures in iSeries

• Map iSeries(AS400) Zoned data to SQL Numeric field to avoid Decimal data errors
• Map iSeries(AS400) Packed data to SQL Decimal field to avoid Decimal data errors
• If your RPGLE program is also SQLRPGLE and using embedded SQL to manipulate data then you must choose the option 3 or 4 depending on the program. Take F4 on the Create Procedure statement

SQL usage  . . . . . .   1                      1=CONTAINS SQL    
                                                2=NO SQL          
                                                3=READS SQL DATA  
                                                4=MODIFIES SQL DATA