Calling CL Programs as Stored Procedures
I have an Excel spreadsheet running a SQL statement that is retrieving data from an AS/400. Works great. But I need to cleanse the data before putting it in the spreadsheet. I have a CL program running on the AS/400; if you supply it with two dates (in MMDDYY format), it will filter the records, massage some data, and create a file called OQTD02PF. Once this file is created I want the SQL statement in Excel to run. Can I run a CL program passing parameters from the Excel spreadsheet? The spreadsheet has the dates on it already, in cells A1 and A2 on a sheet called source data.
Yes, you can do all of these things and more from inside the Excel spreadsheet by using SQL, ADO, and a macro. First, let's talk about calling CL programs on the AS/400 (or iSeries). There are two ways to call a program, as a declared stored procedure or using the QCMDEXC API to call any program on the iSeries. A declared stored procedure is when you have a program on your iSeries and you then run the CREATE PROCEDURE command to define the program to SQL. This causes entries to be made in your system catalogue that describe the external program and the parameters that the program takes. Once the procedure is described, SQL-based programs can inquire about the procedures available on your iSeries and execute procedures on your box. This is a really cool way to reuse your business logic.
Use an API
One way to call a CL program on the iSeries is to use the QCMDEXC API. This allows you to call any program, whether it is a command, a stored procedure, or whatever. The QCMDEXC API takes two parameters, the command string to execute and the length of the command string as a decimal 10,5 number. Here is an example of how you could call the STRDBG (start debug) command using the QCMDEXC API from a VBScript macro.
Dim Con1 as new ADODB.Connection Dim Cmd1 as new ADODB.Command Con1.Open "DSN=MY400;UID=HARNER;PWD=SECRET" Cmd1.ActiveConnection = Con1 Dim Stmt as String Stmt = "Call QSYS.QCMDEXC('STRDBG UPDPROD(*YES)', 0000000020.00000) Cmd1.CommandText = Stmt Cmd1.Execute
Let's walk through the command one line at a time. The first two lines declare the ADO Connection and Command objects. They are used to communicate with the iSeries and to execute commands. The next line uses the Open method of the Connection object to open a connection to the iSeries. In this case, I am using an ODBC data source called MY400 and passing the user ID and password in order to connect. The next line of code associates the command object CMD1 with the open connection object Con1. I then declare a string variable call Stmt, and on the next line I place the text of the command I want to execute into the string variable. Notice that I used the SQL CALL command. CALL is how you let SQL know that you want to execute a stored procedure. The stored procedure that I want to execute is actually the QCMDEXC API, located in the QSYS library. I pass this API the command string to start debug and the length of the command string as the second argument. In the next line of code, I set the CommandText property of the command object to the value of the Stmt variable, and on the last line I use the Execute method to cause the command object to send the command to the iSeries. The iSeries then dutifully places my job into debug mode.
It is easier to call a program if it is declared as a stored procedure because ADO will help you pass different data types to the program. Let's create a small CL program and illustrate both ways to calling it. First, create a source physical file and then add the code from the following example:
PGM PARM(&LIB &FIL) DCL VAR(&LIB) TYPE(*CHAR) LEN(10) DCL VAR(&FIL) TYPE(*CHAR) LEN(10) DSPDBR FILE(&LIB/&FIL) OUTPUT(*OUTFILE) + OUTFILE(QTEMP/MYDSPDBR)
Use the CRTCLPGM program to compile this source into a CL program called SQLBOOK/CLDSPDBR1 and then you are ready to rock and roll. This simple little CL program takes a library name and file name and runs DSPDBR command against the supplied information. The output of the command is redirected to the QTEMP library and places in a file called MYDSPDBR. Because my job is in the same activation group as the CL program, I will be able to read any files created by the CL program in QTEMP. Below is a simple VB program, along with two function definitions, that uses the QCMDEXC API to call our command. Note that the function Rpad is used to pad a string on the right with a specified character and Run4Cmd is used to properly format the call to QCMDEXC.
Private Sub Form_Load() Dim Con1 As New ADODB.Connection Dim Cmd1 As New ADODB.Command Dim Rs As New ADODB.Recordset Con1.Open "DSN=MY400;UID=HARNER;PWD=SECRET;" Cmd1.ActiveConnection = Con1 Cmd1.CommandType = adCmdText Cmd1.CommandText = Run4Cmd("CALL SQLBOOK/CLDSPDBR1 PARM(MCEIS BRANCHES)") Cmd1.Execute Cmd1.CommandText = "select * from qtemp.mydspdbr" Rs.CursorLocation = adUseClient Rs.CacheSize = 100 Rs.Open Cmd1 While Not Rs.EOF Debug.Print Rs.Fields("WHRFI").Value Rs.MoveNext Wend Con1.Close End Sub Function Rpad(x As String, l As Long, c As String) As String Dim sl As Long sl = Len(Trim$(x)) If sl < l Then Rpad = String$(l - sl, c) & Trim$(x) Else Rpad = x End If End Function Public Function Run4Cmd(ctxt As String) As String On Error Resume Next ctxt = "call qsys.qcmdexc('" & ctxt & "'," & _ Rpad(CStr(Len(ctxt)), 10, "0") & ".00000)" Run4Cmd = ctxt End Function
This program connects to the iSeries and then uses the Run4Cmd function to prepare a call to the QCMDEXC API. Once the call is complete, the program reuses the command object to select all of the files from the physical file the program created in QTEMP. Finally, the program goes into a loop, reading the records from the physical file and writing the contents of the field WHRFI that was returned by the select statement to the interactive debug window.
Doing It with a Procedure Definition
Now let's create a stored procedure definition for our command and I'll show you how it makes it a little easier to call the CL program. First, start an interactive SQL session or use my SQLThing tool and enter the following command:
CREATE PROCEDURE SQLBOOK.CLDSPFDP ( IN LIB CHARACTER(10), IN FIL CHARACTER(10)) RESULT SETS 0 EXTERNAL NAME SQLBOOK.CLDSPDBR1 LANGUAGE CL PARAMETER STYLE GENERAL
Once this command is executed, you have caused the procedure definition to be written to the QSYS2.SYSROUTINES file and the definition of the procedure parameters to be written to the QSYS2.SYSPARMS file. This definition defines how to call the CL program CLDSPDBR1. Note that I called the procedure CLDSPFDP. The procedure name has nothing to do with the name of the command I want to call. The name of the command is defined by the EXTERNAL NAME definition in the CREATE PROCEDURE statement. I could have called this procedure BUPKIS; the name of the procedure is irrelevant.
Now that these system catalogue entries have been recorded, you can use the program as an SQL procedure. The advantage of an SQL procedure is that SQL will handle the marshaling of parameter data to and from the procedure. Here is an example of the code needed to call the procedure:
Private Sub Form_Load() Dim Con1 As New ADODB.Connection Dim Cmd1 As New ADODB.Command Dim Rs As New ADODB.Recordset Con1.Open "DSN=GEM2;UID=HARNER;PWD=HARNER;" Cmd1.ActiveConnection = Con1 Cmd1.CommandType = adCmdText Cmd1.CommandText = "Call SQLBOOK.CLDSPFDP (?,?)" Cmd1.Parameters.Append Cmd1.CreateParameter("LIB", adChar, adParamInput, 10, "MCEIS") Cmd1.Parameters.Append Cmd1.CreateParameter("FIL", adChar, adParamInput, 10, "BRMASTER") Cmd1.Execute Cmd1.CommandText = "select * from qtemp.mydspdbr" Rs.CursorLocation = adUseClient Rs.CacheSize = 100 Rs.Open Cmd1 While Not Rs.EOF Debug.Print Rs.Fields("WHRFI").Value Rs.MoveNext Wend Con1.Close End Sub
Notice that in this version of the code I am still using a command object, but I am not using the QCMDEXC API. Instead, I am calling the program using the SQL CALL statement, to call a recognized stored procedure on the iSeries called CLDSPFDP. If you look in the SQLBOOK library, you will not see a program or object called CLDSPFDP. That's because the object doesn't exist; there's only an entry in the system catalogue telling SQL that anyone attempting to run a procedure called CLDSPFDP should cause the program CLDSPDBR1 to be executed.
After formatting the call statement, I am using the command object to create two parameter objects, so that I can pass the expected Library and Filename to the stored procedure. I could have used a method of the command object called Parameters.Refresh to have the parameters collection automatically instantiated from the iSeries system catalog, but by creating the parameters manually I save a round trip between client and server, which cuts down on processing time. Notice that during the creation of the parameter objects I also set their value. You see, the CreateParameter method takes five arguments: parameter name, data type, parameter direction, parameter length, and the initial value. So when I create the parameter and append it to the parameters collection, I am also setting the value that I want to pass to the iSeries. Other than the difference in the command text property and the creation of parameters, the performance of the two example pieces of code should be identical. However, the second method will outperform the first, because the procedure is declared and does not have to go through the command API.
Back to the Future
Back to your initial question. To get your spreadsheet working, you will need to write a VBScript macro to grab the values from cells A1 and B1 and format the values into something that the iSeries expects. Since this tip has gone on so long, I will leave that as an exercise for the reader of this tip. Just kidding! Since I hate not answering a question, the next tip I write will be an example of how to create an Excel macro that calls a stored procedure, passes values from the spreadsheet to the procedure, and writes the results into another spreadsheet tab. Until the next Guru, cheerio!
Howard F. Arner Jr. is a writer and consultant with Client Server Development and the author of iSeries and AS/400 SQL at Work. Howard also designed SQLThing Enterprise Edition, a query program and stored procedure editor specifically for the AS/400's unique capabilities. You can purchase a copy of Howard's book or learn more about SQLThing at www.sqlthing.com. Send your SQL questions to firstname.lastname@example.org or go to www.sqlthing.com to find out more about SQL on the iSeries.
Contact the Editors
|Copyright © 1996-2008 Guild Companies, Inc. All Rights Reserved.|