The Command Object

(Page 1 of 8 )

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:

---

---

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: 

• 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:

 Set objParam = objCmd.CreateParameter("ID", 
adInteger, _
                         adParamInput, 8, _   
                         123 )

and:

  Set objParam = objCmd.CreateParameter
  objParam.Name = "ID"
  objParam.Type = adInteger
  objParam.Direction = adParamInput
  objParam.Size = 8
  objParam.Value = 123

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.

  [Set Recordset = ]Command.Execute([RecordsAffected], 
                                       [Parameters],
                                       [Options])

---

Parameter	Type	Description
RecordsAffected	Long	A Long variable into which the provider returns the
		number of records that the operation affected
Parameters	Variant	An array of parameter values passed to the statement
		specified in the Commands CommandText property
		(Output parameters dont return correct values if
		passed here.)
Options	Long	A value that indicates how the provider should
		interpret the CommandText or CommandStream
		properties of the Command object (default value is -1)

---

You can use RecordsAffected to determine how many records were affected by the command executed. For example:

  objCmd.CommandText = "UPDATE titles " & _
                        "SET royalty = royalty * 1.10"
  objCmd.Execute lngRecs
  Print "Number of records affected by command: "
  Print lngRecs

Parameter values passed in the Parameters argument will override any values in a Commands Parameters collection. For example:

  objCmd.Parameters.Refresh
  objCmd.Parameters("@FirstParam") = "abc"
  objCmd.Execute , Array("def")

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

---

Next: Properties of the Command Object >>
 

More Database Articles
More By Apress Publishing

Comments On: The Command Object

>>> Be the FIRST to comment on this article!

Buy this book...

This article is excerpted from ADO Programmer's Reference by David Sussman (Apress, 2004; ISBN: 1590593421). Check it out at your favorite bookstore. Buy this book now.