Accessing Visual FoxPro Databases with ColdFusion

 
Dec 09, 2005

by Russel Madere

Note: This has been tested with a Windows installation of ColdFusion only. It has not been tested with a Linux install. The test environment is MS Windows XP Professional running IIS and ColdFusion MX 7 for the web server and Windows 2003 server for the database/file server. A Linux application of this may be possible, but has not been tested.

Accessing a FoxPro database from a ColdFusion application offers several unique problems. These are compounded when the database is stored on a network resource like normal ODBC data sources. However, these problems can be overcome.

The first issues to address are the lack of a native driver for FoxPro in ColdFusion and the way FoxPro stores the data. FoxPro is a direct descendant of dBase, which stores each table as a separate physical file rather than grouping tables in a single file like Access or MS SQL. Additionally, FoxPro databases may have a master database file or use a free database directory. Whoever created the FoxPro database will know what storage method it uses.

To address the lack of a native ColdFusion driver, a System DSN is created in the Windows Data Sources (ODBC) applet. You'll need to create a separate DSN for each FoxPro database because of FoxPro's storage methods. The administrator needs to know what method is used to properly configure the DSN.

As seen in Figure 1 below, to configure the DSN, one inputs the name and description, the database storage type, the file location and some driver specific information: the desired collating sequence, whether to select exclusively, select nulls and deleted and whether to fetch the data in the background. The defaults, as shown, are usually sufficient for this. Exclusive select is not available when a Free Table Directory is used.


Figure 1: ODBC Visual FoxPro Setup

Once the Windows DSN is created, a data source can be created in the ColdFusion Administrator. Enter the Data Sources section of the administrator and create a new data source using the ODBC Sockets Driver. Select the FoxPro System DSN as the ODBC DSN. A user name must be entered, but it has no effect on the data retrieval. Everything else is optional. These settings can be seen in Figure 2.


Figure 2: FoxPro Data Source Configured in ColdFusion Administrator

The data source can be tested with a simple CFQUERY and CFDUMP in a page. If the page returns the error that the driver cannot find the FoxPro table file, the most likely problem is that the ColdFusion server does not have permissions to access files. Test this by using a CFDIRECTORY call against the source folder with a CFDUMP. If nothing is listed, the directory security is the problem. If directory contents are listed, verify that the file exists and that permissions on the file match permissions on the directory.

To resolve the directory security issue, one needs to allow the "ColdFusion Application Server" and the "ColdFusion ODBC Server" services to act as a user and to have permissions to the directory in which the files reside. If the directory exists on a network resource, the ColdFusion server services needs to run as a specific user, not as SYSTEM as is the default. To change this, see the ColdFusion TechNote "Running ColdFusion as a Specific User" at http://www.macromedia.com/go/tn_17279.

As you can see, setting up a FoxPro database from ColdFusion is not as hard as one may think. While the process is more complicated than a simple Access or MS SQL data source, the issues are not insurmountable. They can be addressed by properly configuring the data source in Windows and the ColdFusion Administrator, and by configuring the user permissions for the server.
Russel Madere is the Webmaster and Internet Coordinator for The SunShine Pages (www.sunshinepages.com), a New Orleans area independent telephone directory publisher. He has been a web developer for nearly a decade and a ColdFusion developer for over 8 years. Additionally, he has experience in project management and was a FORTRAN developer prior to turning to the web.

Add a Comment
(If you subscribe, any new posts to this thread will be sent to your email address.)
  
Privacy | FAQ | Site Map | About | Guidelines | Contact | Advertising | What is ColdFusion?
House of Fusion | ColdFusion Jobs | Blog of Fusion | AHP Hosting