The LA Fox Developer Newsletter

July 2000

ADO Jumpstart (Cony from page 7)

Select *;

From Customer;

Where country = ? And maxorder_amt>?

As with views, either local or remote, in Visual FoxPro, so too can queries be parameterized in ADO. In ADO, the question mark acts as a placeholder for parameters. The following example illustrates how to put this all together.

First, a connection and a Command object need to be created:

oConnection = CreateObJect(”adodb.connectlon”) oCommand CreateObject(”adodb.command”)

Next, the connection needs to be established:

oConnectlon.Open(”northwlnd” ,“sa”,”,

For illustration purposes, the OLE OB Provider for ODBC is used. The native OLE DB Provider for SQL Server could have been used as well.

Next, the Command object needs to be prepared:

With oCommand

.Actlveconnection = oconnection

.CommandText = “Select From Customer Where country = EndWith

With the Command object ready to go, a parameter object needs to be created:

oCountryParameter =

ocommand.CreateParameter(”country”,adChar,adParamlnput,l

"))

The arguments for the CreateParameter method are as follows:

Name—The name of the parameter

Type—The data type of the parameter. A list of valid values is contained in DataTypeEnum

Direction-The direction of the parameter. Parameters sent to a command are input parameters. Arguments passed back from a command are output parameters. A list of valid values is contained in ParameterDirectionEnum

Size-The length of the parameter

Value—The initial value of the parameter

Alternatively, the parameter could have been created like this:

OCountryParameter = CreateObject(”adodb.parameter’ With oCountryParameter

.Name = “Country” .Type = adChar .Direction = adParamlnput .SIze = 1

.Value = " " EndWith

Once the parameter has been created, it needs to be appended into the Parameters collection of the Command object:

oCommand.Parameters.Append(oCountryParameter)

With the parameter in place, the value of the parameter can be set. In this case, the parameter will be set so that any country that begins with the letter U will be returned into a Recordset object:

With oCountryParameter

.SIze = 2

.Value =

Endwith

Now, a Recordset object can be created:

oRecordset = oCommand.Execute

A useful feature of specifying parameters is that this enforces characteristics such as size, data type, and so on. For example, the preceding parameter was defined as a character. If a value based on a different data type was assigned to the Value property of the Parameter object, an error would result. The same is true if the assigned value is greater in length than what has been specified by the Size property.

Finally, if a list of customers in Mexico were required, the following code would complete the task:

With oCommand .Parameters(”country”).Size = Len(”Mexico”) .Parameters(”country”).Vaiue = “Mexico” oRecordSet = .Execute

EndWith

Properties Collection

Recall the earlier assertion that, by itself, ADO is Incapable of doing anything? ADO in fact just provides an interface. OLE DB providers give ADO the ability to do anything. So then, what distinguishes one OLE DB provider from another? More specifically, how can you determine what an OLE DB provider can and cannot do, or what attributes it does or does not possess? Depending on the OLE DB provider you use, or the type of recordset you use (client or server), what is supported will likely differ.

The Properties collection applies to the Connection, Recordset, and Field objects. The Command object also has a Properties collection, which is identical to the Recordset object Properties collection.

Multiple result sets provide a good example of varying OLE DB provider support. To determine if multiple result sets can be obtained, you can refer to the “Multiple Results” properties:

If oConnection.Properties(”Multiple Results”).Value = I

Endif

While the OLE DB providers for SQL Server and ODBC both support multiple results, the OLE DB provider for Jet does not. To illustrate, the following is valid syntax for SQL Server:

oRecordset.Source=”SELECT FROM customers;" +" SELECT * FROM orders”

(Con’t, page 9)

Page 8