Recycling RPG as Stored Procedures

This article outlines the basics of turning an RPG program into a stored procedure

In today’s IT shops, application integration across multiple platforms is commonplace. Even the if applications that run the bulk of your business are written in RPG, there are probably requirements from non-RPG and non-IBM i applications to get access to some of your business functions. One of the most efficient ways to provide that access is by providing a stored procedure that can be called from any SQL platform. Fortunately, it’s not only efficient at run time; it’s also very easy to do. In this article, we’ll take a look at the basics of turning an RPG program into a 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. There are two kinds of stored procedures on IBM i: External and 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. We’ll concentrate on external stored procedures here since it provides a good way to recycle some of your existing logic or leverage your RPG skills by writing some logic for these other types of applications without needing to learn a new language or new programming techniques. If you can write an RPG (or COBOL or CL) program that communicates via passed parameters, you can write a stored procedure.

Even though stored procedures are called using SQL, there’s no requirement that the RPG program called uses SQL (unless you decide to use more advanced stored procedure features such as returning result sets). You may use embedded SQL, of course, but you may also use “native” RPG CHAIN, READ or UPDATE operations.

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:

 CREATE PROCEDURE GetCustInfo
 (IN CustNo DEC (5,0), OUT Name CHAR (15), OUT City CHAR(25),
 OUT St CHAR(2), OUT Act DEC(1,0))
 EXTERNAL NAME MYLIB/CUSTINFO
 LANGUAGE RPGLE
 PARAMETER STYLE GENERAL

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.

The parameter data types are also specified and they must be specified using SQL data types. DEC (or Decimal) is SQL-ese for packed decimal. Hopefully CHAR (or Character) needs no explanation. If you were passing zoned numeric data, you’d specify NUMERIC as the data type.

How would you enter the Create Procedure statement? You may use any SQL interface that works for other SQL functions on IBM i. Interactive SQL (by using the STRSQL command) can be used, as can the Run SQL Scripts interface from Navigator. You may also enter the CREATE PROCEDURE command into a source member and run it using the RUNSQLSTM (Run SQL Statement) command. There is also a wizard in Navigator to help you create stored procedures.

Testing Your Stored Procedure

Now that you have your stored procedure created, how can you call it to test it? One of the simplest ways to test a stored procedure is to use the Run SQL Scripts dialog in Navigator. This interface allows you to call the procedure and pass parameters. You’ll see the parameter values that come back after the call (the OUT or INOUT values). It can also show you result set values for more advanced procedures that you may want to write.

You could call the stored procedure from Interactive SQL (STRSQL), but since you can’t see the returned results, this is probably not a great choice. You may also, of course, write an RPG program that calls your stored procedure for testing purposes—a sort of test harness for purposes of exercising the called code. If you want to write an RPG test harness for your stored procedure, you’ll need to use embedded SQL for the call. The call statement for our sample procedure might look something like this:

 Exec SQL Call GetCustInfo( :CustNo, :CusName, :CCity, :CState, :Active );

Why Use Stored Procedures?

This example is so simple that you may find yourself wondering why the application on the other platform would bother calling a stored procedure for something that could likely be done easily with a simple SELECT statement. It can often be more efficient to use a stored procedure, particularly if the request requires access to multiple tables (aka files), potentially with program logic determining exactly which rows (aka records) need to be accessed. Of course, RPG (perhaps in combination with CL) also offers unique capabilities that an SQL statement can’t easily handle. You might, for example, want to use RPG to provide numeric editing capabilities (e.g., via %EditC or %EditW) that are unavailable in SQL. Or the RPG program may be doing significant processing in the background before returning the information.

Creating a simple stored procedure to call an RPG program from other application environments is an easy and effective way to reuse your RPG code and leverage your RPG skills for use in new application environments. Give it a try with a simple parameter-passing program like this one. In later articles, we’ll look at some other more advanced techniques you may want to employ with stored procedures, such as returning information for a list of customers.

---