Interrogating a Stored Procedure with ADO

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

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")
  
'Open the connection on the command.
  cmd.ActiveConnection = m_strConnectionString

'Set the command type to stored procedure.
  cmd.CommandType = 4	  

'Set the command text to the procedure name.
  cmd.CommandText = m_strProcName
  
'Call refresh to retrieve the values.
  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

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 & "("

blnFirstParameter = True	
	
'Add a parameter for the returned recordset if requested.
If m_blnRecordset = True Then
strDeclaration = strDeclaration & "rst"
	blnFirstParameter = False
End If	

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
strTempParamVarName = Mid(param.Name,2)
Else
	strTempParamVarName = param.name
End If

Step 4

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.



If NOT param.Direction = 4 Then		
		
If NOT blnFirstParameter = True Then 
	strDeclaration = strDeclaration & ", "
Else
	blnFirstParameter = False
End If
		
strDeclaration = strDeclaration & strTempParamVarName
			
If param.Direction = 3 Then 
	strOutputParameters =  strOutputParameters & strTempParamVarName & _
		" = cmd.Parameters(""" & param.name & """).Value" & vbCrLf
End If
End If


'Create code to attach each parameter.
strCommandParameters = strCommandParameters & _
	"cmd.Parameters.Append cmd.CreateParameter(""" & param.Name _
	& """, " & param.Type & ", " & param.Direction & _
	", " & param.Size & ", " & strTempParamVarName & ")" & vbCrLf


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.

If param.type = 14 or param.type = 131 Then
strPrecisionParameters = strPrecisionParameters & "cmd.Parameters(""" & _
	param.name & """).Precision = " & param.Precision & vbCrLf
End If

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.

strDeclaration = strDeclaration & ")"

Response.Write strDeclaration & vbCrLf  & vbCrLf
Response.Write "Dim cmd		'Command Object" & vbCrLf
Response.Write "Dim RETURN_VALUE		'Return Value" & vbCrLf
Response.Write vbCrLf	
	
Response.Write "RETURN_VALUE = Null" & vbCrLf
Response.Write "Set cmd = Server.CreateObject(""ADODB.Command"")" & vbCrLf
	
If m_blnRecordset = True Then
Response.Write "Set rst = Server.CreateObject(""ADODB.Recordset"")" & vbCrLf
End If	
	
Response.Write "cmd.ActiveConnection = """ & m_strConnectionString & """" & vbCrLf
Response.Write "cmd.CommandType = 4		'Stored Procedure" & vbCrLf
Response.Write "cmd.CommandText = """ & m_strProcName & """" & vbCrLf & vbCrLf

Response.Write strCommandParameters
	
Response.Write vbCrLf & strPrecisionParameters & vbCrLf & vbCrLf
	
If m_blnRecordset = True Then
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 strOutputParameters
Response.Write m_strProcName & " = cmd.Parameters(""RETURN_VALUE"").Value" & _
	vbCrLf & vbCrLf
	
Response.Write "Set cmd = Nothing" & vbCrLf & vbCrLf


Step 5

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 cmd		'Command Object
Dim RETURN_VALUE		'Return Value

RETURN_VALUE = Null
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("RETURN_VALUE", 3, 4, 0, RETURN_VALUE)
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)

cmd.Parameters("@decParam4").Precision = 18

cmd.Execute

strParam2 = cmd.Parameters("@strParam2").Value
z_testInterrogation = cmd.Parameters("RETURN_VALUE").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.

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.



Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: October 29, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Are you interested in building a cognitive application using the power of IBM Watson? Need a platform that provides speed and ease for rapidly deploying this application? Join Chris Madison, Watson Solution Architect, as he walks through the process of building a Watson powered application on IBM Bluemix. Chris will talk about the new Watson Services just released on IBM bluemix, but more importantly he will do a step by step cognitive …

  • QA teams don't have time to test everything yet they can't afford to ship buggy code. Learn how Coverity can help organizations shrink their testing cycles and reduce regression risk by focusing their manual and automated testing based on the impact of change.

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds