The Command Object - Properties of the Command Object
(Page 2 of 8 )
The ActiveConnection Property
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:
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\ADO\ADOTest.mdb"
Set objCmd.ActiveConnection = objConn
objCmd.ActiveConnection = "Provider=" & _
"Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\ADO\ADOTest.mdb
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.
Variant = Command.CommandStream
Command.CommandStream = Variant
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:
<%
Set cmdC = Server.CreateObject("ADODB.Command")
Set objCmd.CommandStream = Request
objCmd.ActiveConnection = ". . ."
objCmd.Dialect = ". . ."
objCmd.Execute , , adExecuteStream
%>
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:
<%
Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.CommandStream = Request
objCmd.ActiveConnection = ". . ."
objCmd.Dialect = ". . ."
objCmd.Properties("Output Stream") = Response
objCmd.Execute , , adExecuteStream
%>
The CommandStream and CommandText properties are mutually exclusive; setting one clears the other.
The CommandText Property This property contains the text of a command to be issued against a data provider.
String = Command.CommandText
Command.CommandText = String
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:
objCmd.CommandText = "usp_MyProcedure ('abc', 123)"
objCmd.CommandType = adCmdStoredProc
Set objRs = objCmd.Execute()
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:
objCmd.CommandText = "usp_MyProcedure"
objCmd.CommandType = adCmdStoredProc
Set objRs = objCmd.Execute (, Array("abc", 123))
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.
CommandTypeEnum = Command.CommandType
Command.CommandType = CommandTypeEnum
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:
objCmd.CommandText = "authors"
objCmd.CommandType = adCmdTable
This actually sends SELECT*FROM authors to the provider. In contrast, consider this code:
objCmd.CommandText = "authors"
objCmd.CommandType = adCmdTableDirect
This only sends the statement authors to the provider. Some providers may not support direct table names and may require explicit SQL statements.
The performance implications of these options are discussed in more detail in Chapter 14.
Next: The Dialect Property >>
More Database Articles
More By Apress Publishing
|
 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.
|
|
