External stored procedure calling SRVPGM procedure with Input and Output parameter

External stored procedure calling SRVPGM procedure with Input and Output parameter

In my previous article where we are calling the RPGLE Program directly but in this article we are not calling the program we are going to call the the Service program (SRVPGM) procedure.

Let's see the example, we have a Service program (SRVPGM) named EXTSRVPGM4

      H NOMAIN                                                                       
      Dsubproc1         pr                                                           
      D                               10i 0                                          
      D                               10a                                            
      D                               10i 0                                          
      Psubproc1         B                     EXPORT                                 
      D subproc1        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;                                                        
         return;                                                                     
       /End-Free                                                                     
      Psubproc1         E                                                            

Explanation of the above code

      H NOMAIN   

Here, a service program source where on H SPEC it is NOMAIN define means no main procedure exist in this source member.

      H NOMAIN                                                                       
      Dsubproc1         pr                                                           
      D                               10i 0                                          
      D                               10a                                            
      D                               10i 0                                          

subproc1 is the proceudre name and above is the procedure prototype defined where we have first two IN and last OUT parmeters. First two are input parms 10i 0 10a and last one is ouput parameter 10i 0.

      Psubproc1         B                     EXPORT  

Begin the procedure subproc1 and is exported.

      D subproc1        pi                                                           
      D n1                            10i 0                                          
      D n2                            10a                                            
      D l_sqlcode                     10i 0   

Procedure Interface defined, where we take 2 input parmeter n1 and n2 and l_sqlcode is the output parameter

       /Free                                                                         
        EXEC SQL                                                                     
        INSERT INTO EASYCLASS1.EXTPF1                                                
         (FLD1,FLD2)                                                                 
         VALUES(:n1,:n2);                                   

SQL Insert to file EXTPF1 in library EASYCLASS1 in selected fields FLD1 and FLD2 with values assigned from host variables n1 and n2

         l_sqlcode = sqlcode;                                                        
         return;                                                                     
       /End-Free   

assigned sqlcode value holds the status of SQL Insert statement execution is assigned to output parameter l_sqlcode and then return from the procedure.

      Psubproc1         E                                                            

End the procedure subproc1

Example of an External Stored Procedure Calling a SRVPGM procedure SUBPROC1 with Input and Output parameter

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

Explanation of the above code

Here, we have written SQLRPGLE Program to create the stored procedure which Calls an SRVPGM procedure with Input and Output parameters.

      D sqlproc         S            500a   inz(*blanks) 

Here, we have a defined a variable sqlproc of 500 characters initialized with blanks

       /Free                                                                         
        sqlproc = 'CREATE PROCEDURE EASYCLASS1.EXTERNALSTOREDPROC4( ' +              
                  'IN PARM1 INTEGER, IN PARM2 CHAR(10), ' +                          
                  'OUT PARM3 INTEGER) ' +                                            
                  'LANGUAGE RPGLE ' +                                                

We have build SQL statement in variable sqlproc to create an external procedure named EXTERNALSTOREDPROC4 which is going to create in library EASYCLASS1 with input parameters 'IN PARM1 INTEGER, IN PARM2 CHAR(10))' and output parameter has OUT PARM3 INTEGER with language RPGLE.

                  'SPECIFIC EASYCLASS1.EXTPROCED4 ' +                              

Here, the specific name is EXTPROCED4 every stored procedure has a specific name that is different from all others, and the stored procedure must be unique in its existence.

                  'EXTERNAL NAME EASYCLASS1.EXTSRVPGM4(SUBPROC1) ' +               

Here, We are creating an external procedure that calls the service program EXTSRVPGM4 procedure named SUBPROC1 which is present in the library EASYCLASS1.

                  'MODIFIES SQL DATA ' +                        

Here, we use clause MODIFIES SQL DATA since calling srvpgm procedure has SQL INSERT clause.

                  '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 and finally last record indicator set to ON.

Calling the external stored procedure in the RPGLE program

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

      D parm1           s             10i 0 inz(4)                                    
      D parm2           s             10a   inz('TEST4')                              
      D parm3           s             10i 0 inz(-1)                                   
       /Free                                                                          
        EXEC SQL                                                                      
        CALL EASYCLASS1.EXTERNALSTOREDPROC4(: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.EXTERNALSTOREDPROC4(:parm1,:parm2,:parm3) where we have two input params parm1 and parm2 where one of integer 10i 0 inz(3) and one of character 10a inz('TEST4') and both are initialized with (4) and (TEST4) 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 EXTERNALSTOREDPROC4. whether SQL Insert executed with success or with errors.