External stored procedure calling RPGLE program with no parameter

External stored procedure calling RPGLE program with no parameter

An external stored procedure is a procedure that is written in a host language and can consist of SQL statements. The source code for the external procedure is separate from the create script(definition).

RPGLE program EXTPGM1 for External stored procedure

      D n1              S             10i 0 inz(1)                                   
      D n2              S             10a   inz('A')                                 
       /Free                                                                         
        EXEC SQL                                                                     
        INSERT INTO EASYCLASS1.EXTPF1                                                
         (FLD1,FLD2)                                                                 
         VALUES(:n1,:n2);                                                            
         *INLR = *ON;                                                                
       /End-Free                                                                     

Here, we have taken two variables n1 and n2 respectively of type integer and character and initialized with value 1 and A respectively. Then inserting the record into the file named EXTPF1 in column FLD1 and FLD2 using the host variable n1 and n2 of type integer and character. We use colon(:) to indicate a host variable in embedded SQL statements.

External Stored Procedure script in SQLRPLE program

Below program script for create procedure will create the external stored procedure using SQL Execute Immediate.

      D sqlproc         S            500a   inz(*blanks)                             
       /Free                                                                         
        sqlproc = 'CREATE PROCEDURE EASYCLASS1.EXTERNALSTOREDPROC1() ' +             
                  'LANGUAGE RPGLE ' +                                                
                  'SPECIFIC EASYCLASS1.EXTPROCED1 ' +                                
                  'EXTERNAL NAME EASYCLASS1.EXTPGM1 ' +                              
                  '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 stored procedure.

        sqlproc = 'CREATE PROCEDURE EASYCLASS1.EXTERNALSTOREDPROC1() ' +             
                  'LANGUAGE RPGLE ' +                                                

In this script, EXTERNALSTOREDPROC1() is the name of the external procedure which is present in library EASYCLASS1 with language RPGLE where, default language is SQL.

                  'SPECIFIC EASYCLASS1.EXTPROCED1 ' +                              

Here, it will provide system name to this procedure named EXTPROCED1. The system name must be unique for an stored procedure. We can have multiple procedures with same name but there specific name must be unique within a library. The same specific name procedure can be reside in the different library but not in the same library.

                  'EXTERNAL NAME EASYCLASS1.EXTPGM1 ' +                            

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

                  'MODIFIES SQL DATA ' +                        

Here, we using clause MODIFIES SQL DATA which allowed the stored procedure external program to modify the SQL data using SQL CRUD operation whichever applies.

                  'PARAMETER STYLE GENERAL';                    

Here, we using general parameter style means the parameters are passed to the external program as they are specified in the CREATE procedure statement.

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

Here, It can be used to prepare and execute an SQL statement that contains neither host variables nor parameter markers.

How to call External stored procedure in RPGLE

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

       /Free                                                                         
        EXEC  SQL                                                                     
        CALL EASYCLASS1.EXTERNALSTOREDPROC1();                                       
         *INLR = *ON;                                                                
       /End-Free                                                                     

Here, EXEC SQL CALL EASYCLASS1.EXTERNALSTOREDPROC1() command is used to execute a stored procedure which is present in library EASYCLASS1 and we always need a name which is used just after create procedure i.e. long name of the procedure, do not use specific name to call a procedure, we can drop a stored procedure using specific name. INLR=*ON is used to indicate that last record has been reached or you can say that is used to terminate the program.