If you work with ADO and make frequent use of the Connection object, you might find that the Command object actually lets you accomplish more, especially when store procedures are being used. This article is taken from chapter 4 of ADO Programmer's Reference, written by Dave Sussman (Apress, 2004; ISBN 1590593421)
Although the Connection object allows the execution of commands (such as SQL statements or stored procedures) against a data store, the Command object has greater functionality and flexibility, especially when stored procedures are being used. It's not actually a requirement that data providers support the Command object, although all the major providers do.
You might be required to pass parameters into stored procedures or stored queries and, as you'll see in this chapter, there's more than one way to do this. One method involves the use of the Parameters collection, which allows complete control over the individual parameters passed to a procedure or query. It's for this reason that the Parameters collection is covered in this chapter (rather than with the other collections in Chapter 8).
When using stored procedures that have output parameters, you must use a command object. The command object's Parameters collection allows the passing of parameters into and out of stored procedures, as well as allowing a recordset to be passed back to the application.
Methods of the Command Object
The Cancel Method
This method cancels execution of a pending asynchronous Execute method call.
Command.Cancel
The Cancel method is particularly useful when allowing users to submit their own asynchronous queries, because these can often have long execution times, and you may wish to provide them with a Cancel button on the screen.
The CreateParameter Method
This method creates and returns a reference to a new Parameter object.
Set Parameter = Command.CreateParameter([Name], [Type], [Direction], [Size], [Value])
The method's arguments are all optional:
Parameter
Type
Description
Name
String
Name of the parameter
Type
DataTypeEnum (Long)
Data type of the parameter (default value is
adEmpty)
Direction
ParameterDirectionEnum
Direction of the parameter (default value
(Long)
is adParamInput)
Size
Long
Maximum length of the parameter value in
characters or bytes
Value
Variant
Value for the parameter
The ParameterDirectionEnum constants list is as follows:
adParamUnknown indicates that the direction of the parameter is unknown.
adParamInput
The
ParameterDirectionEnum constants list is as follows:
adParamUnknown
indicates that the direction of the parameter is unknown.
adParamInput
indicates that the parameter is an input parameter to the command.
adParamOutput indicates that the parameter is an output parameter from the command.
adParamInputOutput indicates that the parameter is both an input to and an output from the command.
adParamReturnValue indicates that the parameter is a return value from the command.
The DataTypeEnum list is quite large, and is included in Appendix B.
Use CreateParameter to create the parameters that are to be passed to stored procedures and stored queries. You can use CreateParameter two ways: the first is with all of its arguments, and the second is without any arguments. For example, the following two sets of code are logically equivalent ways of assigning specific values to a Parameter objects properties:
Note that both code examples do not add the parameter to the Parameters collection of the Command object concerned. For this, you must call the Append method of the Command object, and pass a reference to the Parameter object that is to be added to the Parameters collection. For example:
objCmd.Parameters.Append objParam
Multiple parameter objects can be appended to the Parameters collection of a Command object. However, if you attempt to append a Parameter object that does not have a specific value assigned to at least one of its properties, an error will be generated. Ill discuss the Parameter object itself later in this chapter.
If the NamedParameters property is True, the names of the Parameter objects in the Parameters collection are used to match the parameters to the parameters in the underlying command. If the NamedParameters property is set to False (the default), then the parameters in the Parameters collection are matched to those in the stored procedure or query by the order in which they're listed. In particular, this means that the name you assign to a Parameter in the collection (via the CreateParameter method or the Name property) need not be the same as the corresponding parameter name within the procedure or query. Its the order of parameters that's important, not the names, although its best to use similar (or the same) names both in the collection and in the procedure or query.
The Execute Method
This method executes the query, SQL statement, or stored procedure specified in the CommandText property, or the command specified in the CommandStream property.
This command will use def as the value for the @FirstParam parameter, instead of the value abc supplied in the Parameters collection.
Options can be one of the following CommandTypeEnum constants:
adCmdText indicates that the command text is to be interpreted as a text command, such as a SQL statement.
adCmdTable indicates that the command text is to be interpreted as the name of a table.
adCmdTableDirect indicates that the command text is to be interpreted directly as a table name. This allows ADO to switch some internal options to provide more efficient processing. (See the section on the CommandType property.)
adCmdStoredProc indicates that the command text should be interpreted as the name of a stored procedure or stored query.
adCmdUnknown indicates that the nature of the command text is unknown.
Additionally, you can add one or more of the ExecuteOptionEnum constants:
adAsyncExecute indicates that the command should be executed asynchronously.
adAsyncFetch indicates that after the initial batch of rows is fetched, remaining rows are fetched asynchronously.
adAsyncFetchNonBlocking indicates that asynchronous fetching is used. However, if the requested row has not yet been fetched, the last row fetched is supplied instead.
adExecuteNoRecords indicates that the command does not return any records. Any returned rows are discarded.
adExecuteRecord indicates that the result of the command is a single row and should be returned as a Record object.
adExecuteStream indicates that the results of the command should be returned as a Stream object.
For asynchronous operations you can add adAsyncExecute to the Options argument to make the command execute asynchronously, and adAsyncFetch or adAsyncFetchNonBlocking to force the recordset to be returned asynchronously:
objCmd.Execute , , adCmdTable OR adAsyncExecute _ OR adAsyncFetch
Using streams as command input and output was a new feature of ADO 2.6. The difference is that you use the CommandStream property to specify the stream containing the command to be executed. You can also use the dynamic property Output Stream to specify a stream into which the commands output should be placed. For example:
' construct the SQL query sSQL = "<ROOT xmlns:sql=" & _ "'urn:schemasmicrosoft.com:xmlsql'>" & _ "<sql:query>SELECT * FROM authors FOR XML AUTO" & _ "</sql:query>" & _ "</ROOT>" ' place the query in the stream stmQuery.WriteText sSQL, adWriteChar stmQuery.Position = 0 objCmd.CommandStream = stmQuery objCmd.Dialect = "{5D531CB2E6Ed11D2B25200C04F681B71}" objCmd.Properties("Output Stream") = stmOutput objCmd.Execute
At this stage the stream stmOutput contains the command's results. In ASP you could use the Response object here:
objCmd.Properties("Output Stream") = Response
This would stream the command output directly to the browser. If using the XPATH Dialect, you can also set the following dynamic properties:
Description Mapping Schema Base Path Dynamic Property XML schema that maps XML elements and attributes to tables and columns Directory containing the mapping schema
This property indicates the Connection object to which the Command object currently belongs.
Set Connection = Command.ActiveConnection Set Command.ActiveConnection = Connection Command.ActiveConnection = String String = Command.ActiveConnection
This can be a valid Connection object or a connection string, and must be set before the Execute method is called; otherwise, an error will occur.
There is a subtle difference between using an existing Connection object and a connection string when setting the ActiveConnection. For example, consider the following code:
These code fragments both appear to do the same thing; however, the first uses an existing Connection object, whereas the second will implicitly create a new connection. Connection pooling, however, may reuse an existing connection if it can speed up the connection in this second case.
In Visual Basic, if you set the value of ActiveConnection to Nothing, the Command object is disassociated from the connection but remains active. However, note what happens to the parameters. If the provider supplied the parameters (i.e., via the Refresh method of the Parameters collection), the Parameters collection will be cleared. On the other hand, if the parameters were created and appended manually, they are left intact. This could be useful if you have several commands with the same parameters.
The CommandStream Property
This property, new in ADO 2.6, identifies the Stream object containing the command details.
The CommandStream can be any valid Stream or an object that supports the IStream interface. For example, in an ASP page the input stream (Request) might contain the command details. Because the ASP Request object supports the standard COM IStream interface in ASP version 3.0, the following code allows us to assign the contents of the Request object to the CommandStream property:
The dynamic property Output Stream allows a command's output to be placed into a stream. For example, the preceding code could be modified to output the results of the command to the Response object:
This can be a SQL statement, a table name, a stored procedure name, or a provider-specific command, and the default is an empty string. For example, to set the command text to a SQL string, you can do the following:
objCmd.CommandText = "SELECT * FROM authors " & _ "WHERE state = 'CA'"
You can pass parameters into a stored procedure in SQL Server a number of ways. One way is to use the parameters contained in the Parameters collection. Another way is to pass the stored procedure and its arguments as a text command:
This method is sometimes quicker to code than using the Parameters collection, although you obviously have no way to use output parameters with this method. Alternatively, you could use the Parameters argument of the Execute method:
If you set the Command object's Prepared property to True, then the command will be compiled and stored by the provider (if it supports prepared statements) before executing. This prepared statement is retained by the provider for the duration of the connection. If you are using the SQL Server provider, this may create a temporary stored procedure for you (if you have the Use Procedure for Prepare dynamic property in the Connection objects Properties collection, as discussed in Appendix C). This is particularly useful when the same command must be executed several times but with different parameters.
The Parameters collection is explained in more detail later in this chapter.
The CommandStream and CommandText properties are mutually exclusive; setting one clears the other.
The CommandTimeout Property
This property indicates how long, in seconds, to wait while executing a command before terminating the command and generating an error. The default is 30 seconds.
Long = Command.CommandTimeout Command.CommandTimeout = Long
An error will be generated if the timeout value is reached before the command completes execution, and the command will be cancelled. If you are using Visual Basic and ADO events, the ExecuteComplete event be fires when the error is generated, and you can check the pError argument to detect the error. You can also trap the error and examine the error details.
This property bears no relation to the Connection object's CommandTimeout property; it is not inherited from the connection.
The CommandType Property
This property indicates the type of the Command object.
The CommandTypeEnum constants are defined under the Execute method and in Appendix B.
You should use this property to optimize the processing of the command, because it informs the provider what sort of command you will execute before that command is actually performed. This allows the provider to decide what to do in advance; this often increases performance because ADO doesnt have to figure out the type of command.
The distinction between adCmdTable and adCmdTableDirect is quite subtle. Consider the following:
This property identifies the dialect to be used for the CommandStream or CommandText properties.
String = Command.Dialect Command.Dialect = String
The Dialect property is a Globally Unique Identifier (GUID) and is provider-dependent, allowing the provider to support multiple dialects. The default dialect is standard SQL, identified by the following GUID:
{C8B521FB5CF311CEADE500AA0044773D}
For the SQL XML format supported by SQL Server 2000, you would use the following GUID:
{5D531CB2E6Ed11D2B25200C04F681B71}
For XPATH support, you should use:
{EC2A4293E89811D2B1B700C04F680C56}
Consult your provider documentation to find out any provider-specific dialects.
More details about XML interaction can be found in the ADO help files.
The Name Property
This property indicates the name of the Command object.
String = Command.Name Command.Name = String
In many cases you probably won't use this property, but it could be used to uniquely identify a Command object in a collection of commands. For example, imagine an application that allows users to build up a number of commands. You could store these in a collection and use the commands' Name property to identify them. Think about the query window in SQL Server; you could build quite a good emulation of this by using ADO to connect to multiple data sources and using a collection to store various command objects.
Commands as Connection Methods
There is a rarely noted feature that goes like this: if you name a Command object, then associate the command with a Connection object, the Connection object inherits a method corresponding to the name of the Command. This is a dynamic method, which doesn't show up as part of the object's methods or properties within code editors such as Visual Basic.
Let's consider an example. Suppose you have two types of user, a Clerk and a Manager, and both types need to look at employee details. Managers are allowed to see the salary details, but the humble clerks are not. You have decided to use stored procedures to encapsulate all your SQL logic, so you have two possibilities. The first is to build a single SQL statement to return the employee details, and have it check whether the user is a manager or a clerk. Your user then calls a business object that fetches the data, as shown in this diagram of three-tier architecture with the logic in the server:
Note that you now have a business rule built into the SQL. In many cases this is not a problem (and indeed could be deemed good practice), but this doesn't always fit everyones needs. You may find that you'd rather not put business rules into your SQL code. For instance, in this example, you also must work out details such as how to establish which group the user belongs to. That's not a particularly complex problem, but here's an alternative situation that keeps all the business logic in the same place-the business object-as this diagram shows:
In this situation, the application first calls a business object, perhaps when it starts, and the business object establishes the role: clerk or manager. This sets the appropriate stored procedure and attaches the command as a method of the connection. Later, the application can simply call the business object that calls this new method. Because the method is one of two stored procedures, the correct stored procedure is run. Although this seems like more code, it actually simplifies some programming. It puts the business logic in a component where it is easily reused, and it also makes the stored procedures simpler; these will now run faster because there is no run-time decision to make.
When using this method, you must set the Name property before setting the ActiveConnection; otherwise, an error occurs.
One thing that's not documented very clearly-or very often-is that when using this method, the newly named procedure accepts the parameters of the stored procedure, and an extra argument: a recordset. For example, imagine the following stored procedure:
CREATE PROCEDURE usp_TestProcOne @sAuLName varchar(40) AS SELECT * FROM authors WHERE au_lname = @sAuLName
If ADO doesn't recognize the command, it passes the command and parameters to the provider to see if it can handle it. If the provider recognizes the command, the command is executed; otherwise, an error is generated. Note that this, of course, will result in poorer performance.
One particularly interesting feature of named commands is that a variation of it allows you to retrieve output parameters of a stored procedure, even when the provider doesnt support it. This is covered in more detail in the "Retrieving Output Parameters" section at the end of the chapter.
The NamedParameters Property
This property indicates whether parameter names should be passed to the provider.
Setting this property to True means that ADO will pass the name of each parameter to the provider, where the name will be used to match up the parameters. If the value is False (the default), parameters are interpreted in the order created. It is more efficient to add parameters by position because, when adding by name, ADO has to match the parameters to their underlying counterparts.
The Prepared Property
This property indicates whether or not to save a compiled version of a command before execution.
The default value of the property is False. You should set it to True when the command is to be repeated several times. Although the compilation process will be slower during the first command execution, subsequent executions will be quicker because the command text does not have to be parsed and the execution plan can be reused.
If youre using the SQL Server provider, and you set the Use Procedure for Prepare dynamic property of the Connection object to True, a temporary stored procedure will be created for prepared commands. If you find that you need to prepare a lot of statements, consider moving the SQL into a stored procedure, because this will be more efficient than preparing statements.
Use Procedure for Prepare is one of the provider-specific dynamic properties of the ADO Connection object. For more on ADO's dynamic properties and how to use them, see Appendix C.
If you are creating prepared commands against SQL Server and not disconnecting, you should ensure that the temporary database (tempdb) has enough space to accommodate the stored procedures.
This property describes whether the Command object is open, closed, or currently processing a command.
Command.State = ObjectStateEnum
State can be one of the following ObjectStateEnum constants:
adStateClosed indicates that the command is closed.
adStateOpen indicates that the command is open.
adStateExecuting indicates that the command is executing.
adStateFetching indicates that the command is fetching records.
adStateConnecting indicates that the command is connecting to the provider.
Using the State property allows you to detect the current state of the command. For example, to detect if the Command is still executing an asynchronous query, you could use this line:
If objCmd.State = adStateExecuting Then Print "Command is still executing" End If
Collections of the Command Object
The Parameters Collection
This collection contains all the Parameter objects for a Command object.
Parameters = Command.Parameters
The Parameters collection is most often used when passing arguments to stored procedures or queries. Parameter objects are appended to this collection by use of the Append method:
You can iterate through the objects in this collection in Visual Basic or VBScript by using the For Each...Next command. For example:
For Each objParam In objCmd.Parameters Print objParam.Name Next
If your provider is capable of describing the parameters of a stored procedure or query, then you can use the Refresh method to have the provider fill in the Parameters collection for you:
objCmd.Parameters.Refresh
The Parameters collection is discussed more fully in "The Parameters Collection" section later in this chapter.
Other collections, and their methods and properties, are covered in more detail in Chapter 8.
The Properties Collection
This collection contains all of the provider-specific, dynamic Property objects for a Command object:
Properties = Command.Properties
The Parameter Object
The Parameter object comprises all of the information for a single parameter to be used in a stored procedure or query, and is really used only in conjunction with the Command object and its associated Parameters collection.
Methods of the Parameter Object
This section details the methods of the Parameter object.
The AppendChunk Method
This method appends data to a large or binary Parameter object.
Parameter.AppendChunk Data
Parameter
Type
Description
Data
Variant
Data to be appended to the parameter
The first AppendChunk call after editing the parameter details writes data to the parameter, overwriting any existing data. Subsequent calls add to existing data. Although the documentation states that a parameter must support long data for this method to work, this doesn't appear to be compulsory for SQL Server. You can check the Attributes property (see the following section) to see if it contains adParamLong, which will indicate if it supports long data.
This method is most often used when storing images in tables. A full discussion of using images with AppendChunk appears at the end of Chapter 8.
The Parameter objects AppendChunk method is functionally equivalent to the AppendChunk method of the Field object, and it works the same way. The only difference is that you are dealing with a different base object.
Properties of the Parameter Object
This section details the properties of the Parameter object.
The Attributes Property
This property indicates one or more characteristics of a Parameter object.
This can be one or more of these ParameterAttributesEnum values:
adParamSigned indicates that the parameter will accept signed values. This is the default.
adParamNullable indicates that the parameter will accept null values.
adParamLong indicates that the parameter accepts long data, such as binary data.
You can combine these values by using a logical OR statement:
objParam.Attributes = adParamNullable OR adParamLong
Setting adParamLong doesn't appear to be compulsory for binary data. A SQL Server stored procedure with a parameter of type image will accept long data without this set.
The Direction Property
This property indicates whether the Parameter object represents an input parameter, an output parameter, or an input/output parameter, or if the parameter is a return value from a stored procedure.
ParameterDirectionEnum can be one of the following values:
adParamUnknown indicates that the direction of the parameter is not known.
adParamInput indicates that the parameter is to pass information to the stored procedure or query.
adParamOutput indicates that the parameter is to receive information from the stored procedure or query.
adParamInputOutput indicates that the parameter can be used to pass information to, and return information from, a stored procedure or query.
adParamReturnValue indicates that the parameter will contain the return value of the stored procedure or query.
In a SQL Server stored procedure, you would declare an output parameter by appending OUTPUT to the parameter declaration. For example, the following SQL code creates a stored procedure with input and output parameters and return values:
CREATE PROCEDURE usp_GetValues @PubID char(4), indicates an input parameter @Value integer OUTPUT indicates an output parameter AS BEGIN . . . . RETURN 123 indicates the return value
The return value is always the first parameter (index position zero) in the Parameters collection and is named RETURN_VALUE. If you're creating parameters, you must create the return value first:
The return value should be declared as a long integer. If you use Refresh, then a return value parameter is always created, irrespective of whether the stored procedure returns a value with the RETURN statement.
You can also set this property by using the Direction argument of the Command object's CreateParameter method.
Microsoft Access does not support output parameters or return values.
The Name Property
This property indicates the Parameter object's name.
String = Parameter.Name Parameter.Name = String
This just identifies the parameter name within the Parameters collection, and doesn't have to be the same as the parameters name as defined in the stored procedure or query--but it makes sense to keep these the same. This makes the code more readable and easier to maintain.
You can also set this property by using the Name argument of the Command object's CreateParameter method.
Once the Parameter object has been appended to the Parameters collection, this property becomes read-only, because its name becomes its key in the Parameters collection.
The numeric scale indicates how many digits are to the right of the decimal point.
Both the NumericScale and Precision properties are required to process SQL data of numeric type correctly. Both Oracle and SQL Server 7.0 support numeric types, using both native OLE DB Providers and the OLE DB Provider for ODBC.
Furthermore, note that the NumericScale and Precision properties cannot be set via the Command object's CreateParameter method. If you need to set these properties (for example, in the scenario described in the previous paragraph), you must create an explicit Parameter object and set its properties before appending it to the Parameters collection.
The Precision Property
This property indicates the degree of precision for numeric values in the Parameter object.
The precision indicates the maximum number of digits used to represent a numeric value.
The Precision property, like the NumericScale property, is required for the correct processing of SQL data of numeric type-as supported by both Oracle and SQL Server 7.0, using both native OLE DB Providers and the OLE DB Provider for ODBC. There's more about this in the previous section on the NumericScale property.
The Size Property
This property indicates the maximum size, in bytes or characters, of a Parameter object.
Long = Parameter.Size Parameter.Size = Long
Watch out for a few things when using the Size property:
For variable-length parameters (such as character strings or binary data), you must always set the Size so that the provider knows how much space to allocate for the parameter. If you don't specify the size, an error is generated.
If you use the Parameters collection's Refresh method to force the provider to fill in the parameter details, the Size for variable-length parameters may be at their maximum potential size, and memory may be allocated for these parameters accordingly.
For binary data you have to be quite specific about the size of the parameter. For example, if a SQL Server stored procedure has a parameter of type image, and you Refresh the parameters, the size of this parameter is returned as 2147483647. If you create the parameters yourself and use this size, and then use AppendChunk to add the data to the parameter, there are no problems, but creating the parameter with the actual size of the binary data doesn't work. That's because Size is the maximum size of the parameter, not its actual size.
The Type Property
This property indicates the data type of the Parameter object.
A full list of the DataTypeEnum values is shown in Appendix B, but the following table lists the data types used by the SQL Server and Jet providers and shows how the underlying data store's data types map to those of ADO. The empty table cells indicate that there is no direct mapping between the two database types.
There are a few interesting (and often confusing) things to note. For example, date parameters in SQL Server don't map to the obvious adDate data type, but rather to the adDBTimeStamp data type. Also, the timestamp maps to adVarBinary for SQL Server 6.5 and adBinary for SQL Server 7.0. You should also note that adInteger maps to Visual Basic's Long data type; it doesn't fit VB's Integer type. If the parameters are of the wrong type, they can cause your commands to fail.
If you wish to create your parameters by using the CreateParameter method, but you're having trouble matching data types or sizes, then the simplest fix is to temporarily call the Refresh method and examine the Parameters collection. You can do this in VBScript by looping through the collection or by using the Locals window in Visual Basic. You can then copy the values that ADO has used and amend your code accordingly. Don't forget to remove the Refresh once you've sorted out your parameters, because leaving it in will cause a performance penalty.
You can find more on data types in Appendix E.
The Value Property
This property indicates the value assigned to the Parameter object.
This is the default property and can be omitted if desired.
The value of a parameter can be read only once, and the recordset should be closed before you read the value (depending upon the Output Parameter Availability dynamic property of the Connection). Reading a value more than once returns an empty value. For more details on this, see the "Retrieving Output Parameters" section on parameter values at the end of this chapter.
For a parameter holding binary data, you can use the Value property to set its value instead of using the AppendChunk method. For example:
objRec.Parameters("@Logo").Value = varChunk
A full description of using binary data appears at the end of Chapter 8.
When dealing with stored procedures or queries that accept arguments, you have three options:
Use the Command object's Execute method, passing in the parameters as an array of values into the second argument of this method. Not all providers support this.
Use the Command object's Execute method, passing the parameters in the Command's Parameters collection.
Use the Command object's Execute method, passing the parameter values as part of the command text. In many cases the first is acceptable (and is in fact easier to code), but the one restriction of this is that you can't return any values from your stored procedure. For example, suppose you have a stored procedure like this:
CREATE PROCEDURE usp_AuthorsByState @RequiredState char(2), @ID integer OUTPUT AS BEGIN SELECT * FROM authors WHERE state = @RequiredState SELECT @ID=123 RETURN 456 END
This procedure does three things:
It returns a recordset using one of the parameters to restrict the rows that are returned.
It sets the output parameter to an arbitrary value (123 in this case).
It returns another arbitrary value (456 in this case).
Although the output value and return value are falsely constructed, it illustrates two common techniques for returning information from a stored procedure.
If you wished to call this stored procedure without using the Parameters collection, you could do so using code like this:
This simply calls the procedure, passing in two arguments: CA to filter the recordset and lngID (a long integer) to be used as the output parameter. This works fine, because ADO builds a Parameters collection from the values you supply. However, note that the output parameter (lngID) is never populated with the output value, and there's also no way to access the return value. So although this method is extremely easy to use, it doesnt give you the functionality that the Parameters collection does.
There's a caveat to this approach: If you reference the Parameters collection before calling the Execute method, the Parameters collection is then Refreshed automatically for you. For example:
During the Print statement, ADO automatically calls a Refresh and populates the Parameters collection. The key point is that there are three parameters, not two: the return value, the input parameter (the required state), and the output parameter (the ID value). This means that the next line might fail, because it doesn't use enough parameters:
Set objRec = objCmd.Execute (, Array("CA", lngID))
However, this line works:
Set objRec = objCmd.Execute (, Array(lngRV, "CA", lngID))
This shows that if you manually populate the Parameters collection or use the Array method, you don't need to worry about the return parameter. However, if you Refresh the Parameters collection (or if it is refreshed implicitly for you), you'll find that the return value becomes the first parameter in the collection.
Note that not all providers or versions of SQL Server may support this automatic refresh (or indeed the Refresh method itself). If using SQL Server 6.5, ensure that you have installed the latest Catalog Procedures. See the SQL Books online for more details on this.
Differences from ADO 2.0
The automatic refreshing of parameters in ADO 2.1 onward is different from what occurred in ADO 2.0. Using an explicit Refresh works for both the OLE DB Provider for ODBC and the OLE DB Provider for SQL in both versions of ADO. However, using the implicit refresh gives different results.
Ive used the following code to test the automatic refresh facility in ADO 2.0 and ADO 2.1 onward:
If you are using SQL Server 6.5 (and you haven't updated your Catalogs), only the OLE DB Provider for ODBC will return the correct parameter information to the Parameters collection. If you have updated your catalogs, or if you're using SQL Server 7, then there are differences between the ways that the two versions of ADO handle the parameters. The following list shows which combinations of ADO, providers, and data stores support automatic refreshing of parameters:
ADO Version
Provider
SQL Server
Supported
2.0
OLE DB for ODBC
6.5
Yes
7.0
Yes
OLE DB for SQL
6.5
Yes
7.0
No
2.1 onward
OLE DB for ODBC
6.5
Yes
7.0
No
2000
No
OLE DB for SQL
6.5
No
7.0
No
2000
No
Don't think that this removal of functionality is a detrimental step. In this case it is a deliberate (and documented) move to improve performance and reduce network traffic.
Parameter Types in ASP
When using the array method in ASP scripting languages, you must make sure that any variables used for output parameters have the correct data type. When you declare variables in scripting languages, they are variants, and this may not match the correct parameter type. The output parameter must have the correct type, even though you can't actually use it. For example, in VBScript:
Dim lngID Set objRec = objCmd.Execute (, Array(lngRV, "CA", lngID))
This produces an error, which indicates that a parameter object has not been supplied. That's because lngID is a variant and doesn't have a default type or value. However, this section of code works:
Dim lngID lngID = Set objRec = objCmd.Execute (, Array(lngRV, "CA", lngID))
This forces the variant to hold the correct data type; it gives lngID a sub-type of Integer, which is compatible with the integer type declared in the stored procedure (SQL Server integers map to Longs in Visual Basic and in scripting languages).
The Parameters collection contains a Parameter object for each parameter in a stored procedure, including the return value if the procedure has one. It has only two properties and three methods.
Although more cumbersome to use than the Refresh method to build a parameter list, this method is much more efficient, because it minimizes the total number of calls made to the provider.
You can create a parameter with just one line by using the following:
Set objParam = objCmd.CreateParameter("State",adChar,adParamInput,2,"CA")
You can combine the Append and CreateParameter methods into a single statement:
This method deletes a Parameter object from the Parameters collection.
Parameters.Delete(Index)
Description Index Variant Parameter Type Number or name of the parameter object to be removed from the collection
You must use the Parameter object's index or its Name when deleting a parameter. You cannot use an object variable. For example, we can delete the parameter called ID by using this line:
objCmd.Parameters.Delete ("ID")
Here's another example. This line deletes the first parameter in the collection:
objCmd.Parameters.Delete (0)
The Refresh Method
This method updates the Parameter objects in the Parameters collection.
Parameters.Refresh
This has the impact of querying the data provider for details of the parameters, such as the name, size, direction, and so on. You can then access the parameters by name or ordinal number by indexing into the collection. For example:
You should use this method only when willing to accept the performance hit of the provider requerying the data source for the parameter details. This is especially important if the same set of parameters is used frequently.
You can use this method quite effectively by querying the provider for the parameters once-for example, at the start of the program. You could then copy the parameter details into a global variable (perhaps an array or a collection, or even a local, fabricated recordset), where it can be used many times during the program. The advantage with this method is that you don't have to keep repeating CreateParameter calls, and you can create a generic piece of code to run a command. In addition, you can change the stored procedure without having to change the code that runs the command. The disadvantage is, of course, the delay as the program starts while the parameters are read in. However, you may consider this delay worthwhile, because it gives you a good chance to display that fancy splash screen you've always wanted!
If you access the Parameters collection (for example, setting the value of a parameter) without having created your own Parameters for a command, ADO will call Refresh automatically to fill the collection. Not all providers support the Refresh method.
The performance issues regarding Refresh are discussed in Chapter 14.
Properties of the Parameters Collection
The Count Property
This property indicates the number of Parameter objects in the Parameters collection.
Long = Parameters.Count
You can use the Visual Basic or VBScript For Each...Next command to iterate through the parameters collection without using the Count property. For languages that do not support enumeration of collections, you can use this property in a loop.
The Item Property
This property allows indexing into the Parameters collection to reference a specific Parameter object.
Parameter = Parameters.Item(Index)
Name Description Index Variant Type Number or name of the parameter within the collection
This is the default property and can be omitted. For example, the following lines are identical:
The ability to return information from stored procedures in parameters is extremely useful, but you should be aware of some points. The first is whether output parameters are supported at all by the provider-Access doesn't support them. The second is at what stage the output parameters are available, and for this there are two choices:
The parameter is available as soon as the command has been executed.
The parameter is available only after the recordset has been closed.
This latter option is the one you must watch for, because in that case, ADO will read the parameter values from the provider only once. This means that if you read the parameter before closing the recordset, then close it, and then try to read the parameter value again, the value may not be available.
You can check to see which of the modes your provider supports by examining the Output Parameter Availability dynamic property for the connection. This will return one of the three DBPROPVAL_OA constant values:
DBPROPVAL_OA_ATEXECUTE indicates that the parameters are available after the command has been executed. This has a value of 2.
DBPROPVAL_OA_ATROWRELEASE indicates that the parameters are available after the recordset has been closed. This has a value of 4, and is the option supported by SQL Server.
DBPROPVAL_OA_NOTSUPPORTED indicates that output parameters are not supported. This has a value of 1.
You could check these values with code like this:
Set objCmd.ActiveConnection = objConn objCmd.CommandText = "usp_ProcedureWithOutputParam" objCmd.CommandType = adCmdStoredProc objCmd.Parameters.Append objCmd.CreateParameter("RETURN_VALUE", _ adInteger, adParamReturnValue) Set objRec = objCmd.Execute intParamAvail = objConn.Properties("Output Parameter Availability") If intParamAvail = DBPROPVAL_OA_ATROWRELEASE Then ' parameter not available until recordset is closed objRec.Close End If intRV = objCmd.Parameters("ReturnValue")
This isnt a problem with commands that do not return recordsets, because there is no recordset to close.
There are two solutions to the problem of parameters not being available until the recordset is closed. The first is simply a matter of using client-side cursors, which allows the parameters to be immediately available.
The second method involves the naming of stored procedures by using the Command object's Name property. You've already seen that code similar to this is allowed:
This was never well documented, but what's even less well documented is that you can pass the arguments to the stored procedure and a recordset object like so:
The arguments are accepted by the stored procedure in the normal way, and the recordset object is filled accordingly. As soon as this command is executed, you can access the return and output parameters even though the recordset is still open.
As a word of warning, you shouldn't really rely on this procedure; because it's not documented, there's no way of telling whether this will continue to work in future versions of ADO.