Some of the important tools that you may not be aware of are actually external SQL stored procedures. A slew of these helper objects can only be created in RPG; requiring far too many JOIN statements, logical operators and SELECT/OMIT statements for logical files otherwise. Years ago, we used RPG primarily to gather information that otherwise required some fancy footwork to retrieve, and what remains powerful about RPG is its ability to position in a specific location within our databases and read before or after to receive the necessary business-related results.
These results were used in countless reports, subfile screens and many green screen-related programs. But now, with so many Web programs and interfaces with other systems, what can we do?
Returning data sets using RPG
RPG doesn't understand sets, but SQL and all the external systems in the world do. What if, then, we could return a set of data that represents our business needs from an RPG program? First, let's look at the many ways to declare this procedure.
- External programs such as iSeries Navigator
- SQLRPGLE programs
- SQL licensed programs
What makes this procedure so powerful is that it opens your internal RPG programs up to the world. Using a DB2 database that needs to call an RPG program to return one value, can take ten files to get or return a result set. With SQL, it all can be done within RPG. Let's look at some examples.
To register an external SQL stored procedure, select the schema of your choice and select the "External" option:
[IMAGE]
Figure 1:Selecting the 'External option.
Once there, enter the information pertaining to your external procedure -- the parameters you're using, the result sets being returned and the type of parameters being used to send information to that program.
[IMAGE]
Figure 2:Enter the necessary information for your exter...
To continue reading for free, register below or login
To read more you must become a member of Search400.com
nal procedure.
You might be asking yourself, "Will this Contain SQL?" Or, "How will it be used within the program, and how do I commit the data changes, if any?" Don't worry, it's not as scary as it sounds.
In the 'Parameters' tab, 'Simple - no Nulls' is how most of your parameters will be passed. In the 'External Program' tab, you'll enter the program name, which is Schema(i.e., Library) and select RPGLE.
In SQL, the command would look as follows:
LIBUWANT contains the schema/library where the stored procedure will reside, LIB4RPGPGM is where the RPGLE program you are calling exists and the parameters have been defined.
So now that we are set up, the question remains: "How can we return data?" Our choices are:
- Returned in the parameters (the fields defined as 'OUT')
- Returned as data sets
- Passing back a multiple-occurrence data structure (only recommended to those handy with RPG)
In my RPG program, to return the cursor, I use the following code:
As you define your fetches, along with deciding how you'll perform the selected operations, you can pass that information to the external called procedure. But there is another method. In my definition specification, I have defined a multiple-occurrence data structure for this:
Click here for the code (PDF)
You've now returned the values to the stored procedure called earlier. To then call this from SQL, you'd use the the following code:
Click here for the code (PDF)
Presto! You have a program usable with anything SQL-compatible. Return the data to the external system, and your RPG system has just become SQL-friendly.
The lesson here is that stored procedures have the power to open your RPG programs up to a world they haven't yet encountered.
