External stored procedure calling RPGLE program with Input and Output parameter

External stored procedure calling RPGLE program with Input and Output parameter

In previous articles we've already mentioned about calling RPG program from the stored procedure as an external program with No parameter and Input parameter now, in this article we have both Input and Output parameters.

Let's see the example, we have a program named EXTPGM3

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

Here, a program EXTPGM3 in which we have taken n1 and n2 as an input and the l_sqlcode is the output parameter and then inserting the record into the file named EXTPF1 in column FLD1 and FLD2 with the input parameters n1 and n2 to the program. Once the SQL Insert got executed the sqlcode will be populated as per the Insert success/failure and the sqlcode will be evaluated in output parameter of this program i.e. l_sqlcode and last record indicator set as ON and return from the program.

Example of an External Stored Procedure Calling a RPGLE Program with Input and Output parameter

      D sqlproc         S            500a   inz(*blanks)                             
       /Free                                                                         
        sqlproc = 'CREATE PROCEDURE EASYCLASS1.EXTERNALSTOREDPROC3( ' +              
                  'IN PARM1 INTEGER, IN PARM2 CHAR(10), ' +                          
                  'OUT PARM3 INTEGER) ' +                                            
                  'LANGUAGE RPGLE ' +                                                
                  'SPECIFIC EASYCLASS1.EXTPROCED3 ' +                                
                  'EXTERNAL NAME EASYCLASS1.EXTPGM3 ' +                              
                  '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 and Output parameter.

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

Here, we have a statement sqlproc of 500 characters initialized with blanks and then we create external procedure named EXTERNALSTOREDPROC3 which is going to create in library EASYCLASS1 with input parameters 'IN PARM1 INTEGER, IN PARM2 CHAR(10))' and output parmeter OUT PARM3 INTEGER with language RPGLE.

                  'SPECIFIC EASYCLASS1.EXTPROCED3 ' +                              

Here, specific name is EXTPROCED3 every stored procedure have a specific name which is different from all others and stored procedure must be unique in its existence.

                  'EXTERNAL NAME EASYCLASS1.EXTPGM3 ' +                            

Here, We creatimg an external procedure which call the program named EXTPGM3 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 using general parameter style that means the parameters are pass as they are defined in stored procedure creation script.

        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.

Calling the external stored procedure in RPGLE program

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

      D parm1           s             10i 0 inz(3)                                   
      D parm2           s             10a   inz('TEST3')                             
      D parm3           s             10i 0 inz(-1)                                  
       /Free                                                                         
        EXEC SQL                                                                     
        CALL EASYCLASS1.EXTERNALSTOREDPROC3(:parm1,:parm2,:parm3);                   
         *INLR = *ON;                                                                
       /End-Free                                                                     

Explanation of the above code

Here, 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.EXTERNALSTOREDPROC3(:parm1,:parm2,:parm3) where we have two input parms parm1 and parm2 where one of integer 10i 0 inz(3) and one of character 10a inz('TEST3') and both are initialized with (3) and (TEST3) and third parm is output parameter parm3 whose type is integer 10i 0 inz(-1) initialized with (-1) so that we will be getting some feedback from the program which is EXTERNALSTOREDPROC3. whether SQL Insert executed with success or with errors.