Coldfusion 8 & Foxpro Datasources

I’ve just spent the last 2 days trying to get a CF8 datasource connection to a FoxPro dbf table. FoxPro?? Some of you might have to look that up. Basically it’s an antiquated database system based on dBase (some more of you may have to look that up). Each table in a FP db is contained in a separate file

ie:
tableOne.dbf
tableTwo.dbf

unlike most recent db’s which have all the tables contained in a single file

ie:

myDB.mdb
tableOne
tableTwo

As you might expect, trying to get a brand new app like CF8 and an antique like FP to communicate is a bit like trying to put a saddle on a Ferrari; you can do it but it’s not pretty.

This current project involves getting UPS tracking numbers out of the UPS Connect software which is (you guessed it) FoxPro based. The software has the ability to export tracking number to a csv but it’s a manual process and I don’t want my users to be doing this. It’s also not appropriate as I need the tracking numbers as soon as they are created and batch processing just won’t cut it.

My initial approach was to try and set up a MSSQL linked server using the FP table but after almost a day I gave up. I was able to create the linked server but any time I tried to open the table I received an error

OLE DB error trace [Non-interface error: CoCreate of DSO for MSDASQL returned 0x80040154].
Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider ‘MSDASQL’.

According to several posts this is likely a permissions problem. Based on my reading I set the Connect folder full permissions to everyone, I restarted both MSSQL and SQLAgent services logged on as admin, created TMP and TEMP environment variables on the SQL server machine…and probably some things I have forgotten about but to no avail.

Being under some time pressure I decided to set up the CF datasource using the ODBC socket. I had initially decided not to go that route as I didn’t install ODBC Server components when I did the CF8 install. If you choose not to install there’s no easy way to add it later (ie: via add/remove programs) You either have to do a complete uninstall/reinstall or use the procedure outlined in http://kb.adobe.com/selfservice/viewContent.do?externalId=kb402637&sliceId=1 which is actually a work around for a bug in the CF8 installer.

There is a bug in the install script of CF8 (earlier versions at least) which will prevent the install of the ODBC server software. This occurs if you opted to install the ODBC server but didn’t install the documentation (which you should never do on a production machine).

Follow the procedure under “Download procedure” making sure to change the line

adminObj.login(“admin”);
to
adminObj.login(“yourCFAdminPassword”);

If you don’t you will get an error The current user is not authorized to invoke this method.

Check to see  you’ve got the ODBC Server services up and running by Start > Run > services.msc

The next step depends on whether the FoxPro file is located on the CFServer machine or on a network share. In most cases the file will be on a share but if not you can skip this bit. If not we need to set up CF in a particular way so we can set up the Datasource.

Because CFServer service is installed by default to run under LocalSystem, CF will have no access to network resources. People often encounter this when trying to use cffile, cfexecute, cfdirectory or any of the verity tools. The solution is to set up the CFService to run under a “network user”.  More on that in a bit.

Running as LocalSystem is not normally an issue when setting up a connection to something like MSSQL or MySQL. The driver handles the connection and it doesn’t matter what context the service is running under. However, with FoxPro, you are making a different kind of connection; one that requires file level permissions to access the table, just as you would need with cffile et. al. I haven’t tested it but I expect this would be the case with any file over ODBC such as Access or Excel.

To set up the Coldfusion Server service do the following:

  1. Create a standard user in Active Directory (call it CFService)
  2. Select a very strong password and Set Password To Never Expire
  3. Right Click on the directory you want CF to have access to
  4. Select Properties > Security Tab
  5. Click Add
  6. Enter CFService in the box and click OK
  7. Click CFService in the Group Or User Name Window
  8. Check the appropriate permissions (usually Read & Execute, List Folder Contents and Read))
  9. Click Apply and OK
  10. Open services.msc as admininstrator (either logged on or use RunAS)
  11. Right Click Coldfusion 8 ApplicationServer > Properties  and select LogOn Tab
  12. Check This Account and enter the user name as yourDomain\CFService
  13. Enter CFService password twice
  14. Click OK
  15. Restart Coldfusion 8 Application Server service

Now this gets you to the point where you can use cffile et. al on network resources (provided you give CFService access to those dirs) however there’s still more to do to get a Datasource set up in CFAdmin

We now need to set up a System DSN using the MS ODBC Administrator.  Start Menu > Run > ODBCAD32 when logged on as administrator or Administrative tools > Datasources (ODBC) using RunAS

UPDATE: Check out the comments for info on 64bit windows systems and 32 bit ODBC drivers (like VFP)

Click the System DSN tab and Click ADD

Scroll through the list of drivers until you find Microsoft Visual FoxPro Driver (*If it is not listed you can get it here http://msdn.microsoft.com/en-us/vfoxpro/bb190233.aspx You may need to restart your system after installing and at minimum you will need to reopen the ODBC Administrator.). Select it and Click Finish. Enter the Data Source Name (description is optional) For my situation (UPS Connect) I used Free Table Directory.  To select the path make sure you use a UNC path and not a mapped drive. I created a share on the target machine with the Connect software called ups so my path is

\\192.168.111.6\UPS

I did see some references to Fetch data In background (click the Options button) causing problems with earlier versions of CF so I unchecked it. Click OK and you should have a new System DSN set up.

You can now (finally) fire up CFAdmin to add your new Fox Pro datasource. On the Data Sources page, Enter your desired DSN and select ODBC Socket as the driver and click add.

Select your newly created ODBC DSN from the drop down.  Leave the username as system and password blank. (the Foxpro tables are not pwd protected)

Click Show Advanced Settings and enter the following in the Connection String box

Driver={Microsoft Visual FoxPro Driver}; SourceType=DBF; SourceDB={pathToDB}; Exclusive=No;

or in my case

Driver={Microsoft Visual FoxPro Driver}; SourceType=DBF; SourceDB=\\192.168.111.6\UPS; Exclusive=No;

You can now verify your datasource and all is seemingly ready to go.

if you run the following query

<cfquery name=”test” datasource =”upstracking”>
select * from shipnday
</cfquery>
<cfdump var=”#test#”>

you get an error

Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Visual FoxPro Driver]File ‘shipnday.dbf’ does not exist.

Turns out that you also need to change the Coldfusion 8 ODBC Server Log On credentials to  that of an account other than LocalSystem (ie: our CFService “user”) Make the changes in the Log On tab as you did above for the Application Server service and restart the ODBC Server service and viola… Tracking numbers from a foxpro database on a network share!

Note that in the query you don’t use the dbf extension even though that’s the name of the table/file. The driver takes care of this for us (I assume)

Whew… That was a long post (especially for a first one) but it represents a couple of days work and I decided to put it up here for others (and in fact for myself since I’ve got to do this all over again on the production server when I’ve finished developing this current project)

Advertisements

11 Responses to Coldfusion 8 & Foxpro Datasources

  1. Nick says:

    It should be mentioned that this procedure only works on 32-bit systems. 64-bit versions of Windows do not have an ODBC System DSN for FoxPro at all.

    • sidfishes says:

      Thanks for mentioning that and while you are right that there is not a 64 bit vfp driver, you should (from my reading) be able to run the 32 bit bit administrator and install a 32 bit driver. I don’t have a 64bit system however the 32bit admin should be in SysWoW64 directory.

  2. Nick says:

    You are absolutely right. I was able to get ColdFusion 8 to see the ODBC driver under 64-bit Windows 2008 by first installing the latest VFP ODBC driver, and then running %windir%\SysWOW64\odbcad32.exe and adding the System DSN. Hope this helps someone out there. ColdFusion is 32-bit standard-edition.

  3. pcelba says:

    Just to slightly correct your “antique visions of FoxPro”…

    The latest Visual FoxPro release is dated February 2009 and DBF format is widely spread, so we may suppose it will be here for another 10+ years.

    I am not saying VFP has no limitations but it is still better, faster, more reliable, and more developer friendly than Access. To have each table in separate file could even be an advantage because you have no data size limit.

    Everything depends on MSFT, of course, and the end of VFP is coming but I know two development groups working on their own DBF data management software.

  4. Brett Bruschke says:

    Wondering if you have any experience connecting to a foxpro database that is stored on a remote machine. I too ran through the frustration of getting CF 8 and FoxPro to communicate when both were on the same network, but now I need to move the CF Server offsite. It’s a whole new world of frustration for me. Any tips would be appreciated.

    • JayB says:

      Fortunately for me, no. I think the procedure I ended up with can only work internally as you need to create an odbc system connection. Don’t think that’s viable with a remote server. Good Luck.

  5. Nick says:

    Brett, the only way to make ColdFusion talk to a Foxpro ODBC datasource on another computer is to have the ColdFusion Application service run as a user who is authorized to access the share with the .dbf files on the remote end. Accessing the shares means having Windows file sharing rights. This usually requires a VPN if the files are located on another network.

    • JayB says:

      Good point Nick. Yes, a VPN set up would work if both end points were on the same domain or in a domain trust situation (I think) so the CF “user” could access the files. The only other consideration would be chatter across the vpn. Not sure what impact a CF/FP odbc connection would have on the connection. My -guess- is that it would be OK, especially with maintain DB connections off, but you’d have to really test for performance & network impacts.

  6. Brett Bruschke says:

    @ Nick, @ JayB

    Well, I have actually been able to successfully connect to a database on a remote machine. At least, I think I have, using a product called HXXT JDBC DBF or something like that. I am still needing to test it with some DBF files that are truly off-site, and I am also having issues writing back to the DBF files. However, it is looks promising. If either of you have DBF files on your machines and you would like to work with me to test this then let me know. I think the writing issues I am facing are indeed permission issues, but I find it interesting that I can read the data without permission problems.

  7. Paul says:

    This blog post saved me a whole lot of work — thanks! I can now access a 14-year-old collection of .dbf files from 64-bit CF 9.

  8. JayB says:

    That’s great Paul. Glad to have been able to help.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: