Using the ADODB.Command Object

Using the ADODB.Command Object

The primary purpose of the Command object is to execute parameterized stored procedures, either in the form of the default temporary prepared statements or persistent, precompiled T-SQL statements in SQL Server databases. MSDE and SQL Server create temporary prepared statements that exist only for the lifetime of the current client connection. Precompiled SQL statements are procedures stored in the database file; their more common name is stored procedure. When creating Recordset objects from ad hoc SQL statements, the more efficient approach is to bypass the Command object and use the Recordset.Open method.

Command Properties

The Command object has relatively few properties, many of which duplicate those of the Connection object. Table 30.9 lists the names and descriptions of the Command object's properties. Like the Connection object, the Command object has its own provider-specific Properties collection, which you can print to the Immediate window using statements similar to those for Command objects described in the earlier "Provider-Specific Properties and Their Values" section.

Tip

The Command object is required to take advantage of ADO 2.6+'s Stream object, which contains data in the form of a continuous stream of binary data or text. Text streams often contain XML documents or document fragments returned from SQL Server 2000 XML AUTO queries. The Microsoft OLE DB Provider for Internet Publishing (MSDAIPP) enables Connection, Recordset, Record, and Stream objects to bind to a URL and retrieve data into a Stream object. Windows XP/2000+'s Internet Information Server (IIS) 5.0+ adds the MSDAIPP provider.


Table 30.9. Properties of the Command Object

Property Name

Description

ActiveConnection

A pointer to the Connection object associated with the Command. Use Set cmmName.ActiveConnection = cnnName for an existing open Connection. Alternatively, you can use a valid connection string to create a new connection without associating a named Connection object. The default value is Null.

graphics/2002_icon.gif

CommandStream

A Variant read/write value that contains the input stream used to specify the output stream.

CommandText

A String read/write value that specifies an SQL statement, table name, stored procedure name, or an arbitrary string acceptable to the provider of the ActiveConnection. The value of the CommandType property determines the format of the CommandText value. The default value is an empty string, "". CommandText and CommandStream are mutually exclusive. You can't specify a CommandStream and a CommandText value for the same Command object.

CommandTimeout

A Long read/write value that determines the time in seconds before terminating a Command.Execute call. This value overrides the Connection.CommandTimeout setting. The default value is 30 seconds.

CommandType

A Long read/write value that specifies how the data provider interprets the value of the CommandText property. (CommandType is the equivalent of the optional lngCommandType argument of the Connection.Execute method, described earlier in the chapter (refer to Table 30.7). The default value is adCmdUnknown.

graphics/2002_icon.gif

Dialect

A String read/write value that accepts one of four globally unique ID (GUID) values specifying the type of CommandStream object. Valid settings are DBGUID_DEFAULT (the provider decides how to handle the CommandStream value), DBGUID_SQL (an SQL statement), DBGUID_MSSQLXML (an SQL Server XML AUTO query), and DBGUID_XPATH (an SQL Server XPath query). The values of these constants are defined in the "Programming Stream Objects" section near the end of this chapter.

Name

A String read/write value specifying the name of the command, such as cmmNwind.

graphics/2002_icon.gif

NamedParameters

A Boolean read/write value that, when set to True, specifies that the names of members of the Parameters collection be used, rather than their sequence, when passing parameter values to and from SQL Server functions and stored procedures, or accepting return or output values from stored procedures.

Prepared

A Boolean read/write value that determines whether the data source compiles the CommandText SQL statement as a prepared statement (a temporary stored procedure). The prepared statement exists only for the lifetime of the Command object's ActiveConnection. Many client/server RDBMSs, including Microsoft SQL Server, support prepared statements. If the data source doesn't support prepared statements, setting Prepared to True results in a trappable error.

Properties

Same as the Properties collection of the Connection object.

State

A Long read/write value specifying the status of the Command. Refer to Table 30.4 for ObjectStateEnum constant values.

Tip

Always set the CommandType property to the appropriate adCmd... constant value. If you accept the default adCmdUnknown value, the data provider must test the value of CommandText to determine whether it is the name of a stored procedure, a table, or an SQL statement before executing the query. If the targeted database contains a large number of objects, testing the CommandText value for each Command object you execute can significantly reduce performance.

The initial execution of a prepared statement often is slower than for a conventional SQL query because some data sources must compile, rather than interpret, the statement. Thus, you should limit use of prepared statements to parameterized queries in which the query is executed multiple times with differing parameter values.


Parameters Collection

To supply and accept parameter values, the Command object uses the Parameters collection, which is similar to the DAO and ODBCDirect Parameters collections. ADODB.Parameters is independent of its parent, ADODB.Command, but you must associate the Parameters collection with a Command object before defining or using Parameter objects.

The Parameters collection has a read-only Long property, Count, an Item property that returns a Parameter object, and the methods listed in Table 30.10. The syntax for the Count and Item properties property is

 lngNumParms = cmmName.Parameters.Count prmParamName = cmmName.Parameters.Item(lngIndex) 

Table 30.10. Method Names, Descriptions, and Calling Syntax for the Parameters Collection

Method Name

Description and VBA Calling Syntax

Append

Appends a Parameter object created by the cmmName.CreateParameter method, described in the "Command Methods" section, to the collection. The calling syntax is Parameters.Append prmName.

Delete

Deletes a Parameter object from the collection. The calling syntax is cmmName.Parameters.Delete {strName|intIndex}, where strName is the name of the Parameter or intIndex is the 0-based ordinal position (index) of the Parameter in the collection.

Refresh

Retrieves the properties of the current set of parameters for the stored procedure or query specified as the value of the CommandText property. The calling syntax is cmmName.Parameters.Refresh. If you don't specify your own members of the Parameters collection with the CreateParameter method, accessing any member of the Parameters collection automatically calls the Refresh method. If you apply the Refresh method to a data source that doesn't support stored procedures, prepared statements, or parameterized queries, the Parameters collection is empty (cmmName.Parameters.Count = 0).

You gain a performance improvement for the initial execution of your stored procedure or query if you use the cmmName.CreateParameter method to predefine the required Parameter objects. The Refresh method makes a round-trip to the server to retrieve the properties of each Parameter.

Parameter Object

One Parameter object must exist in the Parameters collection for each parameter of the stored procedure, prepared statement, or parameterized query. Table 30.11 lists the property names and descriptions of the Parameter object. The syntax for getting and setting Parameter property values is

 typPropValue = cmmName.Parameters({strName|lngIndex}).PropertyName cmmName.Parameters({strName|lngIndex}).PropertyName = typPropValue 

You don't need to use the Index property of the Parameters collection; Index is the default property of Parameters.

Table 30.11. Property Names and Descriptions for Parameter Objects

Property Name

Description

Attributes

A Long read/write value representing the sum of the adParam... constants listed in Table 30.12.

Direction

A Long read/write value representing one of the adParam... constants listed in Table 30.13.

Name

A String read/write value containing the name of the Parameter object, such as prmStartDate. The name of the Parameter object need not (and usually does not) correspond to the name of the corresponding parameter variable of the stored procedure. After the Parameter is appended to the Parameters collection, the Name property value is read-only.

NumericScale

A Byte read/write value specifying the number of decimal places for numeric values.

Precision

A Byte read/write value specifying the total number of digits (including decimal digits) for numeric values.

Size

A Long read/write value specifying the maximum length of variable-length data types supplied as the Value property. You must set the Size property value before setting the Value property to variable-length data.

Type

A Long read/write value representing a valid OLE DB 2+ data type, the most common of which are listed in Table 30.14.

Value

The value of the parameter having a data type corresponding to the value of the Type property.

Table 30.12. Constant Values for the Attributes Property of the Parameter Object

ParameterAttributesEnum

Description

adParamSigned

The Parameter accepts signed values (default).

adParamNullable

The Parameter accepts Null values.

adParamLong

The Parameter accepts long binary data.

Table 30.13. Constant Values for the Direction Property of the Parameter Object

ParameterDirectionEnum

Description

adParamInput

Specifies an input parameter (default).

adParamOutput

Specifies an output parameter.

adParamInputOutput

Specifies an input/output parameter.

adParamReturnValue

Specifies the return value of a stored procedure.

adParamUnknown

The parameter direction is unknown.

The Type property has the largest collection of constants of any ADO enumeration; you can review the entire list of data types by selecting the DataTypeEnum class in Object Browser. Most of the data types aren't available to VBA programmers, so Table 30.14 shows only the most commonly used DataTypeEnum constants. In most cases, you only need to choose among adChar (for String values), adInteger (for Long values), and adCurrency (for Currency values). You use the adDate data type to pass Date/Time parameter values to Jet databases, but not to most stored procedures. Stored procedures generally accept datetime parameter values as the adChar data type, with a format, such as mm/dd/yyyy, acceptable to the RDBMS.

Table 30.14. Common Constant Values for the Type Property of the Parameter and Field Objects

DataTypeEnum

Description of Data Type

adBinary

Binary value.

adBoolean

Boolean value.

adChar

String value.

adCurrency

Currency values are fixed-point numbers with four decimal digits stored in an 8-byte, signed integer, which is scaled (divided) by 10,000.

adDate

Date values are stored as a Double value, the integer part being the number of days since December 30, 1899, and the decimal part being the fraction of a day.

adDecimal

Exact numeric value with a specified precision and scale.

adDouble

Double-precision floating-point value.

adInteger

4-byte signed Long integer.

adLongVarBinary

Long binary value (Parameter objects only).

adLongVarChar

String value greater than 225 characters (Parameter objects only).

adNumeric

Exact numeric value with a specified precision and scale.

adSingle

Single-precision floating-point value.

adSmallInt

2-byte signed Integer.

adTinyInt

Byte (1-byte signed integer).

adVarBinary

Binary value for Jet OLE Object and SQL Server image fields (Parameter objects only).

adVarChar

String value for Jet Memo and SQL Server text fields (Parameter objects only).

Note

graphics/globe.gif

The values for the Type property in the preceding table are valid for the Type property of the Field object, discussed later in the chapter, except for those data types in which "Parameter objects only" appears in the Description of Data Type column. The members of DataTypeEnum are designed to accommodate the widest possible range of desktop and client/server RDBMSs, but the ad... constant names are closely related to those for the field data types of Microsoft SQL Server 2000 and MSDE, which support Unicode strings.

For a complete list with descriptions of DataTypeEnum constants, go to http://msdn.microsoft.com/library/en-us/ado270/htm/mdcstdatatypeenum.asp.


The Parameter object has a single method, AppendChunk, which you use to append long text (adLongText) or long binary (adLongVarBinary) Variant data as a parameter value. The syntax of the AppendChunk method call is

 cmmName. Parameters({strName|lngIndex}).AppendChunk = varChunk 

The adParamLong flag of the prmName. Attributes property must be set to apply the AppendChunk method. If you call AppendChunk more than once on a single Parameter, the second and later calls append the current value of varChunk to the parameter value.

Command Methods

Command objects have only three methods: Cancel, CreateParameter and Execute. Executing Command.Cancel terminates an asynchronous command opened with the adAsyncConnect, adAsyncExecute, or adAsyncFetch option.

You must declare an ADODB.Parameter object, prmName, prior to executing CreateParameter. The syntax of the CreateParameter method call is

 Set prmName = cmmName.CreateParameter [strName[, lngType[, _    lngDirection[, lngSize[, varValue]]]]] cmmName. Parameters.Append prmName 

The arguments of CreateParameter are optional only if you subsequently set the required Parameter property values before executing the Command. For example, if you supply only the strName argument, you must set the remaining properties, as in the following example:

 Set prmName = cmmName. CreateParameter strName cmmName. Parameters.Append prmName With prmName    .Type = adChar    .Direction = adParamInput    .Size = Len(varValue)    .Value = varValue End With 

The syntax of the Command.Execute method is similar to that for the Connection.Execute method except for the argument list. The following syntax is for Command objects that return Recordset objects:

 Set rstName = cmmName.Execute([lngRowsAffected[, _    avarParameters[, lngOptions]]]) 

For Command objects that don't return rows, use this form:

 cmmName.Execute [lngRowsAffected[, avarParameters[, lngOptions]]] 

All the arguments of the Execute method are optional if you set the required Command property values before applying the Execute method. Listing 30.2 later in this chapter gives an example of the use of the Command.Execute method without arguments.

TIP

Presetting all property values of the Command object, rather than supplying argument values to the Execute method, makes your VBA code easier for others to comprehend.


Like the Connection.Execute method, the returned value of lngRowsAffected is 0 for SELECT and DDL queries and the number of rows modified by execution of INSERT, UPDATE, and DELETE queries. (For SQL Server, lngRowsAffected is 0 if the SQL statement includes SET NOCOUNT ON.) The avarParameters argument is an optional Variant array of parameter values. Using the Parameters collection is a better practice than using the avarParameters argument because output parameters don't return correct values to the array. For lngOptions constant values, refer to Table 30.7.

Code to Pass Parameter Values to a Stored Procedure

Most stored procedures that return Recordset objects require input parameters to supply values to WHERE clause criteria to limit the number of rows returned. The code of Listing 30.2 executes a simple SQL Server 2000 stored procedure with a Command object. The Sales by Year stored procedure of the NorthwindCS project has two datetime input parameters, @Beginning_Date and @Ending_Date, the values for which are supplied by strBegDate and strEndDate, respectively. The stored procedure, whose SQL statement follows, returns the ShippedDate and OrderID columns of the Orders table, the Subtotal column of the Order Subtotals view, and a calculated Year value. The stored procedure returns rows for values of the OrderDate field between strBegDate and strEndDate.

 ALTER PROCEDURE "Sales by Year"   @Beginning_Date datetime,   @Ending_Date datetime   AS SELECT Orders.ShippedDate, Orders.OrderID,      "Order Subtotals".Subtotal,      DATENAME(yy,ShippedDate) AS Year   FROM Orders INNER JOIN "Order Subtotals"      ON Orders.OrderID = "Order Subtotals".OrderID   WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date 
Listing 30.2 Code Using a Command Object to Execute a Parameterized Stored Procedure
 Option Explicit Option Compare Database Private cnnOrders As New ADODB.Connection Private cmmOrders As New ADODB.Command Private prmBegDate As New ADODB.Parameter Private prmEndDate As New ADODB.Parameter Private rstOrders As New ADODB.Recordset Private Sub Form_Load()    Dim strBegDate As String    Dim strEndDate As String    Dim strFile As String    strBegDate = "1/1/1997"    strEndDate = "12/31/1997"    strFile = CurrentProject.Path & "Orders.rst"    'Specify the OLE DB provider and open the connection    With cnnOrders       .Provider = "SQLOLEDB.1"       On Error Resume Next       .Open "Data Source=(local);" & _        "UID=sa;PWD=;Initial Catalog=NorthwindCS"       If Err.Number Then        .Open "Data Source=(local);" & _          "Integrated Security=SSPI;Initial Catalog=NorthwindCS"       End if       On Error GoTo 0    End With    With cmmOrders       'Create and append the BeginningDate parameter       Set prmBegDate = .CreateParameter("BegDate", adChar, _          adParamInput, Len(strBegDate), strBegDate)       .Parameters.Append prmBegDate       'Create and append the endingDate parameter       Set prmEndDate = .CreateParameter("EndDate", adChar, _          adParamInput, Len(strEndDate), strEndDate)       .Parameters.Append prmEndDate       Set .ActiveConnection = cnnOrders       'Specify a stored procedure       .CommandType = adCmdStoredProc       'Brackets must surround stored procedure names with spaces       .CommandText = "[Sales By Year]"       'Receive the Recordset       Set rstOrders = .Execute 'returns a "firehose" Recordset    End With    With rstOrders       'Save (persist) the forward-only Recordset to a file       On Error Resume Next       'Delete the file, if it exists       Kill strFile       On Error GoTo 0       .Save strFile       .Close       .Open strFile, "Provider=MSPersist", , , adCmdFile    End With    'Assign rstOrders to the Recordset of the form    Set Me.Recordset = rstOrders    Me.txtShippedDate.ControlSource = "ShippedDate"    Me.txtOrderID.ControlSource = "OrderID"    Me.txtSubtotal.ControlSource = "Subtotal"    Me.txtYear.ControlSource = "Year" End Sub 

Caution

When used in ADO code, you must enclose names of stored procedures and views having spaces with square brackets. Including spaces in database object names, especially in client/server environments, isn't a recommended practice. Microsoft developers insist on adding spaces in names of views and stored procedures, perhaps because SQL Server 2000 supports this dubious feature. Use underscores to make object names more readable if necessary.


Note

The code of Listing 30.2 uses an ADO 2.5+ feature, persisted (saved) Recordset objects. Stored procedures return forward-only ("firehose") Recordset objects, which you can't assign to the Recordset property of a form. To create a Recordset with a cursor acceptable to Access forms, you must persist the Recordset as a file and then close and reopen the Recordset with the MSPersist OLE DB provider as the ActiveConnection property value. The "Recordset Methods" section, later in the chapter, provides the complete syntax for the Save and Open methods of the Recordset object.


graphics/power_tools.gif

Figure 30.18 shows the result of executing the code of Listing 30.2. The frmParams form that contains the code is included in the ADOTest.mdb and ADOTest.adp files described earlier in the chapter. The AddOrders.adp project, described in the "Exploring the AddOrders.adp Sample Project" section near the end of the chapter, also includes code for setting stored procedure parameter values.

Figure 30.18. This Datasheet view of the read-only Recordset returned by the Sales By Year stored procedure displays the value of each order received in 1997.

graphics/30fig18.gif



Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net