Passing Output Parameters instead of Rowsets
Suppose there was a technique that did not require the construction of a Recordset, but still let you pass back arguments that ADO could manage for you? Well, if you submit a query that returns OUTPUT parameters instead of rowsets (as generated by SELECT statements), you don't need to have ADO construct a Recordset at all. However, you will need to write a stored procedure to generate the OUTPUT parameters, and an ADO Command object to manage them for you.
The drawback here is that this approach doesn't really fit when there is more than one qualifying row. That is, if the query returns a set of results, you have to map the results into a single set of OUTPUT parameters in the stored procedure—either that or fall back on a rowset—and then construct a Recordset or a user-defined or Variant array to handle the rowset.
If you can make do with a single row, as with a specific key query, keep reading. The code examples in the following two sections show how to construct an ADO Command object that accepts both input and output parameters and that can be passed back to another tier, just as a Recordset was in the previous example of passing strings and string arrays.
The Server-side Code
On the server tier, we provide two routines: one to fill the client-side pick-list of ISBNs, and another to execute a query based on the chosen ISBN.
In the first case, we construct a firehose cursorless result set and pass it back to the client where its rows are used to fill a common ComboBox control.
Private cn As Connection Dim rs As Recordset Public Function GetISBNs() As Recordset OpenConnection Set rs = New Recordset rs.CursorLocation = adUseClient rs.Open "select top 50 ISBN from Titles", cn, adOpenStatic, _ adLockReadOnly, adCmdText Set rs.ActiveConnection = Nothing Set GetISBNs = rs cn.Close End Function
The second routine constructs an ADO Command object to execute a stored procedure that takes a single input parameter and returns two output parameters. We construct the Command.Parameters collection manually (with some difficulty) to improve runtime performance (not developer performance in this case). This code is tied to the stability of the stored procedure—if the stored procedure changes arguments (either in number, position, or datatype), then the code is broken.
After the query is executed, the Command object is passed back to the client tier. Edward (my technical editor) threw a hissy fit when I used this technique, and I tend to agree with him. Passing Command objects between tiers is ugly. Yes, you can do it—it just isn't particularly efficient, and Edward won't help you when you call developer support. The following code uses adExecuteNoRecords to prevent the construction of a Recordset object. Once passed, the local Command object is discarded—it has to be constructed each time, unless we can maintain state on the server (which is not likely if we expect to scale this component).
Public Function GetRecs(ISBN As String) As Command Dim cmd As Command Dim RecordsAffected As Integer OpenConnection ' Our own routine to open the connection Set cmd = New Command With cmd .CommandText = "FetchTitleByISBN" .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter("ISBNWanted", adVarChar, adParamInput, 20) .Parameters.Append .CreateParameter("Title", adVarChar, adParamOutput, 255) .Parameters.Append _ .CreateParameter("Year_Published", adSmallInt, adParamOutput, 2) .ActiveConnection = cn
.Execute RecordsAffected, ISBN, adExecuteNoRecords End With cn.Close Set GetRecs = cmd End Function
A second variation on the preceding approach is to pass back a user-defined type that defines two variables to return the title and year published. The following version has considerably less overhead than passing back the entire Command object:
Public Function GetRecs(ISBN As String) As udtParms Dim cmd As Command Dim RecordsAffected As Integer Dim outParms As udtParms OpenConnection Set cmd = New Command With cmd .CommandText = "FetchTitleByISBN" .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter("ISBNWanted", adVarChar, adParamInput, 20) .Parameters.Append .CreateParameter("Title", adVarChar, adParamOutput, 255) .Parameters.Append _ .CreateParameter("Year_Published", adSmallInt, adParamOutput, 2) .ActiveConnection = cn .Execute RecordsAffected, ISBN, adExecuteNoRecords cn.Close outParms.Title = .Parameters(1) ' title outParms.YearPublished = .Parameters(2) 'Year published End With GetRecs = outParms End Function
The Client-side Code
On the client, we have two very simple routines. The first fills the pick-list ComboBox. This control eliminates the errors generated when users type in ISBN numbers. In this case, we call GetISBNs, a server-side component that returns a firehose cursor (FO/RO, CacheSize=1) with ISBNs from the first 50 rows of the Titles table. These are used to select valid ISBNs from the Titles table.
Private Sub cmbISBNs_GotFocus() Dim rsLocal As Recordset If cmbISBNs.ListCount = 0 Then Set rsLocal = GetISBNs Do Until rsLocal.EOF cmbISBNs.AddItem rsLocal(0) ' ISBN rsLocal.MoveNext Loop cmbISBNs.ListIndex = 0 End If End Sub
The second routine calls the GetRecs server-side component, passing in the chosen ISBN. In this example, shown next, we pass back the entire Command object and pick off the two output parameters in the client code. Why didn't we just pass back the two arguments? It would make more sense in this case.…
Private Sub cmbISBNs_Click() Dim cmdLocal As Command Set cmdLocal = GetRecs(cmbISBNs.Text) txtTitle = cmdLocal.Parameters(0) ' Title txtYearPublished = cmdLocal.Parameters(1) ' Year Published End Sub
However, it seems to me that the preceding approach is also less efficient than simply passing back the two values as a user-defined type. The following variation of the example shows how that's done using a commonly defined Type structure (udtParms):
Private Sub cmbISBNs_Click() Dim inParms As udtParms inParms = GetRecs(cmbISBNs.Text) txtTitle = inParms.Title txtYearPublished = inParms.YearPublished End Sub
Team-Fly |
EAN: 2147483647
Pages: 106