Midrange Guru

Last week I explained how to call CL programs as stored procedures. This week I will show you how to write an Excel spreadsheet macro that calls a stored procedure. This is fun stuff, and it shows the power of VBScript in harnessing the capabilities of your iSeries.

Here are the steps required to make the CL program on your iSeries and to define it as a stored procedure. This CL program takes a library and file name as arguments and writes the results of a DSPDBR (Display Database Relations) command to a file in QTEMP. Our spreadsheet will call the program, then read the results of the program from QTEMP and display them in the spreadsheet.

First, create a source physical file, then add the code from the following example:

Use the CRTCLPGM program to compile this source into a CL program called SQLBOOK/CLDSPDBR and then you are ready to rock and roll. This simple little CL program takes a library and file name and runs the DSPDBR command against the supplied information. The output of the command is redirected to the QTEMP library and places it 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.

Next, start an interactive SQL session or use the SQLThing tool to execute the following SQL statement:

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 CLDSPDBR. Note that I called the procedure CLDDSPDBR_PROC. 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.

First, you need to start Excel and create a blank workbook. Once you have Excel running, right-click the toolbar and check the Visual Basic and Control Toolbox toolbars in order to add them to Excel. These toolbars make it easy to create macros and forms in Excel.

Move to cell A1 and enter the text ODBC Data Source, then resize column A1 so that the text fits within the column. Move to cell B1 and enter the name of an ODBC data source that is set to connect to your iSeries. If you do not have an ODBC data source set up, refer to this page.

Navigate to cell A2 and enter the Text User ID, then move to cell B2 and enter your User ID. Next, in cell A3, enter the text Password and move to B3 and enter your iSeries password. Navigate to A4 and enter the text Library and then in B4 enter the name of one of your libraries on the iSeries to use as a default. Finally, go to A5 and enter the text File Name and then move to B5 and enter a default file name that exists on your iSeries.

To add a button, you need to enter design mode. On the Visual Basic toolbar, click the design mode button (looks like a triangle over a ruler with a pencil next to it) to place the spreadsheet into design mode. Then, from the Control Toolbar, place the button in cell B6. Click the button icon in the Control Toolbar again to turn off control placement mode, then right-click the button you placed in cell B6. From the menu that appears, choose Command Button Object and then choose Edit. These actions will place the button in edit mode, thus allowing you to change the button text. Make the button read Execute and then click anywhere else in the document to finish editing the button.

Now that you have a form for data entry, you need to create a VBScript macro that talks to your iSeries and brings back data. To do this, start the Visual Basic editor. Click the Tools menu and select Macro, then Visual Basic Editor. The VB integrated development environment (IDE) should appear. On the left of the IDE, you should see a Project window, which lists all of your current VB projects. If you have not yet saved the spreadsheet, you will see an item called VBAProject (Book1), which is the current spreadsheet. Under the folder labeled Microsoft Excel Objects, you should see an entry called This Workbook. Double-click the "ThisWorkbook" entry to get an VB Editor window, so you can enter some code.

To talk to the iSeries, we need to reference the Active X Data Objects library in our code. To reference a COM object, select the Tools menu and select References. A list of registered COM components will be displayed. Scroll the list until you see Microsoft ActiveX Data Objects 2.x Library (actually it will be 2.1, 2.6, or, in my case, 2.7; it depends on the version of Windows and Office programs are running). Check the Microsoft ActiveX entry and press OK. You will now be able to use ADO to communicate with your iSeries.

Let's start entering some code. Paste the following code into the VB Editor, or you can download this working spreadsheet.

Now that you have the code in the editor, you are ready to connect the command button to this macro. Navigate back to your spreadsheet and put the spreadsheet in design mode (click the icon with the triangle, ruler, and pencil), then right-click the command button and choose the View Code option. This will place you back into the VB IDE and allow you to associate code with the Click event of the button. Place the following code into the editor:

The above code causes the spreadsheet to invoke the RunProcDSPDBR procedure whenever the button is pressed. Press the File menu and select Close and Return to Microsoft Excel. You are now ready to use the spreadsheet.

When you press the button, a Click event is generated. Since you wrote code to perform an action when the click event occurs, the VB procedure RunProcDSPDBR is invoked. The first thing the procedure does is Dim the different variables it will need to do the work. These include command objects, connection objects, and Recordset objects. The VB procedure then makes Sheet1 the current spreadsheet and reads the contents of the B cells into program variables. It then uses the Open method of the ADO Command object to initiate a connection to your iSeries. Once the connection is open, it associates the connection with a command object and begins to set up a call to the stored procedure. The program creates two ADO parameter objects so that it can pass the library and file name to the stored procedure on your iSeries, and places the value from the cells in sheet 1, in the appropriate parameters, and calls the Execute method. Execute causes the spreadsheet to send the procedure request to the iSeries, where the iSeries then looks up the actual object name of the program and marshals the passed parameters to the program and executes it. Our CL program then executes a DSPDBR command against the supplied library and file, then writes the data to a file in QTEMP and stops execution.

At this point, control is returned to the calling program, spreadsheet, which places an SQL statement into the command object and uses an ADO Recordset object to open the command. The SQL statement joins the MYDSPDBR data to the system file QADBXFIL, which contains a listing of all physical and logical files on your iSeries. I join the output to this file in order to be able to get the file type and description that is stored in the system catalogue.

Once the Recordset of data is opened, the program calls the subroutine MakeHeaders with the value of the Library and Table variables. This subroutine changes the spreadsheet to sheet 2, clears the contents of the sheet, then sets up pretty headers for the output of the program. Once this subroutine completes, the program uses the Range object to make A5 the active cell, and enters into a loop for each record returned from the iSeries. In this loop, the program fishes out the data from the returned record and writes the data into the spreadsheet using the ActiveCell object. See, ActiveCell has a method called Offset, which we can use to point to a cell x number of rows and columns from the currently active cell. Since I know my active cell is A5, I just use the offset to calculate the next cell for writing data. After I write a row of data, I increment R to point to the next row of cells and continue to use the offset method to point to the next output cells.

When all rows have been read from the Recordset, the program closes the Recordset and the connection and makes cell A1 the active cell, so your viewpoint is the top of the spreadsheet. In addition, the program turns on screen updating, so all of the data on the screen is refreshed.

ADO and VBScript are neat ways to talk to your iSeries, call programs, and return data. I hope you have enjoyed this tip. I know I enjoyed writing it. If you want more information on VBScript or ADO, or want more examples of spreadsheet integration, go to the Resources section on the Client Server Development Web site.

Howard F. Arner, Jr., is a writer and consultant with Client Server Development, and 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 harner@sqlthing.com or go to www.sqlthing.com to find out more about SQL on the iSeries.