These days, most companies that require E-commerce applications usually also require those applications to be integrated with their back-end systems. Wouldn't it be nice if we could leverage existing legacy logic to accomplish complex processes, data validation, or data updates? This can be done rather painlessly using stored procedures.

In this article, I will show you how to call a stored procedure on an IBM AS/400 from an ASP page. A large number of companies use AS/400's and could benefit from accessing legacy code for their web applications. The advantage of calling a stored procedure from ASP is that it allows you to build complex logic on the back-end using any language that is supported by the AS/400 (RPG, RPG ILE, CL, Java, Cobol, SQL, C).

Creating a Stored Procedure on the AS/400

The first thing we need to do is to create the stored procedure on the IBM AS/400. There are two steps involved with this process. First, we'll need to create the program that contains all of our logic. Second, we'll need to wrap this program in a SQL stored procedure defining all of the Input, Output, and Input/Output parameters. I will use a simple RPG data retrieval program for this example, but a data validation or data update program could also be used.

Creating the Data Retrieval Program

For the purpose of this article, we will be using a very simple RPG program to retrieve our data from the AS/400. Our RPG program will read from a Customer file by passing in the customer ID and retrieving the customer's name and phone number. The source code for the RPG program can be found in the downloadable material for this article under the file name of RPG_SOURCE.TXT.

The tables (files) on the AS/400 are set up a little differently than on a SQL Server. There are two files used: the physical file and the logical file. The RPG program uses the logical file to read the data that we need, since it is the logical file that is set up with the customer ID as the primary key (you can think of a logical file on the AS/400 as being the equivalent to a SQL view). Both of these files need to be defined on the AS/400 in the QDDSSRC file. The source for both the physical and logical files can be found in the PHYSICAL_SOURCE.TXT and LOGICAL_SOURCE.TXT files respectively.

Here is how our Customer file is set up:

Creating the Stored Procedure

We now need to create the stored procedure that will reference the data retrieval program. To create the stored procedure, we first need to start an interactive SQL session on the AS/400. This is done using the STRSQL command. Once the interactive SQL session is running, we can hit <F4> to be prompted for the action that we want to take. We will select the Create Procedure option (Option 9). We can now specify the name that we want to assign to the stored procedure (I called this procedure RTV_CUST_INFO). Now we need to specify what language our program uses. In this case, I chose to use RPG, but you would enter whatever language you used to create your program. We can now advance to the next screen by hitting <Enter>.

On this screen, we need to specify the name of the program we are going to use and indicate whether or not that program uses SQL. We also need to indicate the type of results that the stored procedure will return. Deterministic should be set to "N" so that the stored procedure will return the same result each time it is called. We can now advance to the final screen.

On this screen, we need to define the parameters that are needed, their type, and their direction. In this example, I have one input parameter (the customer ID), and two output parameters (the customer's name and phone number).

Once all of the above is completed, we can hit <Enter> and we will get a screen like the one in the screenshot below. This screen shows the SQL statement that has created the stored procedure, as well as providing us with a confirmation message that the stored procedure was created successfully. Unless you have memorized the SQL command required to create your stored procedure with all the desired parameters, your best bet is to go through the procedure described above.

Creating the ODBC DSN to the AS/400

We now need to create the DSN (Data Source Name) so that the ASP has a way to connect to our AS/400 and call the stored procedure successfully. This can be done using the ODBC Administration. We will need to select the System DSN tab and click on the Add button. Once that is done, we need to select the driver that we want to use for our database connection. Select the Client Access ODBC Driver (32-bit), as shown in the screen below. Please note that you need to have Client Access installed on the Windows NT server for this to work.

The next step is to name our DSN and, if multiple AS/400's are present, to specify which AS/400 we want to connect to. This is shown in the next screenshot:

Once this is done, we will need to go to the Server tab to specify the default library we want to connect to on the AS/400. In our case, we have all of our objects stored in the same library, MYLIB. You can specify multiple libraries and/or library lists for procedures that are more complex and span multiple libraries. The screen below shows these settings:

The last step in the creation of the DSN is to set the Translation settings. This is to ensure that the data that gets passed back and forth between the Windows NT Server and the AS/400 Server is valid. We need to select the Translate CCSID 65535 option (see next screen shot). Click on the OK button, and we are finished with creating the DSN.

Getting Client Access to Run Under the NT Service

There is one more step we need to take to be able to use the DSN we just created. Because Client Access is not set up to run as NT service we need to manually run a Client Access command at the Windows NT command line, this command will allow Client Access to run under the IIS service. The command we need to run is as follows:

CWBCFG /HOST <NAME OF AS/400> /S

<NAME OF AS/400> should be replaced with the name of your AS/400 on the network. If you do not have an entry in your HOSTS file in windows for your AS/400 you might have to include the /IPADDR <IP ADDRESS> parameter when you run the CWBCFG command.

Please note that if you have multiple AS/400s you wish to connect to, you should run the CWBCFG command for each of them.

Creating the ASP to Call the Stored Procedure

We now need to create the ASP page that will call the stored procedure on the AS/400. Again, this is a two-step procedure. The first step involves the creation of a submission page. The page houses a simple user form, into which the user will enter the customer ID. When submitted, this form will call our processing page. The second step is creating the processing page itself, which will call the stored procedure and display the customer's name and phone number.

Creating the Submission Page

This page is rather simple. It contains a user form with one input text field (for the customer ID) and a Submit button. The code for this page is as follows:

<html>
<head>
<title>Submission page</title>
</head>
<body bgcolor="#FFFFFF">
<form method="post" action="processing.asp" name="custid">
   <p align="center"><b>Please enter your Customer ID below.</b><br>
      <input type="text" name="textfield" size="10" maxlength="10">
      <br>
      <input type="submit" name="Submit" value="Submit">
   </p>
   </form>
</body>
</html> 
  

Here is what this page looks like when viewed in a browser:

Creating the Processing Page

As mentioned above, the processing page receives the customer ID from the submission page and calls the stored procedure on the AS/400. There are several steps we have to follow to achieve this goal. We must first include the file containing the VB Script constants (adovbs.inc). Then, we must call the stored procedure and display the results.

Including the Necessary File

The only file that needs to be included is the adovbs.inc file. This file holds the values for all of the VB Script constants. We can include this file by entering the following line of code at the top of our ASP page:

<!-- #INCLUDE VIRTUAL="included/ADOVBS.INC" -->

Please note that if you choose not to include this file in your ASP, you will need to enter the actual values of the VB Script constants in your code.

Setting up the Connection and Command Objects

We must now create the connection and command objects so that we can call the stored procedure. The code to create these objects is as follows:

Dim conn, cmd

'Set up the Connection object
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "MyConnection", "userid", "password"

'Set up the Command object
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn

Please note that you will need to specify the DSN name, userid, and password for your AS/400.

Setting up the Parameters for the Stored Procedure

There are several ways of setting up the parameters to call a stored procedure. However, I have found that to successfully access the output parameters of a stored procedure on the AS/400, you must manually define all of the parameters for that stored procedure. Other methods, such as the Refresh method, will not allow you to properly access the output parameters (that is, the output parameters will always be blank). However, the Refresh method will work just fine if the stored procedure you are calling does not have any output parameters (for example, a procedure that updates a file on the AS/400). The stored procedure that I am using for this example has one input parameter, the customer ID, and two output parameters, the customer's name and phone number. Here is the code for setting up the parameters properly:

'Define the stored procedure
cmd.CommandText = "RTV_CUST_INFO"
cmd.CommandType = adCmdStoredProc

'specify parameter info 1 by 1
cmd.Parameters.Append cmd.CreateParameter("custid", adChar, adParamInput, 10)
cmd.Parameters.Append cmd.CreateParameter("custnm", adChar, adParamOutput, 50)
cmd.Parameters.Append cmd.CreateParameter("custph", adChar, adParamOutput, 30)

'Assign value to input parameter
cmd.Parameters("custid") = Request("custid")
cmd.Execute

Note that we must set the value for the input parameter before we execute the stored procedure command.

Referencing the Output Parameters and Generating the Results

Once the stored procedure has been called, we need to display the values of the two output parameters. We can do this by setting up two variables, CustName and CustPhone, which we will reference later in our code. Here is how we assign the correct values to the two variables mentioned above:

Dim CustName, CustPhone        'As String

'Assign the value of the output parameters to CustName and CustPhone
CustName = cmd(1)
CustPhone = cmd(2)

Now that the customer's name and phone number are assigned as values of the above variables, we can display them on the results page using the Response.Write method as shown below:

Response.Write "Customer Name: " & CustName & "<br>"_
      "Customer Phone Number: " & CustPhone

Debugging your Stored Procedure on the AS/400

Whether or not this is the first time you are trying to call a stored procedure on an AS/400, chances are that it will not work on the first try. However, debugging your stored procedure can be problematic, as the most likely source of the error is the data retrieval program. Because the stored procedure and data retrieval program are both running in a subsystem called QSERVER, they are not easily accessible to the STRDBG (Start Debug) command. The following two sections will describe how we can monitor the subsystem job and run the debugger over it.

Monitoring the Subsystem Job for the Stored Procedure

In order to run the debug command, we need to find out the job name, the user ID of the user that is running the job, and the job number for the stored procedure. To do this, we need to run the WRKACTJOB (Work Active Job) command from a command line on the AS/400. Once the stored procedure is invoked from the ASP, a new job will appear under the QSERVER subsystem (this job's name will be QZDASOINIT). If we select Option 5 (Work With Job), we will be able to see the job name, user name, and job number. Please write this information down, as we will need to use it later.

For the debugger to debug our data retrieval program, our AS/400 session has to know when that program is called in the QSERVER subsystem. This is where the STRSRVJOB (Start Service Job) command comes in. By typing the STRSRVJOB command and pressing <F4>, we are prompted to enter the job name, user name, and job number. Once we hit <Enter>, the job will start to be monitored. The next step is running the debug command.

Running Debug on the Stored Procedure

Running the debugger on the AS/400 is rather simple. All we need to do is type STRDBG (Start Debug) on the command line and press <F4> to prompt the command. Now we must enter the name of the program we want to debug and the library that it resides in. In our case, the program name is RTV_CUST and it resides in library MYLIB. Note that if your stored procedure was to update any files on the AS/400, you would need to set the Update Production Files option to "*Yes". Otherwise, you will get an error when the program tries to open a file for update.

Now that the debugger has been started, we need to add breakpoints. This can be done using the ADDBKP (Add Breakpoint) command, followed by a space and the line number where you want the program to break (for example, ADDBKP 5400). We can now call the stored procedure from the ASP again and the debugger will automatically stop at the line number that we specified using the ADDBKP command. If you would like to view the value of a certain variable, you can type in the following:

DSPPGMVAR [variable name]

For a more detailed explanation of how to debug stored procedures on the AS/400, please see the following article (this article is written in two parts):

http://www.midrangecomputing.com/mc/article.cfm?titleid=a325&md=20007&WROXEMPTOKEN=1607759Zyx5mStFiYnR0jcEM2C

And

http://www.midrangecomputing.com/mc/article.cfm?titleid=b1464&md=20008&WROXEMPTOKEN=1607759Zyx5mStFiYnR0jcEM2C

Conclusion

In this article, I have shown you how to create and debug a simple stored procedure on the IBM AS/400 and how to call it from your ASP applications. This method can be used to perform much more sophisticated processes, data retrieval, and data updates. I think you will find this to be a powerful tool in web-enabling legacy systems for E-business applications.