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
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?"
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 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:
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.
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 http://www.microsoft.com/data/.
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.
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.
In order to work through the examples presented in this paper, you will need the following:
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:
This section has several comprehensive examples on strategies you may employ when integrating ADO into your Visual FoxPro Applications.
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
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:
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:
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:
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
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 http://www.microsoft.com/data/mdac2.htm
In addition, I highly recommend ADO 2.0 Programmers Reference, by David Sussman and Alex Homer, from Wrox Press.
You can create four types of recordsets in ADO:
Recordset objects can exist in either of two locations, the server or the client:
The most common properties you are likely to use with ADO recordsets include the following:
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:
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.
There are four different locking schemes in ADO recordsets. These locking schemes are similar to those in Visual FoxPro.
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.
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:
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:
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 http://www.microsoft.com/data/mdac2.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
If you opened the Visual Basic IDE
Now, whether you are in the VBA Editor or the VB IDE
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.
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
Or
oRecordset.Open("Customers",;
oConnection,;
adUseClient,;
adLockBatchOptimistic)
For Visual FoxPro:
With oRecordset
.ActiveConnection = oConnection
.Source = "Customer"
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.CursorLocation = adUseClient
.Open(,,,,adCmdtable)
EndWith
Or
oRecordset.Open("Customer",;
oConnection,;
adUseClient,;
adLockBatchOptimistic,;
adCmdTable)
Or
With oRecordset
.ActiveConnection = oConnection
.Source = "Select * From Customer"
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.CursorLocation = adUseClient
.Open
EndWith
Or
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 = ""
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*'")
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:
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:
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.
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:
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.
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 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.
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.
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.
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.
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:
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
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.
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.
Let's discuss the RDS data control option first, since it represents some uncharted territory.
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
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.
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.