External stored procedure calling RPGLE program with Input parameter

External stored procedure calling RPGLE program with Input parameter

Here, we will call an external program with Input parameter using External stored procedure. So, at first we will create an SQLRPGLE prgram with some input paramters to it and then will be creating external stored procedure calling the same SQLRPGLE program with input parameters and finally instead of SQLRPGLE program we can now call external stored procedure from SQL call.

Example of an RPGLE Program with Input Parameter

      D main            pr                  extpgm('EXTPGM2')                        
      D                               10i 0                                          
      D                               10a                                            
      D main            pi                                                           
      D n1                            10i 0                                          
      D n2                            10a                                            
       /Free                                                                         
        EXEC SQL                                                                     
        INSERT INTO EASYCLASS1.EXTPF1                                                
         (FLD1,FLD2)                                                                 
         VALUES(:n1,:n2);                                                            
         *INLR = *ON;                                                                
       /End-Free                                                                     

Here, a program EXTPGM2 with two input parameters n1 and n2 of type integer and character respectively will be created and in this program we will be inserting the record into the file named EXTPF1 in column FLD1 and FLD2 using the input parameters to this program as an host variable.

Example of an External Stored Procedure Calling a RPGLE Program with Input Parameter

Now, we have a program which is populating the file named EXTPF1 so, we need to create a stored procedure over this program basically which calls the program so that we would be able to populate the file using stored procedure.

      D sqlproc         S            500a   inz(*blanks)                             
       /Free                                                                         
        sqlproc = 'CREATE PROCEDURE EASYCLASS1.EXTERNALSTOREDPROC2( ' +              
                  'IN PARM1 INTEGER, IN PARM2 CHAR(10)) ' +                          
                  'LANGUAGE RPGLE ' +                                                
                  'SPECIFIC EASYCLASS1.EXTPROCED2 ' +                                
                  'EXTERNAL NAME EASYCLASS1.EXTPGM2 ' +                              
                  'MODIFIES SQL DATA ' +                                             
                  'PARAMETER STYLE GENERAL';                                         
                                                                                     
        EXEC SQL                                                                     
        EXECUTE IMMEDIATE :sqlproc;                                                  
                                                                                     
         *INLR = *ON;                                                                
       /End-Free                                                                     

Explanation of the above code

Here, we have written RPGLE Program which is creating an External procedure with Input Parameter.

>      D sqlproc         S            500a   inz(*blanks)                             
       /Free                                                                         
        sqlproc = 'CREATE PROCEDURE EASYCLASS1.EXTERNALSTOREDPROC2( ' +              
                  'IN PARM1 INTEGER, IN PARM2 CHAR(10)) ' +                          
                  'LANGUAGE RPGLE ' +                                                

Here, we have defined a statement sqlproc of 500 characters initialized with blanks. In this script, EXTERNALSTOREDPROC2 is the name of the external procedure which is going to be created in library EASYCLASS1 with input parameters 'IN PARM1 INTEGER, IN PARM2 CHAR(10))' with language RPGLE where, default language is SQL. Please note that the type and length of input paramaters in create procedure definition must match with the input parameters of the external RPGLE program.

                  'SPECIFIC EASYCLASS1.EXTPROCED2 ' +                              

Here, it will provide system name to this procedure named EXTPROCED2. This name is unique for each stored procedure cretaed within a library or a schema.

                  'EXTERNAL NAME EASYCLASS1.EXTPGM2 ' +                            

Here, We creatimg an external procedure which call the program named EXTPGM2 which is present in library EASYCLASS1.

                  'MODIFIES SQL DATA ' +                        

Here, we using clause MODIFIES SQL DATA which modifying the SQL data.

                  'PARAMETER STYLE GENERAL';                    

Here, we are using general parameter style means parameters are passed to the external program from external stored procedure as they are defined in procedure.

        EXEC SQL                                                                     
        EXECUTE IMMEDIATE :sqlproc;                                                  
                                                                                     
         *INLR = *ON;                                                                
       /End-Free                                                                     

Here, It can be used to prepare and execute an SQL statement and EXECUTE IMMEDIATE statement builds and runs a dynamic SQL statement in a single operation and here it will create the external stored procedure by executing the SQL script for creating procedure.

Calling the external stored procedure from RPGLE program with Input parameters

Example of a SQLRPGLE Program that Calls a SQL External Stored Procedure:

      D parm1           s             10i 0 inz(2)                                   
      D parm2           s             10a   inz('TEST2')                             
       /Free                                                                         
        EXEC SQL                                                                     
        CALL EASYCLASS1.EXTERNALSTOREDPROC2(:parm1,:parm2);                          
         *INLR = *ON;                                                                
       /End-Free                                                                     

Explanation of the above code

Here, we have two variables parm1 and parm2 which one of integers 10i 0 inz(2) and one of character 10a inz('TEST2') and both are initialized with (2) and (TEST2).

Now, we need to call an external stored procedure so we can call using SQL CALL then we have to write EXEC SQL and write CALL EASYCLASS1.EXTERNALSTOREDPROC2(:parm1,:parm2). Here, we have parameter parm1 and parm2 which are assigned as host variables.