Access Visual FoxPro database from Java

Question

I've been trying to access a Visual Fox Pro database from Java for a week now. I'm getting desperate because my project is in a very tied budget and timeframe. (As any other project, I guess, hahaha)

I have a .dbf, a .cdx and a .fpt files. I need to be able to look for a record, extract data, and update data. I don't have a VFP licence.

I hope someone has some pointers or a working example that I can use. If the only way is buying a driver I'll be willing to consider it based on suggestions received.

This is a short description of what I have tried.

I found this xBaseJ: java objects to read and write to dBase files. But it doesn't suport CDX index files.

Also found this Example to access a dbf. But my tests show this exception: java.sql.SQLException: [Microsoft][Controlador ODBC dBase] La tabla externa no tiene el formato esperado. I guess a translation to english of this error could be something like: java.sql.SQLException: [Microsoft][ODBC dBase Driver] External table is not in the expected format

Because of that error I think I need a newer driver. In this microsoft page say they no longer have an ODBC driver, and everybody should use OLE DB Provider. The problem is I haven't found a way to use it from java.

As far as I can tell, there is no way to do it directly. Found here someone talking about a JACOB "thingy", and someone comments about the need to create a C++ or C# "something" to be able to do what I need. I think he is talking about The JACOB Project: A JAva-COM Bridge. But I also don't know what COM calls would actually have to make.

Haven't been able to find a suitable JDBC driver.

Thank you all.

Ely.

Flamsteed

Answer 1

I found the way to do it.

I ended up using JACOB (from here <= upd 2021-10-25: now here).

1. Downloaded and installed Visual FoxPro OLE DB driver from http://msdn.microsoft.com/en-US/vfoxpro/bb190232 (upd 2021-10-25: links there do not work anymore). the direct link is: Microsoft OLE DB Provider for Visual FoxPro 9.0 SP2 (upd 2021-10-25: does not work anymore)
2. Downloaded JACOB version 1.17 binary and source code.
3. Added jacob.jar as a Java Build Path library
4. Copied jacob-1.17-x86.dll to the project directory (same place as eclipse's .project file)
5. Copied jacob-1.17_src\jacob-1.17\samples\com\jacob\samples\ado contents to my src directory and:
6. Removed the ms directory
7. renamed test.java to Main.java
8. Changed the connection string to: String connectStr = "Provider=vfpoledb;Data Source=C:\\path\\to\\Data\\;Collating Sequence=general;";
9. Changed the queryStr to match one of the dbf's name

Hope this helps someone else.

Flamsteed

Hi, how have o managed to use the provider? I get the message that not suitable driver was found for the provider. – Wagers

I just installed the driver as described in step 1. Nothing special. – Flamsteed

Which windows version are you running? I'm running windows 7 64 bit – Wagers

I'm using Windows XP in a virtual machin (VMWare) – Flamsteed

I've implemented your solution, and thanks about the idea, it works. But it is not so fast and in my application I have to do frequent queries and the memory grows really fast. I have to restart it every hour. – Rollin

if i able to use jacob jar what could be the dirver classname? – Emboss

Sorry. Don't know – Flamsteed

Answer 2

Since the jacob approach did not work anymore as described and others seemed to work without the OLE DB bridge, I cancelled trying there and successfully did it via hxtt.com drivers:

1. register with some email and the mailed link contains some password to get to the drivers page
2. there one can download the proper driver, e.g. v42 till Java 8 or v43 since Java 9
3. use the following JDBC settings in your env (e.g. Eclipse Database Explorer) to access your DBF files (e.g. you have some C:\foo\bar.dbf):
   • driver jar: e.g. DBF_JDBC42_tillJava8.jar
   • driver class: com.hxtt.sql.dbf.DBFDriver
   • URL: jdbc:dbf:/c:/foo/
   • (if required by your tool with dummy values:)
     • database: db
     • user/pass: usr / <empty pass>

Otoscope

I can't say if it supports cdx and/or fpt files – Otoscope

Answer 3

You may find interesting to use a jdbc driver for foxpro files. I made some quick tests with this one, and it almost looks like you are using a real database in Java.

Regards,

Apoplexy

Great to know. When I have some time to spear, I'll give it a try. I was looking for something like that for days, :) – Flamsteed

Unfortunately it does not suport CDX index files. – Home