How to Call a Stored Procedure from Excel, Part 2
I enjoyed "How to Call a Stored Procedure from Excel." The only concern I have is providing the password to retrieve the data. Can't the Visual Basic code be changed so that the password for the system connection is prompted? My preference is to use Worksheets().Columns().Autofit for column resizing and Worksheets().Range().Select, ActiveWindow.FreezePanes = True for locking headings in the spreadsheet. Thanks for the article.
I like the auto-fit also but was having trouble getting my spreadsheet to look good, and it was late and I was tired, so I punted. As for the password, I agree that it should not be on the sheet. What you could do is, in the MACRO, add the following:
Pwd = InputBox("Please Enter Your Password", “Password”)
Replace the line:
Pwd = Range("B3").Value
InputBox is a function that causes the program to prompt the user, with a dialog box, for a password. The dialog will have the caption Password. The InputBox function will pop up a VB input box and prompt for a password and return it to the Pwd variable. Then you could remove it from the sheet and thus prompt the user for the password dynamically.
More on Security
The only problem with the InputBox function is that it displays the password in clear text. To get around this, you can use a form for data entry. To do this, start the macro editor (Visual Basic), then click the Insert menu and click User Form.
Press F4 to change the properties of the form and rename the form usrInput. Then proceed to create five text boxes on the form. Once the text boxes are created, use the F4 key to adjust their properties and name them txtDSN, txtUID, txtPWD, txtLIB, and txtFN. Next, add labels to the form, to make data entry easier, then select the txtPWD input box. Press F4 and set the PasswordChar property to an asterisk (*). This will cause any entries made into the input box to be represented as the asterisk character, thus hiding any user passwords from prying eyes.
The other thing you might want to do is to set defaults for the different prompts on the form. To do this, click the form element and press F4 to get the properties for that element. I set the VALUE property for my txtDSN text box to BAKERTEST, since this is the AS/400 I always access. I set the VALUE property for the txtUID text box to HARNER, since this is the user ID I always use. I set the default for the txtLIB field to HD1100PD, since this is the library I usually inquire about. You should set the defaults according to what you usually prompt to.
Next, you need to add code to the "Go" button that causes the procedure to be called and the information in the form to be passed to the procedure. To do this, double-click the "Go" button, which should reveal the click event for the command button. Place the following code into the click event:
Dim DsnSTR As String Dim CON1 As New ADODB.Connection DsnSTR = "DSN=" & txtODBC.Value & _ ";UID=" & txtUID.Value & _ ";PWD=" & txtPWD.Value 'Open the connection On Error GoTo BadOpen CON1.Open DsnSTR On Error GoTo 0 ThisWorkbook.RunProcDSPDBR txtLib.Value, txtFN.Value, CON1 CON1.Close Unload Me Exit Sub BadOpen: 'handle the errors MsgBox "Opening the connection cause the following error: " & vbLf & CON1.Errors(0).Description
The code above is invoked when the user fills in the form and clicks the "Go" button. The program first puts together a string that will hold the sign-on information for the ODBC data source that you will use to communicate with your iSeries. Notice that the program uses the Value property of the various text boxes on the form to get their contents and to concatenate the contents into a string.
Once the string is prepared, the program uses On Error to set an error handler, and then attempts to open a connection to the iSeries using an ADO connection object. ADO lets you communicate with the iSeries using ODBC. To make sure your macro will run properly, ensure that you have referenced ADO by choosing the Tools and then References menu items and select Microsoft Active X Data Objects 2.x Library from the supplied list of objects ( where the x is any number from 0 to 7. I recommend that you use ADO 2.7. If ADO 2.7 is not listed in your references dialog, you can go to www.microsoft.com/data to get the latest version of the Microsoft Data Access Components.
If the connection is opened successfully, processing will continue to the next line of code, which tells the program to stop processing if it receives an error. The program then calls a procedure called RunProcDSPDBR and passes it the value of the txtLib text box, the value of the txtFN text box, and the connection object Con1. Once the procedure finishes processing, the program closes the CON1 connection and then unloads the form to return processing to the spreadsheet.
The RunProcDSPDBR Procedure
The following code needs to be added to the form. This is the RunProcDSPDBR procedure, which attempts to run the DSPDBR command for the supplied library and file over the supplied ADO connection. It also contains the MakeHeader subroutine, which draws the headers for the returned data in the spreadsheet. I won’t go into how this code works, because I covered it in my last article on running procedures. To place the code in the form, simply double-click the form to get into the forms code window, press CTRL+End to get to the end of the code in the code window, then paste this code into the window:
Public Sub RunProcDSPDBR(LIBRARY, TABLE, CON1 As ADODB.Connection) 'dim the objects we need to communicate Dim Cmd1 As New ADODB.Command Dim Rs As New ADODB.Recordset 'turn off the screen so excel is faster Application.ScreenUpdating = False On Error GoTo BadProblems Cmd1.ActiveConnection = CON1 Cmd1.CommandType = adCmdText Cmd1.CommandText = "Call SQLBOOK.CLDSPDBR (?,?)" Cmd1.Parameters.Append Cmd1.CreateParameter ("LIB", adChar, adParamInput, 10, LIBRARY) Cmd1.Parameters.Append Cmd1.CreateParameter ("FIL", adChar, adParamInput, 10, TABLE) Cmd1.Execute Dim Stmt As String Stmt = "" Stmt = Stmt & "SELECT WHREFI, WHRELI, DBXATR, DBXTXT " Stmt = Stmt & " FROM qtemp.mydspdbr INNER JOIN " Stmt = Stmt & " qsys.QADBXFIL ON" Stmt = Stmt & " (WHREFI = DBXFIL AND WHRELI = DBXLIB)" Stmt = Stmt & " ORDER BY 1" Cmd1.CommandText = Stmt Rs.CursorLocation = adUseClient Rs.CacheSize = 100 Rs.Open Cmd1 MakeHeaders LIBRARY, TABLE Range("A5").Activate R = 0 While Not Rs.EOF ActiveCell.Offset(R, 0).Font.Size = 10 ActiveCell.Offset(R, 0).Font.Bold = True ActiveCell.Offset(R, 0).Value = Rs.Fields("WHRELI").Value ActiveCell.Offset(R, 1).Value = Rs.Fields("WHREFI").Value ActiveCell.Offset(R, 2).Value = Rs.Fields("DBXATR").Value ActiveCell.Offset(R, 3).Value = Rs.Fields("DBXTXT").Value R = R + 1 Rs.MoveNext Wend Application.ScreenUpdating = True Worksheets("Sheet2").PageSetup.PrintArea = "A1:D" & R + 4 Range("A1").Activate Rs.Close Set Rs = Nothing Exit Sub BadProblems: 'you got here becasue of an error! Application.ScreenUpdating = True MsgBox "An error occurred!" End Sub Public Sub MakeHeaders(LIBRARY, TABLE) Worksheets("Sheet2").Activate Cells.ClearContents Cells.ClearFormats Range("A1").Activate Range("A1").ColumnWidth = 12 Range("B1").ColumnWidth = 12 Range("C1").ColumnWidth = 6 Range("D1").ColumnWidth = 52 Range("A1").Value = "Relations Listing" Range("A1").Font.Size = 12 Range("A1").Font.Bold = True Range("A1", "D1").MergeCells = True Range("A2").Value = LIBRARY & "/" & TABLE Range("A2").Font.Size = 12 Range("A2").Font.Bold = True Range("A2", "D2").MergeCells = True Range("A4").Activate R = 0 ActiveCell.Offset(R, 0).Font.Size = 10 ActiveCell.Offset(R, 0).Font.Bold = True ActiveCell.Offset(R, 0).Font.Underline = True ActiveCell.Offset(R, 0).Value = "Library" ActiveCell.Offset(R, 1).Font.Size = 10 ActiveCell.Offset(R, 1).Font.Bold = True ActiveCell.Offset(R, 1).Font.Underline = True ActiveCell.Offset(R, 1).Value = "File Name" ActiveCell.Offset(R, 2).Font.Size = 10 ActiveCell.Offset(R, 2).Font.Bold = True ActiveCell.Offset(R, 2).Font.Underline = True ActiveCell.Offset(R, 2).Value = "Type" ActiveCell.Offset(R, 3).Font.Size = 10 ActiveCell.Offset(R, 3).Font.Bold = True ActiveCell.Offset(R, 3).Font.Underline = True ActiveCell.Offset(R, 3).Value = "Description" End Sub
Adding the Procedure to Your iSeries
This program calls a CL program that invokes the DSPDBR command for the supplied library/file and writes the output to file QTEMP/MYDSPDBR. To create the procedure on your iSeries, create a source physical file, and input the following CL program. Then save the source and compile the program to an object named DSPDBRP in the SQLBOOK library.
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)
Next, start an SQL session, using my SQLThing tool or Client Access Express, and issue the following Create procedure statement to define the procedure on your iSeries:
CREATE PROCEDURE SQLBOOK.CLDSPDBR ( IN LIB CHARACTER(10), IN FIL CHARACTER(10)) RESULT SETS 0 EXTERNAL NAME SQLBOOK.DSPDBRP LANGUAGE CL PARAMETER STYLE GENERAL
Now that the procedure program has been created and the procedure declared to your iSeries, you are ready to attempt to run the macro. If you used a library other than SQLBOOK, you will need to change all of the references to the SQLBOOK library to point to the library where you created the procedure.
Adding a Button to Invoke the Form
To make the dialog box display, go back to Sheet 1 in the Excel spreadsheet and right-click your toolbar to get a list of toolbars. Make sure the Forms toolbar is checked, then select a button from the Forms toolbar and place it on Sheet 1. Right-click the button and choose Assign Macro, then assign the button to Macro Button1_Click and choose the Edit button to edit the Button1_Click macro. In the VB Editor, type usrInput.Show 1. This causes the usrInput form to be shown modally. Modal forms interrupt processing until they are unloaded or hidden.
To run the spreadsheet, exit design mode and click the button on Sheet 1. This should cause the dialog box to be displayed. Enter information about an ODBC data source, your user ID and password, and the library and file you wish to examine. Once you press the "GO!" button, the program will connect, will execute the sub routines, and will fill in the DSPDBR data into Sheet 2 of the spreadsheet.
If you do not want to mess with inputting the code, you can download a copy of this spreadsheet from the Resources area of my 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 email@example.com 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.|