Recycling RPG as Stored Procedures

Recycling RPG as Stored Procedures

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. If you are unfamiliar with the syntax of embedded SQL, take a look at “Bringing the Power of SQL to Your RPG Program.” 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.

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



2019 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