PMS Information Systems
Welcome to PMS Information Systems - IBM i (AS/400) Forum !!!

Get Answers for all your queries on IBM i (AS/400).

How to convert RPG program to Stored procedure (SP)?

View previous topic View next topic Go down

How to convert RPG program to Stored procedure (SP)?

Post  maran on Sat Dec 26, 2015 6:03 pm

How to convert RPG program to Stored procedure (SP)

What is Stored procedure?
A stored procedure is simply a program that can be called from SQL. Since SQL is a database language, that means that for us, the call comes into the system via DB2.

What are the types of Stored Procedure?
There are two kinds of stored procedures on IBM i.
   1) External
   2) SQL
SQL stored procedures are written completely in an SQL procedure language while external stored procedures are written in a high-level language such as RPG.

What is the need to convert native code(RPG,COBOL..) to SP?
There are situation we need to convert RPG program into Stored procedure (SP) so that other than non RPG and non IBM i applications can access our business functions which run on IBM i.

Example:
Let’s assume we have a program that accepts a Employee Code as input and sends back via parameters several pieces of information about that employee. The parameter list for the program might look like the following.

RPG III Style

    C                 *ENTRY PLIST
C PARM EMPCDE
C PARM EMPNAM
C PARM EMPDEP
C PARM EMPSAL
          C*    Other logic goes here


OR

RPG IV style
   D  GETEMPDTL            PI
   D  EMPCDE                          5S 0
   D  EMPNAM                        30A
   D  EMPDEP                           2A
   D  EMPSAL                          11P  2

         // other logic goes here

Create Stored Procedure


The SQL statement  CREATE   PROCEDURE .....      is used to register EMPINQR program with the database i.e DB2 UDB.
CREATE PROCEDURE GetEmpInfo
(IN EMPCDE DEC (5,0), OUT EMPNAM  CHAR (30), OUT  EMPDEP CHAR(2),
OUT EMPSAL DEC(11,2))
EXTERNAL NAME PMSLIB/EMPMST
LANGUAGE RPGLE
PARAMETER STYLE GENERAL

We can register procedure which exits in service program as well.
In this case EXTERNAL NAME shouls be specified as follows:

EXTERNAL NAME MYLIB/SRVPGMNAM(PROCNAME)


How to call stored procedure?

In SQLRPGLE program

Exec SQL Call GetEmpInfo( :EMPCDE, :EMPNAM, :EMPDEP, :EMPSAL );
avatar
maran
Admin

Posts : 442
Join date : 2009-07-24

View user profile http://pmsinformationsystem.forumotion.net

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum