Recycling RPG as Stored Procedures

Recycling RPG as Stored Procedures

Creating a Stored Procedure

Let’s concentrate on developing simple stored procedures using traditional style parameters to communicate between caller and callee. In a later article, we’ll investigate some more advanced options, such as returning result sets.

As an example, let’s assume you have a program that accepts a customer number as input and sends back via parameters several pieces of information about that customer. The parameter list for the program might look like the following. We’ve used a Procedure Interface, but a *Entry PLIST would also work.

    D CustInfo         PI
    D  CustNo                    5P 0
    D  Name                     15A
    D  City                     25A
    D  State                     2A
    D  Active                    1P 0

Because the program will be called using SQL, you need to notify the database about your program and how to call it—e.g., where it is and what parameters it uses. There’s no requirement to make any changes to the RPG program. We do this with the SQL statement Create Procedure. The following is an example of a statement that could be used to register our Customer Information program with the database:

 (IN CustNo DEC (5,0), OUT Name CHAR (15), OUT City CHAR(25),
 OUT St CHAR(2), OUT Act DEC(1,0))

Note that the procedure name (GetCustInfo) is the one that the calling applications will use. In this example, the actual program object name (as specified with the EXTERNAL NAME parameter) is CUSTINFO in library MYLIB. Service Program procedures may also be registered as stored procedures. In that case, the external name syntax would contain the procedure name in parentheses after the Service Program name, such as MYLIB/CUSTSRVPGM(CUSTINFO).

We have also specified the language the program is written in (RPGLE), because some languages have different standards for parameter passing and the database is responsible for passing the parameters appropriately. The PARAMETER STYLE—GENERAL, in this case – specifies that we are using the simplest form of parameter passing, which does not include any null support nor any special SQL error feedback that some other parameter styles will allow.

Immediately following the procedure name in parentheses is the list of parameters the program uses. In this case, we’re specifying the customer number is an input parameter and the remaining parameters are output. Another option is INOUT, which means the fields are used as both input and output. Of course, as far as RPG programs are concerned, all parameters are, technically, INOUT parameters. However, it’s a good idea to specify how the parameters are used logically so that callers understand how to interface to your program. You may notice that this example illustrates the fact that the parameter names are merely documentary here—they don’t need to match the names of the program’s parameter fields.

Jon Paris is a technical editor with IBM Systems Magazine and co-owner of Partner400.

Susan Gantner is a technical editor with IBM Systems Magazine and co-owner of Partner400.

comments powered by Disqus



2017 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

New and Improved XML-INTO

Namespace support makes the opcode a viable option

Authenticating on the Web

The finer points of OpenRPGUI, Part 1

The Microphone is Open

Add your voice: Should IBM i include open-source RPG tools?

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters