Home

Calling CL Programs as Stored Procedures

 Dear Howard,

 I'm stuck on a problem and I thought maybe you could help. I have an Excel spreadsheet running a SQL statement retrieving data from the as/400.  Works great.  However, I need to cleanse the data before bringing it to the spreadsheet.  I have a CL program running on the AS/400 that if you supply it with 2 dates (mmddyy format) it will filter the records, massage some data and create a file called oqtd02pf.  Once this file is created then I want the SQL statement in excel to run.  Can I run a CL program passing parameters from the excel spreadsheet?  The spread sheet has the Dates on it already in cells A1 & A2 on a sheet called "Source Data".  Any help would be greatly appreciated.  Al~

 Dear Al,

 Yes, you can do all of these things and more from inside the Excel spreadsheet using SQL, ADO and a macro.  First of all, lets talk about calling CL programs on the AS/400.  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, 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

 

Lets walk through the command one line at a time.  The first two lines declare our ADO Connection and Command objects.  They are used to communicate with the iSeries and 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 place the text of the command I want to execute into the string variable.  Notice that I use 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 that is 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.  Next line of code, I set the CommandText property of the command object to the value of the Stmt variable and then 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. QED.

 

Now, 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. Lets 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 and filename 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 the function Rpad is used to padd 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 form 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, lets create a stored procedure definition for our command and 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 if I had so desired.  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 is because the object does not exist, only an entry in the system catalogue exists telling SQL that anyone attempting to run a procedure called CLDSPFDP should caue 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. See, the CreateParameter method takes 5 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 the 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 published by me 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 time, cheerio!