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 ex-

ample, 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 specifi-

cally, 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

*/

Supports multiple result sets

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