ADO Jumpstart for Microsoft Visual FoxPro Developers

Article
06/30/2006

John V. Petersen

April 1999

Summary: Provides Microsoft Visual FoxPro developers with an overview of ActiveX Data Objects (ADO) and shows how to incorporate ADO into Visual FoxPro applications. Discusses the ADO object model and implementing Remote Data Services (RDS). (52 printed pages)

Introduction What are OLE DB and ADO? Why Incorporate ADO into a Visual FoxPro Application? ADO Object Model Remote Data Services Summary

Introduction

Microsoft®ActiveX® Data Objects (ADO) is perhaps the most exciting new Microsoft technology in quite some time. Because ADO is concerned with data, this new technology is of particular interest to Microsoft® Visual FoxPro® developers. Of course, you may ask, "Why do I need ADO? Visual FoxPro already has a high-performance local data engine." It's a good question.

This paper provides the Visual FoxPro developer with a background of what ADO is and how to incorporate ADO into Visual FoxPro applications. After reading this paper, you should have enough information to readily answer the question: "Why do I need ADO?"

A Brief Word About ADO Events

One limitation of Visual FoxPro has been an inability to surface COM events. While Visual FoxPro can respond to events raised by ActiveX controls, objects created with the CreateObject function cannot. In Microsoft®Visual Basic®, COM Events are handled by using the WithEvents keyword. In Visual FoxPro, the new VFPCOM.DLL achieves the same results. The topics VFPCOM, ADO Events, and how to integrate ADO and Visual FoxPro will be discussed in another white paper. This paper is dedicated to providing the Visual FoxPro developer, with a comprehensive overview of ActiveX Data Objects, Remote Data Services (RDS), their respective objects, and how those objects work.

This paper covers the following topics:

What are ADO and OLE DB?
Why incorporate ADO into a Visual FoxPro application?
The ADO object model
Remote Data Services

What Are OLE DB and ADO?

When discussing ADO, we are really talking about two distinct elements: the ActiveX data objects themselves and Microsoft Universal Data Access technology, more commonly known as OLE DB.

OLE DB and Universal Data Access

In simple terms, OLE DB is the succeeding technology to the Open Database Connectivity (ODBC) standard. OLE DB is a set of low-level interfaces that facilitate the Microsoft Universal Data Access strategy. ADO is a set of high-level interfaces for working with data.

While both ODBC and OLE DB have the ability to make data available to a client, the capabilities of the two technologies are very different. ODBC is primarily designed for use on relational data. However, data exists in nonrelational as well as relational formats. In addition to new data formats, data resides in new places such as the Internet. Finally, the Microsoft Component Object Model (COM) framework requires better data access technology. Clearly, ODBC does not address these needs; a new technology is needed. That technology is OLE DB, and it is here to stay.

The following graphic best illustrates how OLE DB and ADO work together. Clients can work directly with OLE DB or can work with OLE DB through the ADO interface (the latter is typically the case). Note that OLE DB can access SQL data either directly or through ODBC. An OLE DB provider provides direct access by OLE DB. Also note that OLE DB can also be used to access a variety of non-SQL data, as well as data that exists in mainframes. The ability to access data through a common interface, without regard to data location or structure, is the real power behind ADO and OLE DB.

Whereas ODBC uses drivers, OLE DB uses providers. A provider is a software engine that provides a specific type of data that matches the OLE DB specification. Several OLE DB providers exist today, including those for Microsoft SQL Server™ and Oracle. Because there is such widespread use of ODBC, an OLE DB provider for ODBC has also been created in order to ease the migration from ODBC to OLE DB. Several nonrelational providers are currently under development. Perhaps the most anticipated of these is the OLE DB Provider for Microsoft Outlook®. A special provider, MS Remote, allows direct data access over the Internet. This brief list of providers shows the third-party community commitment to OLE DB, and many new providers are currently under development. For the latest news on available providers, refer to https://www.microsoft.com/data/.

ADO Overview

OLE DB is then a set of low-level interfaces that provide access to data in a variety of formats and locations. While powerful, OLE DB interfaces can be cumbersome to work with directly. Fortunately, ADO provides a set of high-level, developer-friendly interfaces that make working with OLE DB and universal data access a relatively simple task. Regardless of the programming environment you use, any Visual Studio® or Microsoft Office product such as Visual FoxPro, Visual Basic, Visual C++®, or Word, the interface you will use to access data remains constant. That interface is ADO, which in turn uses OLE DB.

ADO itself is just a set of objects. By itself, ADO is not capable of anything. In order to provide any functionality, ADO needs the services of an OLE DB provider. The provider in turn uses the low-level OLE DB interface to access and work with data. One ADO connection may use a SQL Server OLE DB provider and another ADO connection may use an Oracle OLE DB provider. While the interface is constant, the capabilities may be very different because OLE DB providers are very different, which highlights the polymorphic nature of OLE DB.

As developers, we crave consistency. ADO provides us with a consistent interface for our program code.

ADO Version Summary

The current version of ADO (2.1) is the fourth version of ADO to be released in less than two years. ADO 1.0 was primarily limited to working with Active Server pages. Only one OLE DB provider existed, the OLE DB Provider for ODBC Drivers.

ADO (2.1)—Ships with the newest version of Microsoft Web browser, Internet Explorer 5.0. When discussing data or anything related to the Internet, it is almost impossible to do so without mentioning XML. XML, the Extensible Markup Language, is a mark-up language that allows users to create custom tags to describe data. XML is quickly becoming the universal format for storing and streaming data. The primary storage format in Office 2000 for document data will be XML. ADO (2.1) client-side recordsets can be saved as XML documents.

ADO (2.0)—Represented a huge gain in functionality. One of the most notable new features was the ability to create client-side recordsets. To go along with this, also added were the abilities to create filters and indexes, and the ability to sort recordsets. These abilities are very much the same as those that exist with Visual FoxPro cursors. Finally, the ability to persist client-side recordsets was also added. In effect, data could be acquired from a server into a client-side recordset. The client-side recordset could then be saved as a file on the local hard-drive that could be opened at a later time without being connected to the network.

ADO (1.5)—Introduced new capabilities and providers to ADO. Among the new providers was the OLE DB Provider for Jet (the JOLT Provider). The MS Remote Provider, which powers the Remote Data Services (RDS), was introduced as well. This version also introduced the ability to create disconnected recordsets.

What You Need to Get Started

In order to work through the examples presented in this paper, you will need the following:

Microsoft Visual FoxPro 6.0
Microsoft Data Access Components, which can be downloaded from https://www.microsoft.com/data/
SQL Server 6.5 or 7.0 with the sample Northwind database installed
A system DSN called TasTrade that points to the TasTrade Visual FoxPro Sample Database
A system DSN called Northwind that points to the SQL Server Northwind database

Why Incorporate ADO into a Visual FoxPro Application?

Have you ever wanted to pass a cursor as an argument to a function or class method? Or have you wanted to pass data to automation server applications such as Microsoft Word or Excel? Perhaps you have created a Visual FoxPro DLL and have needed a way to pass data from the user interface to a class method in the DLL. Maybe you have been looking for a way to stream data across the Web. If your answer is "yes" to at least one of these, ADO can help you today!

Until now, the world of component-based development has lacked one thing: a method of effectively moving data between processes. Now, whether ADO is hosted by Visual FoxPro, Visual Basic, Excel, or Word, the interface is consistent. The new COM capabilities of Visual FoxPro 6.0 enable creating of ADO recordsets, populating them with data, and passing them to a variety of processes. This all goes to support the strategic positioning of Visual FoxPro, a creator of middle-tier components.

Just about everything in Visual FoxPro is an object, except for reports, menus, and data. One of the biggest feature requests from Visual FoxPro developers has been the ability to work with data as a set of objects. Data objects provide several benefits, including an enhanced event model and the ability to overcome limitations of Visual FoxPro cursors. While many limitations are gone, many benefits of Visual FoxPro cursors have been retained. As you work with ADO, there's good reason to think are many similarities to Visual FoxPro; ADO is based on the Visual FoxPro cursor engine. So, for those who have wanted data objects in Visual FoxPro, the wait is over with ADO.

ADO is not a replacement for Visual FoxPro cursors. Rather, Visual FoxPro cursors and ADO are complementary. When used together, very powerful applications can result. The following pages detail the ADO object model and the common properties and methods you will work with, including:

Remote Data Services (RDS), technology which allows for the streaming of data over the Internet via HTTP.
VFPCOM.DLL, which enables the handling of COM events in Visual FoxPro.
ADO Integration into Visual FoxPro.

This section has several comprehensive examples on strategies you may employ when integrating ADO into your Visual FoxPro Applications.

ADO Object Model

Connection Object

ProgID: ADODB.Connection

The purpose of the Connection object is to provide access to a data store. To illustrate, the following code creates an ADO Connection object:

oConnection = CreateObject("adodb.connection")

Once an ADO Connection object has been created, you can access its data store. An active connection can be established by providing a few pieces of key information and invoking the Open( ) method of the Connection object. The following code opens a connection to the Visual FoxPro TasTrade database:

oConnection.Open("TasTrade")

Alternatively, the following code accesses the SQL Server Northwind database:

oConnection.Open("Northwind","sa","")

These two examples work with the OLE DB Provider for ODBC drivers. Different OLE DB providers can be used as well. The following example sets some common properties of the Connection object and uses the OLE DB Provider for SQL Server:

With oConnection
   .Provider = "SQLOLEDB.1"
   .ConnectionString = "Persist Security Info=False;User 
      ID=sa;Initial Catalog=Northwind;Data Source=JVP"
   .Open
EndWith

Using and creating data link files

The syntax of the ConnectionString property appears complicated. Fortunately, you don't have to code this by hand. When you install the Microsoft Data Access Components (MDAC), you can create a data link file.

To create a data link file:

Right-click your desktop and choose New\Microsoft Data Link from the pop-up menu.
Specify a name for the file.
Right-click and select Properties to modify the file properties.
In the Properties dialog box, click the Provider tab, and choose a provider.
The OLE DB Provider for ODBC is the default choice. For this example, select the OLE DB Provider for SQL Server.
Click the Connection tab.
Specify the name of the server, your user name and password, and the name of the database you wish to connect to.
Open the UDL file in Notepad.

Now, it is just a matter of copying and pasting the information. Alternatively, you can use the file itself:
```
oConnection.Open("File Name=c:\temp\test.udl")
```

ADO recognizes four arguments in the ConnectionString:

File Name: Specifies the name of a UDL file to use.
Provider: Specifies the name of an OLE DB provider to use.
Remote Provider: Specifies the name of a provider to use with Remote Data Services (RDS).
Remote Server: Specifies the server on which data resides when using Remote Data Services (RDS).

Any additional arguments passed in the ConnectionString are passed through to the OLE DB provider being used.

In addition to the Open method, the following are the common methods you are likely to use with the Connection object:

BeginTrans, CommiTrans, and RollBackTrans—These methods work like the Begin Transaction, End Transaction, and RollBack statements in Visual FoxPro. The Connection object controls all transaction processing. For more detail, see the section Transactions/Updating Data. Note that not all OLE DB providers support transaction processing.
Close—This method closes an open Connection object.
Execute—This method runs a SQL statement, stored procedure, or OLE DB provider-specific command. In reality, a Command object, which actually does the work of executing the command, is created on the fly. More on the Command object and the flat object hierarchy of ADO later in this paper.
OpenSchema—This method returns information regarding defined tables, fields, catalogs, and views into an ADO Recordset object. This method works like the DBGetProp( ) function in Visual FoxPro.

Errors collection

ADO does not trap errors, nor does it have an error handler. Instead, ADO can record the occasions when errors occur. It is up to the host application, Visual FoxPro in this case, to both trap and handle the error. ADO only reports what errors have occurred. Note that the error is actually reported by the specific OLE DB provider. ADO is merely a vehicle to report the error.

The Errors collection is part of the Connection object and consists of zero or more Error objects. When an error occurs, an Error object is appended to the Errors collection. The following code illustrates how the Errors collection works. In this example, the name of the database has been misspelled purposely in order to generate an error:

oConnection = CreateObject("adodb.connection")
With oConnection
   .Provider = "SQLOLEDB.1"
   .ConnectionString = "Persist Security Info=False;User 
    ID=sa;Initial Catalog=Nothwind;Data Source=JVP"
   .Open
EndWith
*/ At this point an error will occur – causing VFP's default error
*/ handler – or the active error handler to invoke
*/ At this point, we can query the Errors Collection of the
*/ Connection Object
For Each Error In oConnection.Errors
   ?Error.Description,Error.Number
Next Error

Recordset Object

ProgID: ADODB.Recordset

Once you establish an ADO connection, you can open a recordset of data. The Recordset object is very much like a Visual FoxPro cursor. Like the Visual FoxPro cursor, an ADO recordset consists of rows of data. The recordset is the primary object that you will use while working with ADO. Like the Connection object, the Recordset object also provides an Open method. To illustrate, the following code opens the Customer table of the Visual FoxPro Tastrade database:

oRecordSet = CreateObject("adodb.recordset")
oRecordSet.Open("Select * From Customer",oConnection)

The first argument of the Open method specifies the source of data. As you will see, the source can take on several forms. The second argument of the Open method specifies a connection to use for retrieving the data specified by the source. At a minimum, this is all you need to open a recordset. Additional examples will expand on the additional arguments the Open method accepts.

With a Recordset object created, one of the most common actions you will perform is navigating through records. Depending on the type of ADO recordset that has been created, certain navigational capabilities may or may not be available. The different types of possible ADO recordsets will be discussed shortly. The following code illustrates how to navigate through an ADO recordset:

Do While !oRecordSet.Eof
   oRecordset.MoveNext
EndDo

The following paragraphs briefly describe the most common recordset properties and pethods you are likely to use. It is by no means a replacement for the ADO documentation, which gives both a complete description of the properties and methods and complete descriptions of acceptable enumerated types and arguments. ADO is well documented in the Microsoft Data Access Components (MDAC) SDK. You can download the MDAC SDK from https://www.microsoft.com/data.

In addition, I highly recommend ADO 2.0 Programmers Reference, by David Sussman and Alex Homer, from Wrox Press.

RecordSet types

You can create four types of recordsets in ADO:

Forward Only—This type of recordset can be navigated only in a forward direction. It is ideal when only one pass through a recordset is required. Examples include populating a List box or a Combo box. The RecordCount property is irrelevant with this type of recordset.
Keyset—This type of recordset keeps acquired data up to date. For example, if you retrieve 100 records, data modified by other users to those 100 records will be visible in your recordset. However, modifications regarding new or deleted records made by other users will not be visible in your recordset. Both forward and backward navigation are supported. The RecordCount property returns a valid value with this type of recordset.
Dynamic—With this type of recordset, all underlying data is visible to the Recordset object. Because the number of records in the underlying table can change, the RecordCount property is irrelevant with this type of cursor. However, forward and backward navigation are supported.
Static—Both the number of records and data are fixed at the time the Recordset object is created. The only way to get the latest version of data and all records is to explicitly invoke the Requery method. You can use the RecordCount property. In addition, both forward and backward navigation is permitted.

RecordSet locations

Recordset objects can exist in either of two locations, the server or the client:

Server—The most common examples of server-side ADO recordsets are those created through Active Server Pages (ASP).
Client—A recordset that resides on a workstation is useful when creating disconnected recordsets or recordsets on which you wish to apply filters, sorts, or indexes.

The most common properties you are likely to use with ADO recordsets include the following:

ActiveCommand property—An object reference to the Command object that created the recordset.
ActiveConnection property—An object reference, to the Connection object, that provides the link to an underlying data source.
AbsolutePosition property—Specifies the relative position of a record in an ADO recordset. Unlike the Bookmark property, which does not change, the AbsolutePosition property can change depending on the active sort and filter.
Bookmark property—A unique record identifier that, like the record number in a Visual FoxPro cursor or a record number in Visual FoxPro, does not change during the life of a recordset.
BOF/EOF properties—Beginning of File and End of File, respectively, that work just like the BOF( ) and EOF( ) functions in Visual FoxPro.
EditMode property—Specifies the editing state of the current record in an ADO recordset.
Filter property—The string that represents the current filter expression. This property is like the SET FILTER statement in Visual FoxPro. Unlike the Find method, multiple expressions linked with AND or OR operators are allowed. This property is only applicable to client-side recordsets.
Sort property—A comma-delimited set of fields that specifies how the rows in an ADO recordset are sorted. This property is only applicable to client-side recordsets.
State property—Specifies the state of an ADO recordset. Valid State properties are closed, open, connecting, executing, or fetching.
Status property—Specifies the editing status of the current record. Valid Status properties include unmodified, modified, new, and deleted. This property can be any one of the values contained in RecordStatusEnum.
MarshalOptions property—Specifies how records are returned (marshaled) to the server. Either all or only modified records can be returned. This property is only applicable to client-side disconnected recordsets
MaxRecords property—Specifies the total number of records to fetch from a data source.
RecordCount property—Specifies the number of records in a recordset. This property is like the Recc( ) function in Visual FoxPro.
Source property—Specifies the command or SQL statement that provides data for the recordset.

Note The type and location of a cursor as well as the OLE DB provider you select will affect the recordset properties that are available.

Use the following table as a guide to help you make the right recordset type and location decision:

Table 1. Properties

Type	Bookmark	RecordCount	Sort	Filter	MarshalOptions
Forward Only
Key Set	`4`	`4`
Dynamic
Static: Client	`4`	`4`	`4`	`4`	`4`
Static: Server	`4`	`4`

Only client-side recordsets can be sorted and filtered. If the CursorLocation property of ForwardOnly, KeySet, and Dynamic recordset types is set to adUseClient, making them client-side cursors, the CursorType property is automatically coerced to the Static Cursor type.

Note This is the behavior of the OLE DB Provider for SQL Server. The OLE DB Provider for ODBC supports only ForwardOnly and Static recordsets, regardless of where the recordset resides.

As with properties, method availability can also vary:

Table 2. Available Methods

Type	MoveFirst	MovePrevious	MoveNext	MoveLast	Resync	Requery
Forward Only			`4`			`4`
Key Set	`4`	`4`	`4`	`4`		`4`
Dynamic	`4`	`4`	`4`	`4`		`4`
Static – Client	`4`	`4`	`4`	`4`	`4`	`4`
Static – Server	`4`	`4`	`4`	`4`		`4`

The following list describes some of the common methods you will use in the ADO Recordset object:

MoveFirst, MovePrevious, MoveNext, MoveLast, and Move methods—Navigational methods that work as their respective names imply. The Move method accepts two arguments, the number of records to move and the position from which to begin the move. The Move method is similar to the Go statement in Visual FoxPro. MoveFirst and MoveLast work like Go Top and Go Bottom, respectively. Finally, MovePrevious and MoveNext work like Skip 1 and Skip –1, respectively.
Find method—Accepts a criterion string as an argument and searches the recordset for a match. If a match is not found, depending on the search direction, either the BOF or EOF property will evaluate to true (.T.). This method works much the same way as the Seek and Locate statements in Visual FoxPro. Unlike the Filter property and the Seek and Locate statements in Visual FoxPro, the ADO Recordset object does not allow multiple search values joined by the And or the Or operator. Using anything other than a single search value will result in an error.
Open method—Opens an existing ADO Recordset object. This method accepts several arguments and is discussed in detail later in this section.
Close method—Closes an ADO Recordset object. Many properties, such as CursorType and LockType, although read/write, cannot be modified while the recordset is open. The Close method must be invoked before those and other properties are modified.
Update and UpdateBatch methods—Update writes changes for the current record to the underlying data source; UpdateBatch writes pending changes for all modified records to the underlying data source. The UpdateBatch method is only relevant when Optimistic Batch Locking is used.
Cancel and CancelBatch methods—The Cancel method cancels modifications made to the current record; the CancelBatch method cancels pending changes to all modified records.
Resync method—Refreshes the Recordset object with data from the underlying data source. Invoking this method does not rerun the underlying command. Options exist for which records are actually refreshed.
Requery method—Unlike the Resync method, reruns the underlying command, which causes any pending changes to be lost. In effect, issuing a Requery is like invoking the Close method then immediately invoking the Open method.
Supports method—Specifies whether or not the recordset supports a function, based on a passed argument. For example, you can use this method to specify whether a recordset supports bookmarks, or the addition or deletion of records, or the Find, Update, and UpdateBatch methods, to name a few. Because what is supported is depends on the OLE DB provider used, it is a good idea to use this method to make sure a needed function is supported.
GetRows method—Returns a set of records into an array.
GetString method—Returns a set of records into a string.

The moral of the story is that before relying on the existence of anything in ADO, know and understand the OLE DB provider you are using, because the capabilities available to you can vary dramatically.

Lock types

There are four different locking schemes in ADO recordsets. These locking schemes are similar to those in Visual FoxPro.

Read-Only—As the name indicates, the recordset is opened for read-only purposes only. When you don't need to modify data, this is the best locking scheme to use from a performance standpoint. This scheme applies to both server and client-side recordsets.
Lock Pessimistic—In this scheme, a lock attempt is attempted as soon as an edit is performed. This locking scheme is not relevant for client-side recordsets. Pessimistic Locking in an ADO recordset is like Pessimistic Locking with Row Buffering in a Visual FoxPro cursor.
Lock Optimistic—In this scheme, a lock attempt is made when the Update method is invoked. This locking scheme applies to both server and client-side recordsets. Optimistic Locking in an ADO recordset is like Optimistic Locking with Row Buffering in a Visual FoxPro cursor.
Lock Batch Optimistic—This scheme is like the Lock Optimistic scheme, except that more than one row of data is involved. In this scheme, a lock is attempted on modified records when the UpdateBatch method is invoked. This scheme is like Optimistic Locking with Table Buffering in a Visual FoxPro cursor.

The following table illustrates the availability of some common methods depending on the locking scheme used:

Table 3. Method Availability (Depending on Lock Type)

Lock Type	Cancel	CancelBatch	Update	UpdateBatch
Read Only	`4`
Pessimistic	`4`	`4`	`4`	`4`
Optimistic	`4`	`4`	`4`	`4`
Optimistic Batch	`4`	`4`	`4`	`4`

With the concepts of cursor types, locations, and locking schemes out of the way, we can discuss the real abilities of ADO recordsets. The most notable of these abilities are updating, sorting, and filtering of data. Before undertaking that discussion, however, take a few moments to review the Fields Collection object.

Fields collection object

Associated with the Recordset object, is the Fields Collection object. The Fields Collection object contains zero or more Field objects. The following code enumerates through the Fields Collection of a Recordset object:

For Each ofield In oRecordset.Fields
   With oField
      ?.Name,.Value,.Type,.DefinedSize
      ?.ActualSize,.NumericScale,.Precision
   EndWith
Next oField

The common Field properties you will work with:

Name—Specifies the name of the Field object. This corresponds to the name of the data element in the underlying data source. It is easy to define the name element as the name of the field in the underlying table. However, note that ADO and OLE DB work with both relational and nonrelational data. Given that, while you may be working with ADO, the underlying data may come from Outlook, Excel, Word, or Microsoft® Windows NT® Directory Services.
Value—Indicates the current value of the Field object.
OriginalValue—Indicates the Value property of the Field object before any modifications where made. The OriginalValue property returns the same value that would be returned by the OldVal( ) function in Visual FoxPro. When you invoke the Cancel or CancelUpdate methods of the Recordset object, the Value property of the Field object is replaced by the contents of the OriginalValue property. This behavior is similar to that exhibited when TableRevert( ) is issued against a Visual FoxPro cursor.
UnderlyingValue—Indicates the current value in the data source. This property corresponds most closely to the CurVal( ) function in Visual FoxPro. To populate the Value property of each Field object in the Fields collection, you need to invoke the Resync method of the Recordset object. With a client-side cursor, this property will return the same value as the OriginalValue property, since the recordset may or may not have an active connection.
Type—Indicates the data type of the Field object. The value of this property corresponds to a value contained in DataTypeEnum. Examples of values in DataTypeEnum are adBoolean, adInteger, and adVarChar.
Defined Size—Specifies the size of the field containing a data element in the data source. For example, in SQL Server, the Country field in the Customers table of the Northwind database is 15 characters long. Therefore, the DefinedSize property of the Country Field object is 15.
ActualSize—Represents the length of the actual data element in a datasource. To illustrate, consider the Country Field object again. In the case where the value is Germany, the ActualSize property is 7, while the DefinedSize property is still 15.
NumericScale—Specifies how many digits to the right of the decimal place are stored.
Precision—Specifies the maximum number of digits to be used for numeric values.

In addition to these properties, GetChunk is one interesting method you are likely to use. This method allows you to progressively fetch portions of the contents of a field object. This method is very useful when dealing with large text fields. It can be used only on fields where the adFldLong Bit set of the Attributes property is set to true (.T.). See the next section for details on the Attributes property. Understand that fields of the type ADLongVarChar have the adFldLong Bit set. The Notes field of the Employees table is of the type adLongVarChar.

The following code fetches data from the notes field in 10-byte chunks:

Local nBytesRead,cChunkRead 
nBytesRead = 0
cChunkRead = Space(0)
Do While .T.
   nBytesRead = nBytesRead + 10
   cChunkRead = oRecordset.Fields("notes").GetChunk(10)
   If IsNull(cChunkRead) Or;
    nBytesRead > oRecordset.Fields("notes").ActualSize
      Exit
   Else
      ?cChunkRead
   Endif   
EndDo

Successive calls to GetChunk continue where the previous call ended. The GetChunk method is very useful when you need to stream data or only need to see the first few characters of a large text field.

Along with GetChunk, examine the AppendChunk method. The first time this method is called for a field, it overwrites any data in the field. Successive calls then append the data, until pending edits are cancelled or updated. The following code illustrates how this method works:

For x = 1 To 100
   oRecordset.Fields("notes").AppendChunk(Str(x)+Chr(10)+Chr(13))
Next x

Both the GetChunk and AppendChunk methods are ideal for dealing with low memory scenarios.

The Attributes property

An attribute specifies the characteristics of something. As a person, you have many attributes, eye color, height, weight, and so forth. In the OOP world, objects have many attributes. Most of the time, attributes are exposed in the form of properties. A Visual FoxPro form has several properties such as Width, Height, and BackColor, just to name a few. The same is true for objects in ADO. Sometimes, however, it is not convenient to have a one-to-one correspondence between attributes and properties. Often, you can pack large amounts of information into a smaller space through the power of setting bits. A bit is much like a switch. It is either on or off or 1 or 0. If you string these bits together, you gain the ability to store multiple values in a small space. This is how the Attributes property works.

The Connection, Parameter, Field, and Property objects all have an Attributes property. If you have never worked with bit operations before, working with this property can be quite challenging. In some situations, as is the case with the GetChunk and AppendChunk methods, you will need to refer to the Attributes property of the Field object to determine whether those methods are available.

Using the Field object to illustrate how the Attributes property works, you can associate the following attributes with a Field object and its associated binary values:

AdFldMayDefer—Indicates that the field contents are retrieved only when referenced—0x00000002
adFldupdateable—Indicates that the field can be updated—0x00000004
adFldUnkownupdateable—Indicates that the provider does not know whether the field is updateable—0x00000008
adFldFixed—Indicates that the field contains fixed length data—0x00000010
adFldIsNullable—Indicates that the field can accept a null value during a write operation—0x00000020
adFldMayBeNullable—Indiates that the field may contain a null value—0x00000040
adFldlong—Indicates that the field contains long binary data and that the GetChunk and AppendChunk methods can be used—0x00000080
adFldRowID—Indicates that the field contains a row ID and cannot be updated. This does not relate to a field that may contain the identity value or some other auto-incrementing value. Rather, it relates to a ROW ID that is unique across the database. Oracle has this feature—0x00000100
adFldRowVersion—Indicates whether the field indicates the version of the row. For example, a SQL TimeStamp field may have this attribute set—0x00000200
adFldCachedDeferred—Indicates that once this field has been read, future references will be read from the cache—0x00001000

Usually, more than one of these attributes are present at any given time. Yet the Attributes property is a single value. Using the Employees table Notes field as an example, you will see that the Attributes property yields a value of 234. The value 234 represents the sum of the attributes for that field. For example, nullable and long attributes have decimal values of 32 and 128 respectively. This means that the Attributes property evaluates to 160. This works like the Windows Messagebox dialog box with regard to specifying the icon and types of buttons that are present.

Knowing that the Attributes property is a sum of the attributes of a Field object does not help in determining whether a specific attribute is present. This is where understanding bit operations comes in handy. The first step is to convert the sum (such as 234, above) into a binary equivalent:

11101010

Working from right to left, (or from the least significant bit to the most significant)—and beginning with zero, see that bits 1, 3, 5, 6, and 7 are set, (indicated by their values of 1 in those positions). Bits 0, 2, and 4 are not set. The next step is to determine whether a field is "long."

To determine whether a field is a long field, we must first convert the adFldLong constant, which specifies which bit if set, indicates that the field is long. The adFldLong constant has a hex value of 0x00000080. This translates into a decimal value of 128. The following is the binary equivalent:

10000000

Converting a hex value to decimal in Visual FoxPro is simple. The following code illustrate how to convert hexadecimal values to decimal:

x = 0x00000080
?x && 128

And, if you ever need to convert back to hexadecimal:

?Transform(128,"@0") && 0x00000080

Using our original hex value, 11101010, and working from right to left and beginning with zero, see that the seventh bit is set. Therefore, the seventh bit of the Attributes property, if set, means the field is long. Going further, whatever attributes occupy bits 1, 3, 5, and 6, also apply to this field. The following table of field attributes should help to sort things out:

Table 4. Field Attributes

Hex Value	Decimal Value	Field Attribute Constant	Bit
0x00000002	2	AdFldMayDefer	1
0x00000004	4	AdFldupdateable	2
0x00000008	8	AdFldUnkownUpdateable	3
0x00000010	16	AdFldFixed	4
0x00000020	32	AdFldIsNullable	5
0x00000040	64	AdFldMayBeNull	6
0x00000080	128	AdFldLong	7
0x00000100	256	AdFldRowID	8
0x00000200	512	AdFldRowVersion	9
0x00001000	4096	AdFldCacheDeferred	12

So, along with being a long field, the field is deferred, updateable, can have a null written to it, and it may also already contain a null value. Visually, this makes sense. How can you do this programmatically?

If you refer to online examples (almost always programmed in Visual Basic), you will see code like this:

If (oField.Attribute AND adFldLong) = adFldLong 
   ' The field is long
End If

This is pretty slick in that you can test for whether a specific attribute bit is set by using the AND operator with the attribute property and the constant. If you try this in Visual FoxPro, you will get data type mismatch errors. Fortunately, there is a way. Visual FoxPro contains a host of bit functions. One function, BITTEST, does as its name implies. It tests whether a specified bit in a passed argument is set. To review, we need to see if the seventh bit in the value 234 is set. The following Visual FoxPro code demonstrates how to use the BITTEST function:

If BitTest(234,7)
   */ The Field is long
Endif

To find out if the field is nullable:

If BitTest(234,5)
   */ The Field is long
Endif

The Attributes property of the Connection, Parameter, and Property objects works in the same manner as illustrated above. The differences are the names and quantity of attributes that are present.

ADO and COM defined constants

ADO and OLE DB, like any COM components, make extensive use of defined constants in the examples that document the usage of properties, events, and methods. Other development environments in Visual Studio such as Visual Basic and Visual Interdev provide IntelliSense technology, because of their respective abilities to interact directly with the type libraries of COM components. For these development environments, you can reference defined constants just as if they were a part of the native language. So, working with published examples is a fairly trivial task. On the other hand, in the Visual FoxPro development environment there is, in fact, a bit of a challenge. The question always seems to be "How can I use the Visual Basic samples in Visual FoxPro?" The biggest stumbling block is usually in finding the value of the defined constants. In Visual FoxPro, you need to use the #Define statement for each constant.

One solution for obtaining the value of the ADO defined constants is to obtain the MDAC SDK from Microsoft. The MDAC SDK can be downloaded from https://www.microsoft.com/data/download.htm.

Once you install the SDK, locate the Include\ADO directory. In that directory, you will find the ADOINT.H file, which contains all of the enumerated types and the values for the defined constants.

A second, and perhaps easier, solution is to use the resources already installed on your machine. If you are working through the sample code in this paper, you already have the Microsoft Data Access Components installed on your workstation. The Visual Basic Development Environment (both the full Visual Basic IDE and the Visual Basic Editor in desktop applications like Word and Excel) has a great resource called the Object Browser. This could, in fact, be the most underutilized tool on the planet.

To illustrate its functionality, open any desktop application that uses Visual Basic, such as Word or Excel. Or, if you have the Visual Basic Programming System installed, you can open that as well.

If you opened a VBA application

From the View menu, choose Toolbars.
From the Toolbars menu, choose Visual Basic.
On the Visual Basic toolbar, click Visual Basic Editor.
From the Tools menu, choose References.
Check the Microsoft Data Access Objects 2.x Library.

If you opened the Visual Basic IDE

Create an empty project.
From the Project menu, select References.
Check the Microsoft Data Access Objects 2.x Library.

Now, whether you are in the VBA Editor or the VB IDE

Press F2 to display the Object Browser.
In the first combo box, select ADODB.
In the second box, type ADVARCHAR.
Press Search
or
Press Enter.

Clearly, the Object Browser is a powerful tool for the developer who works with COM components. Not only are the defined properties, events, and methods accessible in the Object Browser, so also are the defined constants and their respective values. Notice the value of adVarChar in the lower pane of the Object Browser.

Opening, sorting, and filtering data

One of the big advantages of using a development platform such as Visual FoxPro is its local data engine. Not only does the engine provide superior query performance, but it also provides some very flexible capabilities when it comes to both working with and presenting data. There isn't a Visual FoxPro application that fails to sort or filter data to some degree. In Visual FoxPro, sorting is accomplished by creating a set of index tags for a table. Filtering is accomplished by using the Set Filter command. Fortunately, ADO has these capabilities as well.

You can see in the Field Attribute table that the availability of features depends on the location in which the recordset is created. It is clear that we must ensure that a client-side recordset is created.

For example, create a Connection object to the TasTrade or SQL Server Northwind database. The following code assumes that the Connection object, oConnection, has been created before you open the Recordset object.

First, we need to implement a few required #Defines:

#Define adUseClient   3
#Define adLockBatchOptimistic   4
#Define adCmdTable   2

For SQL Server:

With oRecordset
   .Source = "Customers"
   .ActiveConnection = oConnection
   .CursorLocation = adUseClient
   .LockType = adLockBatchOptimistic
   .Open
EndWith

oRecordset.Open("Customers",;
                 oConnection,;
                 adUseClient,;
                 adLockBatchOptimistic)

For Visual FoxPro:

With oRecordset
   .ActiveConnection = oConnection
   .Source = "Customer"
   .CursorType = adOpenStatic
   .LockType = adLockReadOnly
   .CursorLocation = adUseClient
   .Open(,,,,adCmdtable)
EndWith

oRecordset.Open("Customer",;
                 oConnection,;
                 adUseClient,;
                 adLockBatchOptimistic,;
                 adCmdTable)

With oRecordset
   .ActiveConnection = oConnection
   .Source = "Select * From Customer"
   .CursorType = adOpenStatic
   .LockType = adLockReadOnly
   .CursorLocation = adUseClient
   .Open
EndWith

oRecordset.Open("Select * From Customer",;
                 oConnection,;
                 adUseClient,;
                 adLockBatchOptimistic)

SQL Server and Visual FoxPro open data differently. Remember that when using SQL Server, you are using the OLE DB Provider for SQL Server. When you access data in Visual FoxPro, use the OLE DB Provider for ODBC, since there is no native OLE DB provider for Visual FoxPro.

The difference rests with the optional fifth argument of the Open method. The SQL Server OLE DB Provider is designed to recognize when you pass just a table name. With the ODBC OLE DB Provider, you must specify how it should interpret the Source property. By default, the ODBC OLE DB Provider expects a SQL statement. When you pass a SQL statement, there is no need to explicitly state how the provider should interpret things. The Visual FoxPro ODBC driver generates an "Unrecognized Command Verb" error message if you only specify a table name as the source and you fail to use the optional fifth argument. Note that if you use the ODBC OLE DB Provider to access SQL Server, you must employ the same technique that is needed for Visual FoxPro.

Which method should you employ when you populate the properties individually before invoking the Open method or passing the arguments to the Open method? Once again, it is a matter of preference. Of the two, manually populating the properties makes for more readable code.

Sorting and filtering data are just matters of manipulating the Sort and Filter properties respectively. The following code sorts the recordset created from TasTrade in the example above, by country, ascending, then by region, descending:

oRecordset.Sort = "Country,Region Desc"

The following code displays the sort and the functionality of the AbsolutePosition and Bookmark properties.

oRecordset.MoveFirst
Do While Not oRecordset.Eof
   With oRecordset
      ?.Fields("country").Value,;
       .Fields("region").Value,;
       .AbsolutePosition,;
       .Bookmark 
       .MoveNext
   EndWith
EndDo

Setting a filter is as easy as setting the sort. The following code filters for records where the country is Germany:

oRecordset.Filter = "Country = 'Germany'"

The Filter property also supports multiple values:

oRecordset.Filter = "Country = 'Germany' Or Country = 'Mexico'"

Finally, wild card characters are also supported:

oRecordset.Filter = "Country Like 'U*'"

To reset either the Filter or Sort properties, set them equal to an empty string:

oRecordset.Sort = ""
oRecordset.Filter = ""

Finding data

Another important capability of an ADO recordset is the ability to find records based on a search string. This capability works like searching for records in a Visual FoxPro cursor. Unlike the Seek or Locate statement in Visual FoxPro, the Find method provides control over the scope of records that are searched. The following code searches for a country that begins with the letter "B."

oRecordset.Find("country Like 'B%'")

Although multiple criteria are not allowed, wild card searches are permitted:

oRecordset.Find("country Like 'U*'")

Searches for multiple criteria, such as the following, would result in an error:

oRecordset.Find("country Like 'G*' Or country Like 'B*'")

Transactions/updating data/conflict resolution

Updating data in an ADO recordset is a fairly simple process. As in any environment, conflict resolution in multi-user environments is always an issue to be dealt with. This is where the Errors collection comes into play. Error trapping and handling needs to become an integral part of your ADO-related code. The following code samples employ a simple error handling scenario and use the Errors collection to determine whether conflicts have occurred. For a complete list and description of ADO error codes, consult the online documentation.

When you update data, you can update either a single row, or several rows at a time in batch mode. These methods most closely correspond to row and table buffering, respectively, in Visual FoxPro. Building on the recordset already created, the lock type is Batch Optimistic. While updates are normally conducted in batches, you can also update one row at a time, just as in Visual FoxPro.

The following code modifies the CompanyName field and attempts to update the SQL Server data source:

oRecordset.Fields("companyname").Value = "Ace Tomato Company"
oRecordset.Update

Depending on a variety of scenarios, this code may or may not work. Perhaps a contention issue exists? Perhaps the user does not have rights to modify data. Hundreds of issues can cause an attempted update to fail. Therefore, anytime you attempt an update, you should employ error trapping. The following code expands the previous example and makes it a bit more robust:

Local Err,cOldErr,oError
cOldError = On("Error")
On Error Err = .T.
oRecordset.Fields("companyname").Value = "Ace Tomato Company"
oRecordset.Update
If Err
   For Each oError In oRecordset.ActiveConnection.Errors
      With oError
         ?.Number,.Description
      EndWith
   Next oError
Endif
On Error &cOldErr

If you are thinking, "Hey, maybe I should write a wrapper class to better encapsulate and centralize code," you're on the right track. The following code creates a custom class that can serve as a starting point:

Local oRecordsetHandler
oRecordsetHandler = CreateObject("RecordsetHandler")
oRecordset.Fields("companyname").Value = "Alfreds Futterkiste"
If !oRecordsetHandler.Update(oRecordset)
   oRecordsetHandler.Cancel(oRecordset)
Endif
Define Class RecordsetHandler As Custom
   Protected oRecordset
   Protected ErrFlag
   
   Procedure Update(oRecordset)
      This.oRecordset = oRecordset
      oRecordset.UpdateBatch
      Return !This.ErrFlag
   EndProc
   Procedure Cancel(oRecordset)
      This.oRecordset = oRecordset
      oRecordset.Cancel
      Return !This.ErrFlag
   EndProc
   
   Procedure Error(nError, cMethod, nLine)
      Local oError
      For Each oError In This.oRecordset.ActiveConnection.Errors
         With oError
            ?.Number,.Description
         EndWith
      Next oError
      This.ErrFlag = .T.
   EndProc
EndDefine

There's a better way to determine whether an update proceeded successfully. The preferred approach is to trap events that ADO fires. Visual FoxPro by itself does not surface COM Events. Fortunately, the new VFPCOM.DLL component provides this capability to Visual FoxPro. The previous example can be modified to show how using COM Events makes for more robust code and class design.

Now we can improve the code of our example. Most of the time, for efficiency, you will want to batch your updates that comprise multiple records. Often, when you update multiple records, transaction processing is required. In other words, either updates to all records must succeed or none should occur. To illustrate, let's say you must apply a 10 percent price increase to the products you sell. The prime requirement is that all records in the Products table need modification. Without transactional capabilities, the possibility exists that, for example, after the first 10 records are updated, an error generated on the eleventh record prevents a complete update. Transaction processing provides the ability to rollback changes.

The following example incorporates error trapping and the three transaction methods of the Connection object:

Local Err,cOldErr
cOldErr = On("error")
On Error Err = .T.
oRecordset.ActiveConnection.BeginTrans
Do While !oRecordset.Eof
   If Err
      Exit
   Else
      With oRecordset
         .Fields("unitprice").Value = ;
            .Fields("unitprice").Value * 1.1
         .Movenext
      EndWith   
   Endif
EndDo
oRecordSet.UpdateBatch
If Err
   oRecordset.ActiveConnection.RollBackTrans
   oRecordset.CancelBatch
Else
   oRecordset.ActiveConnection.CommitTrans
Endif   
On Error &cOldErr

Additional operations you are likely to employ with recordsets deal with adding new records and deleting existing records. Both of these processes are very simple. The following code adds a new record:

oRecordset.AddNew

As in Visual FoxPro, in ADO the new record becomes current. Once the AddNew method is invoked, the field can be populated and, depending on the LockType, you then invoke either the Update or UpdateBatch methods to modify the data source.

Deleting records is just as easy. The following code deletes the current record:

oRecordset.Delete

Once again, after deleting the record, a call to Update or UpdateBatch will update the data source.

SQL Server identity fields and parent/child relationships

SQL Server, like most server RDBMSs and Microsoft® Access®, creates an auto-incrementing field that can serve as a primary key for a table. Typically, the data type for this field is Integer. In SQL Server, this type of field is called the Identity field. Fields of this type are read-only. It begs the question, "When adding records, how can one determine what these values are?" Knowing that the next generated value is a requirement for maintaining referential integrity when child tables are involved. The following example code shows a recordset in which the first field, ID, is the auto-incrementing field. After new field is added, checking the value of the ID field yields a character with a length of zero. Attempting to update the field results in an error. However, once the recordset is updated, checking the value again will yield a valid identity value.

oRecordset.AddNew
?oRecordset.Fields("id").Value && empty string
oRecordset.UpdateBatch
?oRecordset.Fields("id").Value && returns new identity value

With the new identity value available, you can add records in child tables, using the identity value in the parent table as the foreign key in the child tables.

But, what do you do in cases where you have disconnected recordsets?

This section details an important capability in ADO—the ability to have recordsets without an active connection to the backend data source. At this point you can freely add new records to disconnected records. When the recordset is eventually reconnected, those newly added records are then sent to the backend data source. How do you know what the identity value will be in those cases? Simply put, you don't know. At the same time, however, you still need to be able to add both parent and child records locally. You need some method that maintains the relationship locally, while at the same time, supporting the use of the identity value when the data is sent to the backend.

The simplest solution to this problem is to include a field in each table that serves as the local ID. You need this extra field because the identity field will be read-only. On the client side, you can use several methods for producing an ID that is unique. One approach is to use the Windows API to fetch the next Global Unique Identifier (GUID). The following procedure outlines how the local process unfolds:

Add a new parent record.
Fetch the next GUID.
Update the local primary key column with the GUID.
Add a new child record.
Update the local primary key column with the GUID.
Update the foreign key column of the child with the GUID from its parent.

At some point, you will reconnect to the server. The update process could be performed within the context of a transaction, done one row at a time by navigating through each record. Checking the recordset Status property, which indicates whether the current record has been newly created, modified, deleted, and so on, determines whether the current row should be sent back to the server. If the record should be sent back, the parent record can be updated via the UpdateBatch method. The UpdateBatch method accepts an optional argument that specifies that only the current record be updated. By default, UpdateBatch works on all records. If the value of one is passed—corresponding to the adAffectCurrent constant—only the current record is updated. Once the update occurs, the identity value generated by the server is available. This value would then be used to update the foreign key columns of any related children. Once that process is complete, the records for that parent would be sent back to the server as well. This same process would be used if grandchild and great-grandchild relationships also existed.

The following Visual FoxPro code, from Visual FoxPro 6 Enterprise Development, by Rod Paddock, John V. Petersen, and Ron Talmage (Prima Publishing), illustrates how to generate a GUID:

Local oGuid
oGuid = CreateObject("guid")
?oGuid.GetNextGuid( )
*/ Class Definition
Define Class guid AS Custom
  */ Create protected members to hold parts of GUID
  Protected data1
  Protected data2
  Protected data3
  Protected data4
  Procedure GetNextGuid
    */ The only public member. This method will return the next GUID
    Local cGuid
    cGuid = This.Export( )
    UuidCreate(@cGuid)         
    This.Import(cGuid)            
    cGuid = This.Convert(cGuid)
    Return cGuid
  EndProc
  Protected Procedure bintoHex(cBin)
    */ This method converts a binary value to Char by calling the Hextochar

    */ Method
    Local cChars, nBin
    cChars = ""
    For nDigit = 1 To Len(cBin)
      nBin = Asc(Substr(cBin, nDigit, 1))
      cChars = cChars + This.Hex2Char(Int(nBin/16)) + ;
        This.Hex2Char(Mod(nBin,16))
    EndFor
    Return(cChars)
  EndProc
  Protected Procedure hex2char(nHex)
    */ This method converts a hex value to  ASCII 
    Local nAsc
    Do Case
      Case Between(nHex,0,9)
        nAsc = 48 + nHex
      Case Between(nHex,10,15)
        nAsc = 65 + nHex - 10
    EndCase
    Return(Chr(nAsc))
  EndProc
  Procedure import(cString)
    */ This method takes the binary string and populates the 4 data
    */ properties
    With This
      .Data1 = Left(cString, Len(.Data1))
      cString = SubStr(cString, Len(.Data1)+1)
      .Data2 = Left(cString, Len(.Data2))
      cString = SubStr(cString, Len(.Data2)+1)
      .Data3 = Left(cString, Len(.Data3))
      cString = SubStr(cString, Len(.Data3)+1)
      .Data4 = Left(cString, Len(.Data4))
    EndWith
    Return cString
    EndProc

  Protected Procedure export
    */ This method creates the buffer to pass to the GUID API.
    With This
      .Data1 = Space(4)
      .Data2 = Space(2)
      .Data3 = Space(2)
      .Data4 = Space(8)
    EndWith
    Return(This.Data1 + This.Data2 + This.Data3 + This.Data4)
  EndProc
  Protected Procedure Convert(cGuid)
    */ This method makes the call to the BinToHex that 
    */ converts the data in the 4 data properties from 
    With This
      cGuid =  .BinToHex(.Data1) + "-" + .BinToHex(.Data2) + "-" + ;
        .BinToHex(.Data3) + "-" + .BinToHex(.Data4)
      Return cGuid
    Endwith 
    EndProc
  Procedure Init
    */ Declare the function in the DLL
    Declare Integer UuidCreate ;
      In C:\Winnt\System32\RPCRT4.DLL String @ UUID
    Return
  EndProc
EndDefine

Output is produced as follows:

Disconnected/Persisted Recordsets

One of the most powerful features of ADO is the ability to create both disconnected and persisted recordsets. A disconnected recordset is a client-side recordset that does not have a current ActiveConnection. SQL data sources, such as SQL Server, Oracle, and so on, are licensed according to the number of concurrent connections. For example, the number of people that using an application connected to SQL Server is 300. However, it has been determined that at any time, only 50 users actually use the services of a connection. A connection is needed only when data is being requested, updates are made, or a stored procedure on the database server is invoked. From a financial standpoint, it is far less expensive for a company to only purchase 50 licenses than to purchase 300. From a resource standpoint, performance should improve because the server only has the overhead of 50 connections instead of 300, of which 250 are idle at any time.

Using the ADO recordset of customer data already created, the following code disconnects the client-side recordset:

oRecordSet.ActiveConnection = Null

If you attempt to do this with a server-side recordset, an error occurs stating that the operation is not allowed on an open recordset. Once the recordset is disconnected, you can continue to work with and modify records. The following code will work:

oRecordset.MoveFirst
Do While !oRecordset.Eof
   ?oRecordset.Fields("companyname").Value
   oRecordset.Fields("companyname").Value = ;
      Upper(oRecordset.Fields("companyname").Value)
   oRecordset.MoveNext 
EndDo

With modified records in a client-side recordset, three basic options exist.

Cancel local changes
Marshall local changes to the server
Save (persist) the recordset locally.

You can save (persist) the recordset locally for both later use and, ultimately, for marshalling those persisted changes back to the server.

The first choice is pretty simple to implement, since it takes one line of code:

oRecordset.CancelBatch

The second choice is also simple to implement. Much of the work in updating multiple records and transactions has already been detailed. This procedure really involves two separate steps:

Re-establish an active connection.
Marshal modified records back to the data source.

The following code re-establishes the connection:

With oConnection
   .Provider = "SQLOLEDB.1"
   .ConnectionString = "Persist Security Info=False;User 
      ID=sa;Initial Catalog=Northwind;Data Source=JVP"
   .Open
EndWith
oRecordset.Activeconnection = oConnection

Then the code marshals the records by attempting the updates

Local Err,cOldErr
cOldErr = On("error")
On Error Err = .T.
With oRecordset
   .ActiveConnection.BeginTrans
   .UpdateBatch
   If Err
      .ActiveConnection.RollBackTrans
      .CancelBatch
   Else
      .ActiveConnection.CommitTrans
   Endif
EndWith   
   On Error &cOldErr

Often, however, there's a need to shut things down and then reopen the recordset at another time. To be effective, the recordset must reflect incremental changes. This cycle may repeat any number of times.

To illustrate how to persist a recordset, consider again the following code that modifies records in a Recordset object:

oRecordset.MoveFirst
Do While !oRecordset.Eof
   ?oRecordset.Fields("companyname").Value
   oRecordset.Fields("companyname").Value = ;
      Upper(oRecordset.Fields("companyname").Value)
   oRecordset.MoveNext 
EndDo

Now you can invoke the Save method to persist the recordset:

oRecordset.Save("c:\temp\customers.rs")

At a later time, you can open the persisted recordset:

oRecordset = CreateObject("adodb.recordset")
oRecordset.Open("c:\temp\customers.rs")

After the persisted recordset is reopened, you can use the same code, which establishes a connection to a disconnected recordset, to make additional modifications. You can marshal changes made in the persisted recordset to the underlying data source.

Hierarchical/Shaped Recordsets

Visual FoxPro not only provides the ability to work with local data, but also the ability to set up relations using the Set Relation command. When you move the record pointer in the parent table, the record pointer automatically moves in any child tables that exist. This makes working with and building interfaces for one to many relationships very simple in Visual FoxPro. Fortunately, the same capability exists in ADO, in the form of hierarchical recordsets, also referred to as shaped recordsets.

There are two necessary components when creating and working with hierarchical recordsets:

The Microsoft DataShape OLE DB Provider, MSDataShape
The Shape language, a superset of the SQL syntax

The first requirement is fairly easy to fulfill because it only entails setting the Provider property of the ADO Connection object to the proper value:

oConnection.Provider = "MSDataShape"

The second requirement, using the Data Shape language, is a bit more challenging. When you first see Data Shape language, it can be fairly intimidating, just as FoxPro may have been when you first worked with it. But like anything else, with a bit of practice and patience, Microsoft Data Shape language will become second nature.

To examine Shape language, consider a parent-child common scenario of customers and orders. For each customer, zero or more orders can exist. In turn, each order can contain one or more line items. The following code employs Shape syntax to relate customers and orders in the SQL Server Northwind database:

SHAPE {SELECT * FROM "dbo"."Customers"} AS Customers APPEND ({SELECT * 
FROM "dbo"."Orders"} AS Orders RELATE "CustomerID" TO "CustomerID") AS 
Orders

If your first thought is, "Gee, this is like setting relations in Visual FoxPro," you are indeed correct. It is exactly the same principle. If the Shape syntax is broken down, the task becomes manageable. The first clause in the code begins with the keyword SHAPE, to signify that what follows is not pure SQL, but rather, Data Shape language. The Data Shape language is a super-set of SQL, which is why you need to use MSDataShape as the OLE DB provider. MSDataShape can interpret and execute Shape commands. Finally, the last portion of the first command specifies that the results of the SQL statement are to be aliased as Customers.

In the next set of commands, things get a bit complicated, especially when the hierarchy is nested an additional one or two levels (this is the case when order details are added, as we'll do in the next example).

You can interpret the keyword APPEND as "Append the results of the next SQL statement to the results of the previous SQL statement." Of course, just appending records won't suffice. Rather, you must provide a rule that specifies how the records are to be related. This is where the RELATE keyword comes into play.

You can interpret the RELATE keyword as, "When appending records, do so based on these join fields." In this case, the join is between the CustomerID column in the Customers table and the CustomerID column in the Orders table.

Finally, we need to alias the data that was just appended as Orders. The following code sets up the objects and creates the hierarchical recordset:

#Include adovfp.h
Local oRecordset,oConnection,oCommand, cShpStr
oRecordset = CreateObject("adodb.recordset")
oConnection = CreateObject("adodb.connection")
cShpStr = 'SHAPE {SELECT * FROM "dbo"."Customers"} AS Customers '
cShpStr = cShpStr + 'APPEND ({SELECT * FROM "dbo"."Orders"} ;
  AS  Orders '
cShpStr = cShpStr + 'RELATE "CustomerID" TO "CustomerID") AS Orders'
With oConnection
   .Provider = "MSDataShape"
   .ConnectionString = "Data Provider=SQLOLEDB.1;Persist Security ;
    Info=False;User ID=sa;Initial Catalog=Northwind;Data Source=JVP"
   .Open
EndWith
With oRecordset
   .ActiveConnection = oConnection
   .Source = cShpStr
   .CursorType = adOpenStatic
   .LockType = adLockBatchOptimistic
   .CursorLocation = adUseClient
   .Open
EndWith

The question at this point is, "How is the data appended?" The technique is rather clever. When you append a recordset to another recordset, you do so through a Field object. If you query the Count property of the Fields collection, you discover that the value of 12 is returned. However, in SQL Server, you see that the Customers table only has 11 fields. The twelfth field, in this case, is actually a pointer to the Orders recordset. The rows in the Orders recordset for a given row in the Customers recordset are only those for that customer. The following code illustrates just how powerful hierarchical recordsets are:

oRecordset.MoveFirst
Do While !oRecordset.Eof
   With oRecordset
      ?.Fields("Customerid").Value,.Fields("CompanyName").Value
   EndWith
   oOrders = oRecordset.Fields("orders").Value
   Do While !oOrders.Eof
      With oOrders
      ?Chr(9),.Fields("Customerid").Value,.Fields("orderdate").Value
      .MoveNext
      EndWith   
   EndDo   
   oRecordset.MoveNext
EndDo

With the basics of hierarchical recordsets out of the way, we can turn our attention to a more complicated, real-life example. The following example adds several dimensions to the recordset.

First, the Order Details table is appended to the Orders child recordset. In this case, a new field that will in turn point to the OrderDetails recordset, is added to the Orders recordset. The Products table is then appended to the OrderDetails recordset providing three levels of nesting. Appended to the Products recordset are two tables, Categories and Suppliers. Traversing up the hierarchy to the Orders recordset appends the Employees table.

This list illustrates the hierarchy and shows all the tables involved as well as the nesting scheme. When creating reports, it is quite possible that you will need all of these tables. The ability to relate tables in this fashion and the ability to display the data in a user interface or a report have always been true powers of Visual FoxPro. Before ADO, attempting all this work outside Visual FoxPro was extremely difficult, sometimes bordering on the impossible.

Customers

Orders

OrderDetails
   Products
      Categories
      Suppliers
Employees
   EmployeeTerritories
      Territories
         Region
Shippers

The following is the Shape syntax to create the hierarchical recordset:

SHAPE {SELECT * FROM "dbo"."Customers"} AS Customers APPEND (( SHAPE 
{SELECT * FROM "dbo"."Orders"} AS Orders APPEND (( SHAPE {SELECT * FROM 
"dbo"."Order Details"} AS OrderDetails APPEND (( SHAPE {SELECT * FROM 
"dbo"."Products"} AS Products APPEND ({SELECT * FROM "dbo"."Categories"} 
AS Categories RELATE 'CategoryID' TO 'CategoryID') AS Categories,({SELECT 
* FROM "dbo"."Suppliers"} AS Suppliers RELATE 'SupplierID' TO 
'SupplierID') AS Suppliers) AS Products RELATE 'ProductID' TO 
'ProductID') AS Products) AS OrderDetails RELATE 'OrderID' TO 'OrderID') 
AS OrderDetails,(( SHAPE {SELECT * FROM "dbo"."Employees"} AS Employees 
APPEND (( SHAPE {SELECT * FROM "dbo"."EmployeeTerritories"} AS 
EmployeeTerritories APPEND (( SHAPE {SELECT * FROM "dbo"."Territories"} 
AS Territories APPEND ({SELECT * FROM "dbo"."Region"} AS Region RELATE 
'RegionID' TO 'RegionID') AS Region) AS Territories RELATE 'TerritoryID' 
TO 'TerritoryID') AS Territories) AS EmployeeTerritories RELATE 
'EmployeeID' TO 'EmployeeID') AS EmployeeTerritories) AS Employees RELATE 
'EmployeeID' TO 'EmployeeID') AS Employees,({SELECT * FROM 
"dbo"."Shippers"} AS Shippers RELATE 'ShipVia' TO 'ShipperID') AS 
Shippers) AS Orders RELATE 'CustomerID' TO 'CustomerID') AS Orders

This is just about as complicated as it gets. Nobody in their right mind would want to hammer this code out manually. Fortunately, there is a visual way to build this code. The DataEnvironment designer that ships with Visual Basic allows you to visually design ADO connections, recordsets, and hierarchical recordsets. The following illustrates how this hierarchical recordset appears in the designer:

The extensive Shape syntax can be copied and pasted into Visual FoxPro, or any other environment that can host ADO. For complete details on how to use the DataEnvironment designer, consult the Visual Basic documentation on the MSDN CDs that ship with Visual Studio.

The following Visual FoxPro code traverses the hierarchical recordset and displays the data:

#Include adovfp.h
oRecordset = CreateObject("adodb.recordset")
oConnection = CreateObject("adodb.connection")
cShpStr = 'SHAPE {SELECT * FROM "dbo"."Customers"}  AS Customers APPEND'
cShpStr = cShpStr + '(( SHAPE {SELECT * FROM "dbo"."Orders"}  AS Orders '
cShpStr = cShpStr  + 'APPEND (( SHAPE {SELECT * FROM "dbo"."Order 
Details"}  AS OrderDetails '
cShpStr = cShpStr  + 'APPEND (( SHAPE {SELECT * FROM "dbo"."Products"}  
AS Products '
cShpStr = cShpStr  + 'APPEND ({SELECT * FROM "dbo"."Categories"}  AS 
Categories '
cShpStr = cShpStr  + 'RELATE "CategoryID" TO "CategoryID") AS 
Categories,'
cShpStr = cShpStr  + '({SELECT * FROM "dbo"."Suppliers"}  AS Suppliers '
cShpStr = cShpStr  + 'RELATE "SupplierID" TO "SupplierID") AS Suppliers) 
AS Products '
cShpStr = cShpStr  + 'RELATE "ProductID" TO "ProductID") AS Products) AS 
OrderDetails '
cShpStr = cShpStr  + 'RELATE "OrderID" TO "OrderID") AS OrderDetails,'
cShpStr = cShpStr  + '(( SHAPE {SELECT * FROM "dbo"."Employees"}  AS 
Employees '
cShpStr = cShpStr  + 'APPEND (( SHAPE {SELECT * FROM 
"dbo"."EmployeeTerritories"}  AS EmployeeTerritories '
cShpStr = cShpStr  + 'APPEND (( SHAPE {SELECT * FROM "dbo"."Territories"}  AS Territories '
cShpStr = cShpStr  + 'APPEND ({SELECT * FROM "dbo"."Region"}  AS Region '
cShpStr = cShpStr  + 'RELATE "RegionID" TO "RegionID") AS Region) AS 
Territories '
cShpStr = cShpStr  + 'RELATE "TerritoryID" TO "TerritoryID") AS 
Territories) AS EmployeeTerritories '
cShpStr = cShpStr  + 'RELATE "EmployeeID" TO "EmployeeID") AS 
EmployeeTerritories) AS Employees '
cShpStr = cShpStr  + 'RELATE "EmployeeID" TO "EmployeeID") AS Employees,'
cShpStr = cShpStr  + '({SELECT * FROM "dbo"."Shippers"}  AS Shippers '
cShpStr = cShpStr  + 'RELATE "ShipVia" TO "ShipperID") AS Shippers) AS 
Orders '
cShpStr = cShpStr  + 'RELATE "CustomerID" TO "CustomerID") AS Orders '
With oConnection
   .Provider = "MSDataShape"
   .ConnectionString = "Data Provider=SQLOLEDB.1;Persist Security 
Info=False;User ID=sa;Initial Catalog=Northwind;Data Source=JVP"
   .Open
EndWith
With oRecordset
   .ActiveConnection = oConnection
   .Source = cShpStr
   .CursorType = adOpenStatic
   .LockType = adLockBatchOptimistic
   .CursorLocation = adUseClient
   .Open
EndWith
Do While !oRecordset.Eof
   With oRecordset
      ?.Fields("CustomerID").Value,.Fields("CompanyName").Value
   EndWith
   oOrders =  oRecordset.Fields("orders").Value
   Do While !oOrders.Eof
      oShippers = oOrders.Fields("shippers").Value
      oEmployee = oOrders.Fields("employees").Value
      oEmployeeTerritories = 
oEmployee.Fields("employeeterritories").Value
      oTerritories = oEmployeeTerritories.Fields("territories").Value
      oRegion = oTerritories.Fields("region").Value
      ?"Order ID:  ",oOrders.Fields("orderid").Value,;
      "Order Date:  ",oOrders.Fields("orderdate").Value
      oOrderDetails = oOrders.Fields("orderdetails").Value
      ?"Territory:  ", 
oTerritories.Fields("territorydescription").Value,;
      "Region:  ",oRegion.Fields("RegionDescription").Value
      ?"Shipper: ",oShippers.Fields("companyname").Value
      oEmployee = oOrders.Fields("employees").Value
      With oEmployee
         ?"Employee: ",.Fields("employeeid").Value,;
         .Fields("firstname").Value + " " + .Fields("lastname").Value
      EndWith   
      ?"Order Details:  "
      Do While !oOrderDetails.Eof
         oProducts = oOrderDetails.Fields("Products").Value
         oCategories = oProducts.Fields("categories").Value
         oSuppliers = oProducts.Fields("suppliers").Value
         ?Chr(9),;
          oProducts.Fields("productname").Value,;
          oSuppliers.Fields("companyname").Value,;
          oCategories.Fields("categoryname").Value,;
          oOrderDetails.Fields("Quantity").Value,;
          oOrderDetails.Fields("UnitPrice").Value
          oOrderDetails.MoveNext
      EndDo
      oOrders.MoveNext
   EndDo   
   oRecordset.MoveNext
EndDo

The output appears as follows:

Because a hierarchy exists, the ability to create drill-down interfaces becomes a fairly simple task. The preceding Visual FoxPro code illustrates how to traverse the hierarchy.

Perhaps you want to use Microsoft Word or Excel as a reporting tool. With a combination of Visual FoxPro COM servers, ADO, and Automation, the process becomes manageable. The first and third parts of the solution have been around. However, only now that a set of COM objects exists to handle and work with data as Visual FoxPro does natively can the solution become a reality.

Hierarchical recordsets and recursive relationships

One of the nice features of SQL Server, and of most other server back ends is provision for recursive relations. The following is the SQL Server 7.0 database diagram for the Northwind database:

In the Northwind database, the Employees table employs recursion to support a manager/staff relationship. Both managers and staff are employees. In some cases, it happens that some employees report to other employees. In Visual FoxPro, you can create the same sort of relation by opening a table twice using two different aliases. In ADO, the task is totally supported and is quite easy to implement. The following is the Shape syntax:

SHAPE {SELECT * FROM "dbo"."Employees"}  AS Managers APPEND ({SELECT * 
FROM "dbo"."Employees"}  AS Staff RELATE 'EmployeeID' TO 'ReportsTo') AS 
Staff

The following Visual FoxPro code displays a list of managers and the staff that reports to each manager:

#Include adovfp.h
oRecordset = CreateObject("adodb.recordset")
oConnection = CreateObject("adodb.connection")
cShpStr = 'SHAPE {SELECT * FROM "dbo"."Employees"}  AS Managers '
cShpStr = cShpStr + 'APPEND ({SELECT * FROM "dbo"."Employees"} AS Staff '
cShpStr = cShpStr + 'RELATE "EmployeeID" TO "ReportsTo") AS Staff '
With oConnection
   .Provider = "MSDataShape"
   .ConnectionString = "Data Provider=SQLOLEDB.1;Persist Security 
Info=False;User ID=sa;Initial Catalog=Northwind;Data Source=JVP"
   .Open
EndWith
With oRecordset
   .ActiveConnection = oConnection
   .Source = cShpStr
   .CursorType = adOpenStatic
   .LockType = adLockBatchOptimistic
   .CursorLocation = adUseClient
   .Open
EndWith
Do While !oRecordset.Eof
   oStaff = oRecordset.Fields("staff").Value
   If oStaff.Recordcount > 0
      With oRecordset
         ?.Fields("firstname").Value + " " + ;
         .Fields("lastname").Value ,;
         .Fields("Title").Value
         Do While !oStaff.Eof
            With oStaff
               ?Chr(9),;
               .Fields("firstname").Value + " " + ;
               .Fields("lastname").Value ,;
               .Fields("Title").Value
            EndWith
            oStaff.MoveNext
         EndDo
      EndWith
   Endif
   oRecordset.MoveNext
EndDo

The output appears as follows:

Finally, note that hierarchical recordsets are updateable. The following code expands the previous example to illustrate how to make a simple update:

Do While !oRecordset.Eof
   oStaff = oRecordset.Fields("staff").Value
   If oStaff.Recordcount > 0
      With oRecordset
         Do While !oStaff.Eof
            With oStaff
               .Fields("firstname").Value = ;
                  Upper(.Fields("firstname").Value)
               .Fields("lastname").Value = ;
                  Upper(.Fields("lastname").Value)
               .Fields("Title").Value = ;
                  Upper(.Fields("Title").Value)
            EndWith
            oStaff.MoveNext
         EndDo
         */ Write changes to Staff recordset
         oStaff.UpdateBatch
      EndWith
   Endif
   oRecordset.MoveNext
EndDo

The ability to view related records, coupled with the ability to make updates, places the ADO hierarchical recordset capability on par with similar capabilities in Visual FoxPro.

Multiple recordsets

Use of hierarchical recordsets represents only one method for returning data from multiple recordsets in one object. For starters, building hierarchical recordsets is not the most straightforward of propositions. In many cases, a simpler alternative may be all that is required.

Consider the case where you need a specific customer record and the orders for that customer. Yes, you could use a hierarchical recordset. But, there is a simpler way: run two SQL statements.

Some OLE DB providers can process multiple SQL Statements. The OLE DB Provider for SQL Server has this capability. Attempting to do this with Visual FoxPro tables via the OLE DB Provider for ODBC will not work.

When using this technique, you have two choices on where the logic exists to perform the task. One choice is to build the SQL on the client and pass it to the server through a Command object. The other choice is to invoke a stored procedure on the database server through a Command object. I'll illustrate both techniques. The Command object will be discussed in detail later in this paper.

To illustrate the stored procedure method, the following stored procedure must be created on the SQL Server Northwind database:

CREATE  PROCEDURE CustomerAndOrders @CustomerID nchar(5)
AS
Select * From Customers Where Customers.CustomerID = @CustomerID
Select * From Orders Where Orders.CustomerID = @CustomerID

With the stored procedure created, the following code will create the recordset:

#Include adovfp.h
oConnection = CreateObject("adodb.connection")
oCommand = CreateObject("adodb.command")
With oConnection
   .Provider = "SQLOLEDB.1"
   .ConnectionString = ;
      "Persist Security Info=False;User ID=sa;Initial
        Catalog=Northwind;Data Source=JVP"
   .Open
EndWith
With oCommand
   .CommandText = "CustomerAndOrders"
   .ActiveConnection = oConnection 
   .CommandType = adCmdStoredProc 
EndWith
oCommand.Parameters("@CustomerID").Value = "ALFKI"
oRecordset = oCommand.Execute
Do While !oRecordset.Eof
   ?oRecordset.Fields(1).Value
   oRecordset.MoveNext
EndDo
oRecordset = oRecordset.NextRecordset
Do While !oRecordset.Eof
   ?oRecordset.Fields(0).Value
   oRecordset.MoveNext
EndDo

Like any recordset, the recordset just produced can be navigated. Once the first set of records from the Customers table have been navigated, the NextRecordset method is invoked. This causes the recordset produced by the second SQL statement to become available. Thus, the next set of commands loops through the records from the Orders table. This technique is ideal in those situations where you may need to populate Combo or ListBox controls.

The previous example references a collection that has not been discussed yet, the Parameters collection. The Parameters collection and the individual Parameter objects that it contains serve several purposes. One purpose is to provide the capacity to create parameterized queries. Another purpose is to provide the ability to send arguments to, and return data from, a stored procedure. For more information on the Parameters collection, see the Command Object section of this paper.

Alternatively, you can produce the SQL on the client if you wish. The following code illustrates the difference:

With oCommand
   .CommandText = "Select * From Customers Where CustomerID =
      'ALFKI'" + Chr(13) + "Select * From Orders Where CustomerID =
      'ALFKI'"
   .ActiveConnection = oConnection 
   .CommandType = adCmdText 
EndWith
oRecordset = oCommand.Execute

The same result is achieved. The difference lies in how the result is achieved.

Which approach is better?

It depends on what your requirements are. The first option, which uses stored procedures, is more secure; the code is set and you can assign permissions with regard to who can execute the stored procedure. The second option provides more flexibility, but less security.

Fabricated recordsets

Up to this point, recordset objects have been presented in the context of origination from an ADO connection. In many cases, you may want to create an ADO recordset with data that does not come from a data source, just as you may in some cases use the Create Cursor command in Visual FoxPro. For example, you may have an application that works with a small amount of data, such as an array or Visual FoxPro cursor. Perhaps you need to dynamically build a table structure. Whatever the reason, the ability to create ADO recordsets from scratch is powerful.

To illustrate this capability, consider the need to fetch a list of files from a specified directory. In Visual FoxPro, a handy function, ADIR( ), performs this sort of task. However, what if you need to pass the data to another application? Or, perhaps you need to persist the list to a file on disk. While Visual FoxPro arrays are powerful, ADO recordsets provide a compelling alternative. The following code fetches a list of files from a specified directory, fabricates a recordset, and copies the values from the array into the newly created recordset:

*/GetFiles.prg
#INCLUDE "adovfp.h"
Local Array aFiles[1]
Local nFiles,nField,nFile,oRS
nFiles = Adir(aFiles,Getdir( )+"*.*")
oRS=Createobject("adodb.recordset")
With oRS
.CursorLocation=ADUSECLIENT
.LockType=ADLOCKOPTIMISTIC
*/ Adding new fields is a matter of appending
*/ new field objects to the Fields Collection. 
.Fields.Append("File",ADCHAR,20)
.Fields.Append("Size",ADDOUBLE,10)
.Fields.Append("DateTime",ADDBTIME,8)
.Fields.Append("Attributes",ADCHAR,10)
.Open
EndWith
For nFile = 1 To nFiles
   */ Add a new record. This automatically makes
   */ the new record the current record - just
   */ like VFP.
   oRS.AddNew
   With ors
      .Fields("File").Value = aFiles[nFile,1]
      .Fields("Size").Value = aFiles[nFile,2]
      .Fields("DateTime").Value = ;
        Ctot(Dtoc(aFiles[nFile,3]) + " " + aFiles[nFile,4])
      .Fields("Attributes").Value = aFiles[nFile,5]
   EndWith
Next nItem
Return oRS

With the new recordset created and populated, it can be navigated like any other recordset:

oFiles = GetFiles ( )
Do While !oFiles.Eof
   ?oFiles.Fields("File").Value
   oFiles.movenext
EndDo

ADO recordsets instead of arrays

Referring to the previous example, let's say that the list needs to be sorted by file size, descending. Arrays in Visual FoxPro can be sorted, when all columns in the array are of the same data type. In this case, there are three data types: Character, Numeric, and DateTime. With a client-side ADO recordset, the process becomes simple. The following code does the trick:

oRS.Sort = "Size Desc"

Sorts are not limited to just one column. Perhaps you need to sort by size, descending, and then by file, ascending:

oRS.Sort = "Size Desc,File"

And, when it comes to sorting, such properties as Bookmark and AbsolutePosition that have already been demonstrated are available here as well.

Perhaps you need to find a specific value. The ASCAN( ) function in Visual FoxPro enables you to do this. However, it does not allow you to specify a particular column to search. Rather, once the first occurrence of a specified value is found, regardless of the column, the search is stopped. With ADO recordsets, more granular control is provided. The following code checks to see if a file called VFP6.EXE is in the recordset:

oRS.Find("File Like 'VFP6.EXE'")
If !oRS.Eof
   */ Found it
Else
   */ Not found
Endif

Finally, you may wish to filter the list based on the file size being greater than a specified value:

oRS.Filter = "size > 50000"

When evaluating the tools at your disposal for local data handling, be sure to consider fabricated ADO recordsets. Also, if you find yourself running into obstacles with Visual FoxPro arrays, fabricated ADO recordsets may provide a sound alternative.

Command Object

ProgID: ADODB.Command

The purpose of the Command object is just as the its name implies, to run commands. For example, you may need to run a SQL update against a SQL Server table. To illustrate, the following code applies a 10 percent increase in the UnitPrice field in the Products table of the SQL Server Northwind database:

oCommand = CreateObject("adodb.command")
With oCommand
   .ActiveConnection = oConnection
   .CommandText = "Update Products Set unitprice = unitprice * 1.1"
   .Execute
EndWith

The ActiveConnection property

To review, both the Command object and Recordset object have the ActiveConnection property. A Command object needs to know what data source it is to execute commands against. A Recordset object needs to know what data source contains the data it is to retrieve. The way you accomplish this is by setting the ActiveConnection property.

The ActiveConnection property presents a great opportunity to talk about the flexible nature of the ADO object model. The ADO object model is very flat, in that you do not have to create a series of objects in order to gain access to other objects. For example, the following is one way to create and open both a Connection and a Recordset object:

oConnection = CreateObject(""adodb.connection"")
oRecordset = CreateObject(""adodb.recordset"")
With oConnection
   .Provider = ""SQLOLEDB.1""
   .ConnectionString = ""Persist Security Info=False;User 
    ID=sa;Initial Catalog=Nothwind;Data Source=JVP""
   .Open
EndWith
With oRecordset
   .ActiveConnection = oConnection
   .Source = ""Products""
   .Open
EndWith

Here is another way to create the two objects:

oRecordset = CreateObject(""adodb.recordset"")
With oRecordset
   .ActiveConnection = ""Provider=SQLOLEDB.1;Persist Security 
    Info=False;User ID=sa;Initial Catalog=Northwind;Data Source=JVP""
   .Source = ""Products""
   .Open
EndWith

Now, you can reference the Connection object because it has been implicitly created from the passed connection string:

?oRecordset.ActiveConnection.ConnectionString

The same is true for the Command object. While a Command object was not explicitly created, a Command object was in fact created and actually did the work of creating the recordset. Using the recordset just created, the following command will yield "Products" as the CommandText:

?oRecordset.ActiveCommand.CommandText

Which method should you use?

It is really a matter of preference. The latter method, which uses only the RecordSet object, is somewhat overloaded. It carries the same overhead as the former method because you must still create a Connection object. The former method is probably a better way to go as it makes for more readable code.

Parameters collection

The Parameters collection works with the Command object. The primary use of the Parameters Collection is to both pass arguments to, and accept return values from stored procedures. To illustrate, consider the CustOrderHist stored procedure in the SQL Server Northwind database:

CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND 
OD.ProductID = P.ProductID
GROUP BY ProductName

To illustrate how the Parameters collection is used in conjunction with the Command object, consider the following comprehensive example:

First, you need to establish a valid connection:

oConnection = CreateObject("adodb.connection")

Next, the connection needs to be opened.

With oConnection
   .Provider = "SQLOLEDB.1"
   .ConnectionString = "Persist Security Info=False;User 
    ID=sa;Initial Catalog=Northwind;Data Source=JVP"
   .Open
EndWith

With a valid, open connection, a Command object can be prepared:

With oCommand
   .ActiveConnection = oConnection 
   .CommandText = "CustOrderHist"
   .CommandType = adCmdStoredProc && adCmdStoredProc = 4
EndWith

At this point, information can be obtained from the Parameters collection:

For Each Parameter in oCommand.Parameters
   ?Parameter.Name,Parameter.Size,Parameter.Type
Next Parameter

The first Parameter object is reserved for the value that the stored procedure may return. Regardless of whether the stored procedure explicitly returns a value, this Parameter object will be created. Examining the CustOrderHist stored procedure, note that a single argument, a customer ID, is accepted.

With a Command object and Parameter object in place, the real work can begin. To get things rolling, a value needs to be assigned to the Parameter object that will in turn be passed to the stored procedure. In this case, a SQL statement is executed that totals the quantity, by product, that a specified customer has purchased. The following code provides a customer ID and executes the stored procedure:

oCommand.Parameters("@CustomerID").Value = "ALFKI"
oRecordset = oCommand.Execute

Yet another way to produce a Recordset object is through the execution of a stored procedure. The resulting Recordset object contains two fields that correspond to the select statement in the CustOrderHist stored procedure. Need a different history? Just update the Value property of the Parameter object and invoke the Execute method of the Command object.

The Parameters collection also comes into play in the area of parameterized queries. Consider the following SQL Statement:

Select * ;
   From Customer ;
   Where country = ? And max_order_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.connection")
oCommand = CreateObject("adodb.command")

Next, the connection needs to be established:

oConnection.Open("northwind","sa","")

For illustration purposes, the OLE DB 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
   .ActiveConnection = 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,adParamInput,1," "))

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 = adParamInput
   .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 = "U%"
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").Value = "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 = 1
   */ 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"
oRecordset.Open
?oRecordSet.Fields.Count && number of fields in customers table
oRecordset = oRecordset.NextRecordSet
?oRecordSet.Fields.Count && number of fields in orders table

In this case, the OLE DB Provider for SQL Server can return multiple recordsets. If you attempt the same thing with the OLE DB Provider for ODBC, which you need to use when accessing Visual FoxPro data, you will receive an error message stating that the requested action is not supported by the OLE DB provider.

Another example involves the way in which the Properties collection deals with the location of a Recordset object. Recordsets can either exist locally as client-side recordsets or they can exist remotely as server-side recordsets. Client-side recordsets, as will be discussed shortly, have several capabilities that server-side recordsets do not have. One of these abilities is to create indexes. The following code creates a client-side recordset:

oRecordset = CreateObject("adodb.recordset")
oConnection = CreateObject("adodb.connection")
With oConnection
   .Provider = "SQLOLEDB.1"
   .ConnectionString = "Persist Security Info=False;User 
    ID=sa;Initial Catalog=Northwind;Data Source=JVP"
   .Open
EndWith
With oRecordset
   .Cursorlocation = adUseClient && adUseClient = 3
   .ActiveConnection = oConnection
   .Source = "Products"
   .Open 
EndWith

Now, lets create an index on the ProductName field using the following code:

oRecordSet.Fields("productname").Properties("optimize").Value = .T.

In the absence of a declaration of where a Recordset object should reside, the Recordset object, by default, resides on the server. Attempting to reference the Optimize property results in an error stating that the specified property could not be found in the collection.

While the ADO interface is constant, depending on the provider you use, the capabilities may be very different. Be sure to consult your provider's documentation.

Remote Data Services

One of the most powerful data access capabilities introduced by Microsoft is Remote Data Services (RDS). Although a separate set of objects exists for RDS, RDS is really just another component for use with ADO. There are two ways you can implement RDS.

Use the same ADO objects described in this paper
Use the RDS data control

Let's discuss the RDS data control option first, since it represents some uncharted territory.

The RDS Data Control

The following code creates an instance of the RDS data control:

oRDSDataControl = Createobject("rds.datacontrol")

Once the data control is created, only three properties need to be populated: Server, Connect, and SQL.

With oRDSDataControl
   .Server = "http://jvp"
   .Connect = ;
    "Remote Provider=SQLOLEDB.1;database=northwind;User ID=sa;"
   .Sql = "Customers"
EndWith

Because we're using the SQL Server OLE DB Provider, the SQL property can consist of just the table name. The following code retrieves the same recordset, but does so with the OLE DB provider for ODBC:

With oRDSDataControl
   .Server = "http://jvp"
   .Connect = "dsn=northwind;uid=sa;pwd=;"
   .Sql = "Customers"
EndWith

Whenever possible, you should use a native OLE DB provider rather than the OLE DB provider for ODBC.

With the RDS data control properties set, you can create a recordset. Invoke the Refresh method to accomplish this, as in the following code:

oRDSDataControl.Refresh
oRecordset = oRDSDataControl.Recordset

From this point on, you can work with the recordset the same way you work with any other ADO client-side recordset:

Do While !oRecordset.Eof
   orecordset.Fields(1).value = ;
      Proper(orecordset.Fields(1).value)
   oRecordset.Movenext
EndDo
oRecordset.Updatebatch

Alternatively, you can replace the last line of code with a call to the SubmitChanges method of the RDS data control:

oRDSDataControl.SubmitChanges

Implementing RDS Through the ADO Interface

You can invoke RDS by using the same ADO Connection object discussed above. As with hierarchical recordsets, the first step involves the selection of an OLE DB provider. In this case, the MSRemote provider is required. The following code sets up the Connection object:

oConnection = CreateObject("adodb.connection")
With oConnection
   .Provider = "MS Remote.1"

   .ConnectionString = "Remote Server=http://jvp;Remote 
         Provider=SQLOLEDB.1;database=northwind;User ID=sa;Pwd=;"
   .Open
EndWith

The ADO ConnectionString property supports only four arguments. The first two, Provider and File Name, have already been discussed. The third and fourth, Remote Provider and Remote Server, are used by the RDS in the example above. The Remote Provider is the same OLE DB provider used when you create local connections. The additional parameters that specify the database, user ID, and password are used by the OLE DB Provider for SQL Server that in turn is located on the remote server. The following code connects the Recordset object and Connection object and with one difference, is basically the same as the previous examples in this paper:

With oRecordset
   .ActiveConnection = oConnection
   .Source = "Customers"
   .LockType = adLockBatchOptimistic
   .Open
EndWith

The only difference is that properties such as CursorLocation and CursorType are omitted since all recordsets created through RDS must exist on the client. Additionally, all client-side recordsets are static types. If you like, you can still specify the properties explicitly. Any incompatible properties will be coerced to a valid value. For example, if you specify the CursorType to be a ForwardOnly cursor and you specify the recordset exists on the client, when the Open method is fired, ADO forces the cursor type to be static. The same is true if you specify the CursorLocation to be on the server and you use the MSDataShape provider. Since all hierarchical pecordsets must exist on the client, the CursorLocation is coerced to the proper value.

Summary

The goal of this paper has been to provide you with a fairly comprehensive overview of both ADO and RDS from the perspective of Visual FoxPro applications. Note that ADO is not a replacement for the Visual FoxPro Cursor Engine. Rather, regard it as another tool at your disposal. Both Visual FoxPro cursors and ADO recordsets have their relative strengths and weaknesses.

ADO is ideal in situations where your application is component based, or in situations where you need to pass data to other applications such as Excel in automation operations. Fabricated ADO recordsets can provide an interesting alternative to arrays when more robust data handling requirements are necessary.

For most local data handling operations however, Visual FoxPro cursors will usually provide better results.

John V. Petersen, MBA, is president of Main Line Software, Inc., based in Philadelphia, Pennsylvania. John's firm specializes in custom software development and database design. He is a Microsoft Most Valuable Professional and has spoken at many developer events, including Visual FoxPro Developers Conference, FoxTeach, the Visual FoxExpress Developer's Conference, DevDays, and TechEd. In addition, John has written numerous articles for FoxTalk and FoxPro Advisor. John is co-author of Visual FoxPro 6 Enterprise Development and Hands-on Visual Basic 6—Web Development, both from Prima Publishing. John's latest project is the ADO Developer's Handbook, from Sybex Publishing, due September 1999.

E-mail: jpetersen@mainlinesoftware.com

You can reach Main Line Software at http://www.mainlinesoftware.com.