CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
By David R. K. DeLoveh
This article shows how to use the refresh method of the ADO Parameters collection to interrogate a stored procedure. You may already have experience using ADOX or SQL-DMO to accomplish this, but please keep in mind that the method displayed here is meant to be simple enough that anyone with ADO experience can use it. (ADOX and SQL-DMO will be addressed later.)
During my second year of development in Microsoft technologies, I constantly wished I’d known something earlier. I understood the basics but was not knowledgeable enough to draw conclusions about what could be done with them. I would see articles written for intermediate to advanced developers about writing code to generate code, and I was baffled by the specifics. Feeling discouraged, I worked through my mind-numbing tasks the same way every day. The task I remember doing the most is writing ADO interfaces to stored procedures. This process put me to sleep. The following is a simple method to keep you from falling asleep writing ADO interface code. In this article, I will show how to create the code in an automated fashion for use in VBScript connecting to MS SQL Server 7.
The refresh method is part of the command object’s parameter collection. You may already be using it to make calling stored procedures easier. For example:
Dim objCmd
Dim lngRetVal, strOutputParam
Set objCmd = Server.CreateObject(“ADODB.Command”)
ObjCmd.ActiveConnection = “Provider=”
ObjCmd.CommandType = 4 ‘Stored Procedures
ObjCmd.CommandText = “sp_TestGenerator”
ObjCmd.Parameters.Refresh
ObjCmd.Parameters(1).Value = “Text”
ObjCmd.Parameters(2).Value = 9
ObjCmd.Parameters(3).Value = Null
ObjCmd.Execute()
LngRetVal = ObjCmd.Parameters(0).Value
StrOutputParam = ObjCmd.Parameters(3).Value
Set objCmd = Nothing
In this example, when you call the refresh method, ADO goes to the SQL Server and gets the information about the parameters. The advantage is that the proper values for the ADO size, direction, and precision, among others, are loaded for all the parameters of the command. You can now assign values by ordinal position and execute the command. The drawback to using the refresh method when calling a stored procedure is that two trips are made to the SQL Server each time you want to call the stored procedure. The first is when the refresh method is called. The second trip occurs when you execute the command. The information retrieved by the refresh method is enough to generate code to call the stored procedure without the refresh method.
Step 1
‘Open the connection on the command. ‘Set the command type to stored procedure. ‘Set the command text to the procedure name. ‘Call refresh to retrieve the values.
We first need to prompt the user to provide a small amount of information. In the following form, the user will enter a connection string, a procedure name, and decide if the code should handle the return of a recordset.
When the form is submitted, the data is saved to variables m_strConnectionString, m_strProcName, and m_blnRecordset. With this information we start the process.
‘Declare and create the command object.
Dim cmd
Set cmd = Server.CreateObject(“ADODB.Command”)
cmd.ActiveConnection = m_strConnectionString
cmd.CommandType = 4
cmd.CommandText = m_strProcName
cmd.Parameters.Refresh
At this point, all of the information about the parameters is loaded into the parameters collection. Next we will display our results.
Step 2
Dim blnTR1
Dim param
for each param in cmd.Parameters
If blnTR1 Then
Response.Write “<TR style=””background-color:silver;””>”
Else
Response.Write “<TR>”
End If
blnTR1 = NOT blnTR1
Response.Write “” &
“<TD align=””left””> ” & param.Name & ” </TD>” & _
“<TD align=””center””> ” & GetParameterDirectionEnum(param.Direction) & _
” (” & param.Direction & “) </TD>” & _
“<TD align=””center””> ” & GetDataTypeEnum(param.Type) & _
” (” & param.Type & “) </TD>” & _
“<TD align=””center””> ” & param.Precision & ” </TD>” & _
“<TD align=””center””> ” & param.Size & ” </TD>” & _
“<TD align=””center””> ” & param.Value & ” </TD>” & _
“</TR>”
next
We use the “For each .. In .. Next” construct to iterate through each parameter in the collection. From this we retrieve values for Name, Direction, Type, Precision, Size, and Value. For the Direction and Type, it helps to have a little more information. Since I didn’t write the code spec on ADO, I can’t look at the number 135 and know that its ADO data type is adDBTimeStamp. (Yes, even after writing these by hand countless times, I could be just blocking out bad memories.) Therefore, I feel it’s necessary to translate the data types and directions into the ADO enumerations. To produce the enumerations values, we use the GetParameterDirectionEnum and GetDataTypeEnum functions declared later in our code. The results of our work are displayed in a table.
Step 3
blnFirstParameter = True ‘Add a parameter for the returned recordset if requested. We will now iterate through the parameters again. We start by chopping the “@” sign off the parameter name if it exists. The parameter name is then assigned to strTempParamVarName. This value is used as the parameter variable for the function. If Left(param.Name,1) = “@” Then
Now that we have the knowledge about the procedure, we need to convert it into power. After writing ADO interfaces for a while, we all develop a style. Some people create a separate command and connection object, while others will use just a command object and assign the connection string to it. Regardless of your style, we can automate a good portion of it. In my example, we will create a VBScript function that will call the stored procedure. The following code executes after the refresh method was called for the tabled output above.
Declare variables to be used:
Dim blnFirstParameter ‘Is this the first parameter
Dim strDeclaration ‘Function declaration
Dim strCommandParameters ‘Parameters for the command
Dim strOutputParameters ‘Retrieving of output parameters
Dim strPrecisionParameters ‘Setting of precision for Decimal and Numeric
Dim strTempParamVarName ‘The variable name for the parameter
‘Setup the function declaration.
‘Default the Function name to the proc name
strDeclaration = “Function ” & m_strProcName & “(”
If m_blnRecordset = True Then
strDeclaration = strDeclaration & “rst”
blnFirstParameter = False
End If
strTempParamVarName = Mid(param.Name,2)
Else
strTempParamVarName = param.name
End If
Step 4
If NOT param.Direction = 4 Then If NOT blnFirstParameter = True Then strDeclaration = strDeclaration & strTempParamVarName If param.Direction = 3 Then ‘Create code to attach each parameter. The SQL Server decimal(14) land numeric(131) data types must be handled differently. The create parameter call does not include the precision value, so we create code to reference the parameter by name and set the precision values explicitly. With all of the parameters processed, we exit the loop and start writing out the function. This part is very straightforward and only varies if a recordset must be returned. Response.Write strDeclaration & vbCrLf & vbCrLf Response.Write “RETURN_VALUE = Null” & vbCrLf If m_blnRecordset = True Then Response.Write “cmd.ActiveConnection = “”” & m_strConnectionString & “””” & vbCrLf Response.Write strCommandParameters Response.Write vbCrLf & strPrecisionParameters & vbCrLf & vbCrLf If m_blnRecordset = True Then Response.Write strOutputParameters Response.Write “Set cmd = Nothing” & vbCrLf & vbCrLf
Next, we process everything except the return parameter. If this isn’t the first parameter, add a comma to separate it from the previous one. If this is an output parameter (SQL Server types the declaration as adInputOuput (3)), then add the code for retrieving the values after the command is executed.
strDeclaration = strDeclaration & “, ”
Else
blnFirstParameter = False
End If
strOutputParameters = strOutputParameters & strTempParamVarName & _
” = cmd.Parameters(“”” & param.name & “””).Value” & vbCrLf
End If
End If
strCommandParameters = strCommandParameters & _
“cmd.Parameters.Append cmd.CreateParameter(“”” & param.Name _
& “””, ” & param.Type & “, ” & param.Direction & _
“, ” & param.Size & “, ” & strTempParamVarName & “)” & vbCrLf
If param.type = 14 or param.type = 131 Then
strPrecisionParameters = strPrecisionParameters & “cmd.Parameters(“”” & _
param.name & “””).Precision = ” & param.Precision & vbCrLf
End If
strDeclaration = strDeclaration & “)”
Response.Write “Dim cmd ‘Command Object” & vbCrLf
Response.Write “Dim RETURN_VALUE ‘Return Value” & vbCrLf
Response.Write vbCrLf
Response.Write “Set cmd = Server.CreateObject(“”ADODB.Command””)” & vbCrLf
Response.Write “Set rst = Server.CreateObject(“”ADODB.Recordset””)” & vbCrLf
End If
Response.Write “cmd.CommandType = 4 ‘Stored Procedure” & vbCrLf
Response.Write “cmd.CommandText = “”” & m_strProcName & “””” & vbCrLf & vbCrLf
Response.Write “rst.CursorLocation = 3 ‘adUseClient” & vbCrLf
Response.Write “rst.Open cmd, , 3, 1 ‘adOpenStatic, adLockReadOnly” & _
vbCrLf & vbCrLf
Response.Write “Set rst.ActiveConnection = Nothing” & vbCrLf
Else
Response.Write “cmd.Execute” & vbCrLf & vbCrLf
End If
Response.Write m_strProcName & ” = cmd.Parameters(“”RETURN_VALUE””).Value” & _
vbCrLf & vbCrLf
Step 5
Dim cmd ‘Command Object RETURN_VALUE = Null cmd.Parameters.Append cmd.CreateParameter(“RETURN_VALUE”, 3, 4, 0, RETURN_VALUE) cmd.Parameters(“@decParam4”).Precision = 18 cmd.Execute strParam2 = cmd.Parameters(“@strParam2”).Value Set cmd = Nothing End Function If the user selected to return a recordset, code to declare, create, open, close, and destroy it will be added. The recordset’s CursorLocation property is set to adUseClient(3). If you run the statement without it, the return and output parameters will appear unchanged by the stored procedure. If you don’t return a recordset and use only a command object and execute it, the output and return parameters will be returned correctly.
Once all the information is written, you will have a text area displaying your results. Here is the output for the z_testInterrogation stored procedure without returning a recordset.
Function z_testInterrogation(strParam1, strParam2, intParam3, decParam4, datParam5)
Dim RETURN_VALUE ‘Return Value
Set cmd = Server.CreateObject(“ADODB.Command”)
cmd.ActiveConnection = “DSN=LocalServer;UID=SA;PWD=”
cmd.CommandType = 4 ‘Stored Procedure
cmd.CommandText = “z_testInterrogation”
cmd.Parameters.Append cmd.CreateParameter(“@strParam1”, 200, 1, 14, strParam1)
cmd.Parameters.Append cmd.CreateParameter(“@strParam2”, 200, 3, 18, strParam2)
cmd.Parameters.Append cmd.CreateParameter(“@intParam3”, 3, 1, 0, intParam3)
cmd.Parameters.Append cmd.CreateParameter(“@decParam4”, 131, 1, 0, decParam4)
cmd.Parameters.Append cmd.CreateParameter(“@datParam5”, 135, 1, 0, datParam5)
z_testInterrogation = cmd.Parameters(“RETURN_VALUE”).Value
All of the code we just examined is very simple. It only accomplishes slightly more than the basics of writing the code to call stored procedures, however, the value we add to our toolbox by understanding it is immense. When we are new to writing ADO, we are likely to make a lot of mistakes. We may go even as far as making design mistakes that are repeated in every call. With the basis displayed here, we could modify the process to repeat for all of the procedures we need to fix, thereby correcting the flaws in only a few minutes. When we are experienced developers, the burden of fixing a project gone wrong is often assigned to us. Perhaps we were only assigned to code review a project we had nothing to do with. In these cases, we have to know the technologies inside and out. If we find the data access to be flawed, we could get the project back on track by giving the development team a tool based on the technique I described. Then the time saved can be used to solve the business rules of the application, or even go home on time! When I used it, I found it to be helpful to create both business and data-layer functions.
The example displayed above is totally void of error handling. I tend to like to know as much as possible when a problem occurs. To accomplish this, I find myself writing complex error-handling routines. Now, I just write it once in my code generator and have it reproduced everywhere.
It should be noted that the binary data types for SQL Server 7 were not discussed here. I feel that, in the current context, they would only clutter the example because they are handled far differently. Many good articles are written for working with binary data in SQL Server 7, so I don’t want to just be repeating it.
ADOX and SQL-DMO
As promised, let’s revisit the topics of ADOX and SQL-DMO. ADOX is the name for the new library included with ADO 2.1 The library is formally called “ActiveX Data Objects Extensions for DDL and Security,” or ADOX. You may think you can do more with ADOX or SQL-DMO, and you’re right. But think about what we are trying to accomplish here. Using either of these would be like using a flame thrower to destroy a small anthill. The flame thrower is a lot of fun and allows you to destroy it in a more controlled manner. You can fry it piece by piece, or destroy it in one shot, but beware of starting a forest fire! When you think about it, the flame thrower is overkill for something that can be accomplished by just crushing it with your shoe. If you have never used a flame thrower, you’re probably going to need some training and a little practice before you can get the job done.
Our problem is easily solved with tools we already know, recordset and command objects. I am in no way bashing ADOX or SQL-DMO. I am quite the fan of ADOX, though the lack of implementation by the current drivers is disappointing. Both are very powerful and flexible tools to learn and add to your development tool belt. They could be used here to make this example more user friendly by retrieving a list of stored procedures for the user to choose from. Please do not infer from this article that ADOX and SQL-DMO do exactly the same thing. They have some overlap, but each has its specific uses. See the end of this article for links to more information about them.
Conclusion
So let’s recap what we should be doggie-bagging from this article. First, the refresh method, which is costly to use every time you want to call a stored procedure, is great for getting all the information you need to build a basic ADO interface generator. Second, watch out for the ADO pitfalls discussed here. Decimal and numeric data types must be supplied a precision value, and opening a recordset on a command that has output parameters without using client-side cursors will cause the output parameters not to show updates. Finally, if you find that you will need more power and control in your generator, look to ADOX and SQL-DMO.
Download complete file below.
Additional References
Refresh Method
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthadorefresh.asp
ADO and ADOX
http://msdn.microsoft.com/library/en-us/ado270/htm/admscadoapireference.asp
About the Author
David R. K. DeLoveh is a Tech Lead for AdOutlet, which provides solutions to the media industry. He is also part owner of VisiNex Solutions, Inc. VisiNex works to provide quality Web development to small- and medium-sized businesses in the central Ohio area. David specializes in development of Web-based applications built on Microsoft technologies. He can be reached at david@deloveh.com.
