The AS/400 has fantastic support for using stored procedures. Stored procedures are an excellent way to reduce your communications overhead, to encapsulate business logic on the server, and to make your client/server programs and Web pages more robust. In this article, I’ll introduce you to stored procedures by explaining what they are and by showing you how to call RPG programs as stored procedures.

What Is a Stored Procedure?

At its heart, a stored procedure is just a mechanism for calling a program on the AS/400 via SQL. That program can be written in any language—C, CL, RPG, Java, or COBOL—that executes natively on the AS/400. The stored procedure itself is just a declaration of the variables that need to be passed to and returned from the program you are calling, the definition of the language that the program was written in, and the other information about the program that you want to run.

When you declare a procedure, the definition of the procedure and its parameters is stored in the system catalogue. When a program wants to call the procedure, the AS/400 uses the procedure definition to correctly marshal arguments back and forth between the caller and the host. The nice thing about this is that the AS/400 and SQL handle all of the messy details like platform data conversion for you. Stored procedures are a cool way to open up business logic on your AS/400 to other platforms.

Why Use Stored Procedures?

Every day, I consult with companies that ask the same question: “How do we rearchitect our legacy application to support multiple presentation layers?” If the logic of your application is sound, why on earth would you want to rearchitect? You want the fastest possible path to client/server and Web deployment of your applications, so you want to reuse every piece of code you possibly can.

A company has a huge investment in legacy code. Like fine wine, legacy systems take years to properly mature. Once they have matured, why should a company invest money in rewriting the application in Common Object Request Broker Architecture (CORBA) or ILE or as Java Servlets? If RPG and COBOL code have run your business fine for all of these years, there is no need to abandon code that works. You can reuse your

existing skill set, instead of hiring expensive experts, to migrate your code to use stored procedures.

Once a program is defined as a procedure, it can be called from any platform that supports SQL and has a driver/provider for the AS/400. You can call it using ODBC, OLEDB, JDBC, SQLJ, DB2 Connect, etc.

By implementing procedures, you gain client/platform independence while maintaining your business logic next to your data on the AS/400. Client/platform independence allows you to make a 5250 interface, a Web interface, or a GUI Visual Basic (VB) interface to your business logic. This is the world as it should be.

Another reason for using stored procedures is that they reduce the communications overhead between client and server. Imagine that you have a custom program that calculates the price of an inventory item for a specific customer. First, the program needs to look at the customer record to see if the customer gets special pricing. Next, the program looks at the project file to see if the customer gets special bid pricing on this project. Last, the program looks at the item discount file to see if there are special quantity breaks for that item. If you were to replicate this logic in a client program, the client program would have to prepare and execute three SQL statements (or do three Record Level Access reads via OLE DB) to have all of the data required to do the logic. Each prepare, execute, and fetch instance causes multiple TCP/IP packets between the client and the AS/400. If you place all of the logic in a stored procedure, you reduce the time for completion by removing the network latency introduced by client/server communications. Only one prepare and one execute statement are required, and that encapsulates all of the logic for the pricing program.

I once had a client program that added records to a master file. It basically had to execute five SQL statements to verify the master record and then execute a final insert statement. When performed from the client machine, I could do all of these operations in about one second.

By encapsulating all of the operations into a stored procedure, I was able to insert over 25 records per second from a single client into the master file. Stored procedures equal performance.

A Simple RPG Procedure

The best way to learn is by doing, so I will show you how to set up a small RPG program on the AS/400 and then call it as a stored procedure. This will expose you to all of the elements of the procedure definition and to the mechanisms available for calling the procedure from a client application.

The example stored procedure creates unique sequence numbers for a provided key. This can be useful generating items like purchase order numbers or ID numbers. The program takes a sequence ID as an argument and returns the next available sequence number for that ID. To work correctly, it needs a table called SEQCTRL. You would execute the SQL statements shown in Figure 1 to create the library, to create the SEQCTRL table, and to insert the records you need for the program to work. Each SQL statement is ended with a semicolon so that it can be executed from Operations Navigator’s Run SQL Script or from my SQLThing tool. If you use the Start Structured Query Language (STRSQL) command to run the statements, make sure you remove the semicolons.

Create a source physical file called QRPGSRC in the SQLBOOK library and add a new member in the file called RPGETSEQ. Enter the source shown in Figure 2 and then issue the following command to add the SQLBOOK library to your library list:

ADDLIBLE LIB(SQLBOOK)

Now execute the following command to compile the program:

CRTRPGPGM PGM(SQLBOOK/RPGETSEQ)
SRCFILE(SQLBOOK/QRPGSRC)

At this point, you should have a program that exists in the SQLBOOK library, called RPGETSEQ. You are almost ready to call the program as a stored procedure.

Creating a Procedure Definition

Before you can create a procedure, you need to add a procedure definition to your AS/400 system catalogue. To add the procedure definition, use the Start Interactive SQL (STRSQL) command, the Operations Navigator, or the SQLThing tool to issue the following SQL statement:

CREATE PROCEDURE SQLBOOK.MYPROCTEST
(SID IN CHAR (10), VLU OUT DECIMAL(9,0), RC OUT DECIMAL(9,0))
LANGUAGE RPG
NOT DETERMINISTIC
NO SQL
EXTERNAL NAME SQLBOOK.RPGETSEQ
PARAMETER STYLE GENERAL

The SQL statement declares that there is a procedure available on your AS/400 and causes entries to be written in the system catalogues describing the procedure and its parameters. The first line of the procedure declaration indicates that the procedure is called MYPROCTEST and that it exists in the library SQLBOOK. Even though the program to be called is RPGETSEQ, the procedure declaration can name the procedure anything it wants. In addition, the procedure does not have to be declared in the same library as the program to be called. When I want to declare procedures that will be called by lots of programs that have lots of different library lists, I usually declare them in the QGPL library because a lot of programs include that library.

The second line of the procedure tells SQL about the parameters of the procedure. This particular procedure takes three parameters. The first is an IN parameter of data type CHAR (10), and the parameter name is SID. You can name the parameters anything you want; they do not have to match the names of variables inside of the program. Notice that the words IN and OUT, in the declaration of the parameters, tell SQL how the parameters will be used. A parameter can be declared as IN, OUT, or INOUT. IN tells SQL that the parameter will be passed but that the caller is not be interested in a return value. OUT informs SQL that the caller is interested in reading the return value from the called procedure. INOUT indicates that the variable will travel into and be read from the procedure. Even if a parameter is declared as OUT, SQL passes to the called program any data you choose to put in the parameter. I typically declare the input/output functions of my parameters for the purpose of documentation.

Another thing to notice is the DECIMAL(9,0) data type of the second parameter. If you look at the SQL that creates the SEQCTRL table (Figure 1), you will see that the VLU column is an integer. However, since I am dealing with RPG, I need to declare the data type as a DECIMAL(9,0) for RPG to return the correct value to the calling program. Try declaring the data type as an integer, and it will not get passed back to the caller. This is a weird RPG quirk that you should watch out for.

The next line indicates the language of the procedure. In this case, I am declaring that the procedure is an RPG program. This helps SQL ensure that any variables are correctly massaged when they are passed to the program being called. You do not have to specify this unless the language of the program is REXX. If it is not specified, SQL will look at the program attributes of the procedure to determine the language it was written in.

The clause NOT DETERMINISTIC is a very important instruction to SQL. This indicates that, if you call the procedure over and over with the same parameters, it will

return different results. If a procedure is declared as DETERMINISTIC, SQL may elect not to execute the procedure in subsequent calls or return results from the previous call if the parameters are exactly the same. It is important that you define a procedure as either deterministic or not deterministic, as this can effect whether or not the procedure program is executed.

The clause NO SQL informs SQL that the procedure does not contain SQL statements. It is not necessary to specify this argument, but it is a good idea to inform SQL if a procedure does not contain SQL statements. This helps SQL determine how to best execute the procedure call. You could also specify that the procedure CONTAINS SQL or that it READS SQL DATA or that it MODIFIES SQL DATA. These arguments place restrictions on the type of SQL statements that can be used in the program being called.

The clause EXTERNAL NAME SQLBOOK.RPGETSEQ tells SQL the name of the external program that it will be calling when this procedure is executed. In this case, I am informing SQL to call the RPGETSEQ program in the library SQLBOOK.

Finally, the clause PARAMETER STYLE GENERAL indicates that the procedure will not accept null values as input parameters. Null-parameter passing requires special handling within the procedure code. The procedure code must accept an additional argument that is not passed from the caller of the procedure but passed via SQL during the invocation of the procedure. This argument is an array of indicator variables that tell SQL whether or not an argument is a null value. Since my RPG program does not need a null value, I can declare the call as PARAMETER STYLE GENERAL and save a few processing steps in the calling of the program.

Calling the Procedure

So far, I have created an RPG program, compiled it, and created a procedure definition. Figure 3 shows a simple VB program that can call the MYPROCTEST procedure. This program uses Active X Data Objects (ADO) and ODBC to connect to the AS/400. Note that it is using an ODBC data source name (DSN) called MC400CA as well as my user ID and password. You will need to create an ODBC data source (done via the Control Panel ODBC icon) and change the user ID and password to your user ID and password for this sample to run. When you create your ODBC data source, be sure to add the SQLBOOK library to your library list or the RPG program will fail. (The library list is controlled in the second panel of the data source setup if you are using the Client Access ODBC driver.) The code will fail because the procedure will not be able to find the SEQCTRL file if the SQLBOOK library is not in the library list. Finally, since ADO is used to talk to the AS/400, it must be referenced in Visual Basic (done via the Tools/References menu).

The first two lines declare ADO connection and command objects. The connection object is used to communicate with your AS/400, and the command object is used to execute SQL statements. The next line uses the Open method of the connection object to open a connection to your AS/400. Next, the command object is associated with the connection object by setting the command object’s ActiveConnection property. Now, the VB environment is ready to execute an SQL statement.

The CommandText property of the command object is set to the SQL statement that you want to run. In this case, I am setting it to “{CALL MYTEST.MYPROCTEST(?,?,?)}”. The braces ({}) tell ADO that it should use the ODBC calling convention when executing this procedure call. It is important that you always do this when calling a procedure via ADO with ODBC; otherwise, ADO will incorrectly retrieve procedure parameter information if you use the parameters refresh method. Even with the braces, ADO will incorrectly identify the decimal columns as character columns, which is why I never use the parameters refresh method and always declare my parameters by hand. For more information about stored-procedure parameter-passing using ADO, see my companion article “Pesky Stored Procedure Pitfalls” on the AS/400 Network Expert Web site (www.midrangecomputing.com/ane).

The next three lines of code describe the procedure parameters to ADO by adding items to the parameters collection. The parameters collection is what you use to set and get the parameters of a stored procedure call. The Append method is what places the item in the parameters collection. The CreateParameter method creates a new parameter object and takes the following arguments: parameter name, data type, parameter direction, data type size, and parameter value. In the first declaration, I am declaring a parameter called P1, which is a 10-character input parameter. The next parameter is called P2, and it is a double- precision numeric-output parameter. It is declared as double even though the procedure has it declared as Decimal(9,0). This is because ADO will mess up the retrieval of the data if I attempt to declare the parameters as integers or decimals when marshaling data to RPG programs. When retrieving parameters from RPG stored procedures, I typically use the adDouble data type for any numeric variables.

By creating your parameters manually, thereby not using the parameter refresh method, you can save time in program execution by reducing the amount of information retrieved from the server. My companion Web article also has a nice VB utility that writes the parameter declarations for your procedures. It will save you time.

The next line of code sets the value of the P1 parameter to PONUM. I could have passed this value when I created the parameter, but I wanted you to see how you can set it by referencing the parameters collection.

Next, the Execute method of the command object is called. This causes the command to be run on the AS/400.

The next line of code evaluates whether P3 parameter is equal to -1. If it is, the program shows you a message, informing you that there was an error during execution of the procedure. Otherwise, the value of parameter P2 is loaded into the variable X and is then displayed via the MsgBox function.

It is interesting to note that this code will execute not only in the VB development environment but also in Word, Excel, PowerPoint, Visio, and Windows Scripting Host or in an Active Server Page (ASP). This is because Microsoft has standardized ADO as the technology for building database applications, and it uses VB as the macro language in all of its products. If you want to write client/server programs that talk to your AS/400, VB is a language you want to learn, as it is portable between all Microsoft products and technologies. In addition, many other vendors have licensed the VB language as the macro language for their own products, so it is worth a look.

Where to Go from Here

Stored procedures are an effective way to speed up Web and client/server code. By separating your development logic into procedures, you can modularize your code on the AS/400, making the logic easier to reuse on other platforms as well as within other programs on the AS/400. You have seen how simple calling a procedure from a Web or VB client is, so why not make some procedures of your own and start expanding your legacy logic to other platforms?

CREATE SCHEMA SQLBOOK;

CREATE TABLE SQLBOOK.SEQCTRL
(SEQID CHAR(10) NOT NULL PRIMARY KEY,

VLU INTEGER NOT NULL WITH DEFAULT);

INSERT INTO SQLBOOK.SEQCTRL VALUES (‘PONUM’,0);
INSERT INTO SQLBOOK.SEQCTRL VALUES (‘WORK ORDER’,0);

Figure 1: These SQL statements create the necessary data.

FSEQCTRL UF E K DISK

F SEQCTRL KRENAMERSEQCT

*

C *ENTRY PLIST

C PARM REQKEY 10

C PARM VLU 0090

C PARM IND 0090

*

C REQKEY CHAINRSEQCT 98

C *IN98 IFEQ '0'

C ADD 1 VLU

C UPDATRSEQCT

C ELSE

C MOVE -1 IND

C ENDIF

*

C RETRN

Figure 2: This RPG program creates a sequence number.

Dim Con1 As New ADODB.Connection
Dim Cmd1 As New ADODB.Command
Con1.Open "DSN=MC400CA;UID=HOWIE;PWD=SECRET;"
Cmd1.ActiveConnection = Con1
Cmd1.CommandText = "{CALL SQLBOOK.MYPROCTEST (?,?,?)}"
Cmd1.Parameters.Append Cmd1.CreateParameter("P1", adChar, adParamInput,
10)

Cmd1.Parameters.Append Cmd1.CreateParameter("P2", adDouble,
adParamOutput)
Cmd1.Parameters.Append Cmd1.CreateParameter("P3", adDouble,
adParamOutput)

Cmd1.Parameters("P1").Value = "PONUM"
Cmd1.Execute
If Cmd1.Parameters("P3").Value = -1 Then

MsgBox "There was an error!"
Else

X = Cmd1.Parameters("P2").Value

MsgBox "Your Sequence Number is " & X
End If
Set Cmd1 = Nothing
Con1.Close
Set Con1 = Nothing

Figure 3: Use this VB code to call the procedure and correctly marshal the arguments back and forth.