Midrange Programmer

OS/400 Edition
Volume 1, Number 21 -- November 21, 2002

Data Is Served: A VARPG Data Server

by Raymond Everhart

[The code for this article is available for download.]

I was invited to a working lunch last week to discuss real-time retrieval of Microsoft Access data for use in an iSeries application. We ended up at my favorite Mexican restaurant. It's the kind of place that is so authentic that my waiter barely spoke English. He was polite, quick to smile, and brought the food so fast that I couldn't help but be impressed. The only problem was that he didn't bring a single thing that I ordered.

It was frustrating to say the least. I could smell the food. I could see the food (on someone else's plate). But I just couldn't get my waiter to bring me what I wanted. Now, if I had just ordered the No. 5 special combo dinner, I'm sure that all would have gone well. But one of the reasons why this is my favorite restaurant is that they will make me exactly what I want, even if it isn't on the menu. I feel the same way about retrieving data from PC-based applications. I may not want an export file. Instead, I may want a specific transaction or the inventory balance of a certain item. I'm not trying to trivialize the problem, but if I can write a program to get the data I want from the iSeries, then why not from a PC?

In "The Opportunity of a Lifetime" and "Using Data Queues in VARPG," I explained how to use ODBC/JDBC parts and data queues in VARPG. I used an ODBC/JDBC part to retrieve data from an Access database, and I used data queues to communicate between programs. In this article, I will explore the use of data queues to serve Microsoft Access data back to an iSeries RPG IV program. For this example (Figure 1) I will use two data queues: one to send the data request to the VARPG program and another to return the retrieved data to the RPG IV program.

Figure 1: A functional overview

A Table for Two Databases, Please

First take a look at the two databases involved. The first table is on the iSeries. The ITEMMST table contains the item number and some related information. The DDS for this file looks like this:

A          T.Name++++++RLen++TDpB      Functions++++++++++++++++++
 
A          R ITEMMSTR
A*
A            ITEMNUM       15A         TEXT('Item Number')
A            ITMDESC       30A         TEXT('Item Description')
A            ITEMCLASS      5A         TEXT('Item Class')

A          K ITEMNUM

The second table, ITEMLOC, is located in an Access database named WhsLocator.mdb. The ITEMLOC table is part of a warehouse locator system, and it contains the warehouse number, item number, location, and quantity of the item in that location. There can be multiple ITEMLOC records for each ITEMMST record.

The data in the ITEMLOC table looks like that in Figure 2.

Figure 2: Data in the ITEMLOC table

To pass data between programs, the data queue will be subdivided using an externally described data structure. This data structure will form the basis for all communication between the two programs. It is important that this data structure allow for a controlled and meaningful exchange of information. This data structure needs to be carefully designed, or you will end up with something like a Moo Goo Gai Pan enchilada. This VARPG application was designed to handle multiple RPG IV requestor programs. In order to ensure that the requestor gets the data it requested, the RESPONSE data queue must be keyed. To simplify this application, the same data structure will be used for both the REQUEST and the RESPONSE data queue:

A          T.Name++++++RLen++TDpB      Functions++++++++++++++++++
   
A          R DTAQDSR 
A*
A            JOBNAME       10A         TEXT('Job Name')
A            USERID        10A         TEXT('User ID')
A            JOBNUM         6A         TEXT('Job Number')
A            RQNUM          9A         TEXT('Request Number')
A            COMMAND       15A         TEXT('Command')
A            CMDDATA       25A         TEXT('Command Data')
A            RTNCODE        5A         TEXT('Return Code')
A            RTNDATA       25A         TEXT('Return Data')

The following fields will be used:

JOBNAME--This is the RPG IV job name.
USERID--This is the RPG IV user ID.
JOBNUM--This is the RPG IV job number.
RQNUM--This field is incremented with every request made by this job. Using this field as part of the key allows you to ignore any garbage entries for this job that may exist on the data queue.
COMMAND--This is the name of the function in the VARPG program that we want to execute.
CMDDATA--This field is any data required to execute the COMMAND in the VARPG program.
RTNCODE--This field contains a result code or error code from the VARPG program.
RTNDATA--This field contains the data retrieved from the execution of the function named in COMMAND.

The JOBNAME, USERID, JOBNUM, and RQNUM fields make up the key to the RESPONSE data queue. By using a keyed data queue, the RPG IV job that sent the request can retrieve just the data that it requested.

May I Take Your Order?

Think of the RPG IV program as your waiter. A screen is presented, and you tell the program what you want. In our example, when you supply an item number and press the Enter key, a list of item locations and quantities, retrieved from the Access database, are displayed in a subfile.

The logical flow of the program is as follows:

Get the item number to display.
Validate the item number in the ITEMMST file.
Make sure that the VARPG program is running.
Pass the request for data to the VARPG program.
Retrieve the data from the data queue and write the subfile record.
Display the subfile.
Loop through steps 1 through 6 until the F3 Exit key is pressed.
Submit the command to end the VARPG program.
End the RPG IV program.

(View the source code for this program here.)

I Need a No. 2 Special, Hold the Guacamole!

In our example, the VARPG program is like a chef. The chef takes the food order from the waiter and prepares your meal. The VARPG program reads an entry from the data queue and, based on the function requested, performs certain actions. The logic flow of the VARPG program looks like that in Figure 3.

Figure 3: A data queue server logic flowchart

Did You Save Any Room for Dessert?

I've covered many topics and techniques in this series of articles. This approach to retrieving data from other systems has many potential applications. This example was designed to showcase some of the capabilities of VARPG, but some design considerations should be addressed before this technique is "production ready."

The first question to address is what if the VARPG server program is not running? The solution to this is relatively straightforward. Your application should assume that no response from the VARPG server program means that the server program needs to be started. See "Calling PC Commands from RPG," by Kevin Vandever, for a method for doing this.

Another question that should be addressed is what happens to requests that are placed in the data queue when the VARPG server is not running? In this example, the request will stay in the data queue until the VARPG server job is started. At that point, the request will be processed and the result will be placed in the response data queue. The response may be irrelevant by the time it finds its way into the response data queue, so some sort of a garbage collection routine is recommended. The routine could be as simple as clearing the data queue once a day or as complicated as checking each data queue entry against the list of active jobs.

Would You Like to Take That Home with You?

This example is probably more than you want to try in one sitting, so all of the source code and instructions that you need to build and run this sample application have been packaged together for you in a handy styrofoam container. Just try not to let it sit too long in the back of the refrigerator.

Raymond Everhart is an independent programmer/consultant in the Dallas/Fort Worth area and has 17 years of experience with IBM midrange servers. Raymond can be reached at reverhart@raecodesign.com.