Advantage Developer Zone


Using Visual FoxPro Data

Wednesday, October 8, 2008

With the release of Advantage Version 9 support for FoxPro version 9 tables has been added. This allows developers who have applications using the newer features available in FoxPro tables, such as nulls, varchars and autoincrement fields, can now access these tables through Advantage. A conversion utility, dbcconvert.prg, reads an existing DBC and creates an Advantage Data Dictionary (ADD) which provides additional functionality for Advantage-based applications.

Converting Visual FoxPro Databases

Visual FoxPro databases are created by defining a database container (DBC) which is linked to the DBF tables. This database container also includes definitions for views, triggers and stored procedures. Although Advantage cannot directly access the database container, there is a conversion utility which will read the database container and create an Advantage Data Dictionary with the same metadata.
The conversion utility is a FoxPro program (prg) which you can modify to suit your needs. It currently has the ability to export view definitions, referential integrity rules, default field values, field and table validation rules, and primary keys.

There are a few conditions that must be met in order to convert relations and views. Primary keys used in FoxPro defined relations must be defined with the .NOT.Deleted() filter expression. FoxPro also allows Views to contain an ORDER BY clause which is contrary to the ANSI standard for views and not allowed by Advantage. DBC Views with an Order By will not be imported into the Data Dictionary.

Using the Conversion Utility

To use the utility simply open Visual FoxPro and open the database you wish to convert. Once the database is opened you can run dbcconvert.prg. The dbcconvert.prg file is included with the Advantage OLE DB Provider and requires the Advantage Database (Remote) Server.

Accessing the Data

After the conversion utility is run you will have an Advantage Data Dictionary with the same name as the DBC you converted (ex. converting Books.dbc will create Books.add) without changing the original DBC. You open the newly created Data Dictionary just like any other Advantage Data Dictionary.

To access the FoxPro tables with both a native FoxPro application and an Advantage-based application use COMPATABILITY Locking. This allows the tables to be opened by both mechanisms at the same time. Remember to open the tables in SHARED mode from your FoxPro application.

The screenshot below shows the same table open using Visual FoxPro 9 and Advantage Data Architect at the same time.


The addition of Visual FoxPro table support brings a host of new functionality to Advantage. The dbcconvert utility provides a quick way to get started by creating the initial data dictionary based on the original DBC. This allows a way to access legacy FoxPro data with other applications through Advantage. For example, you could extend the data out to the Internet with an ASP.NET application which connects to the data using the Advantage .NET Data Provider.

Visual FoxPro applications can also use the Data Dictionary created from the original DBC to access the data using the Advantage Remote Server. This can extend the life of FoxPro applications by providing a Client/Server solution.