Visual foxpro datasource?

[m.ardito]

Hi,

I need to support an old accounting application that is based on visual foxpro .

This datasource is readable on windows with the VFP odbc driver set to read DBF files in a folder, but I need to get data from those DBF-tables (with cdx files) in Teiid, of course.

In the past I used a csv/dbf jdbc driver to read some other DBF file (see Read .dbf format files ) but those were probably a different DBF kind, and that jdbc in Teiid can't open the VFP files, apparently (I get an exception related to "DANS DBF Library error").

Also, I can open those VFP DBF files with libreoffice 4.x calc, but not with microsoft excel 2010.

Is there any other (best if free) good, tested way to get (readonly is enough) that data in Teiid?

Since I don't really need high performances on this (I can materialize tables later) I also thought to bypass this issue either:

• using SQL linked servers to access that VFP through ODBC drivers, and then query those linked servers with jtds JDBC in Teiid.
  This works in squirrelsql, although the only syntax that SQL server recognizes is like
  
  "SELECT * FROM openquery(linkedservername, 'select a, b, c FROM remote.table') as rt"
  
  which doesn't seem work in Teiid, of course, when the same SQL server is connected... I don't know if it's possible...

• using an MSaccess database which links that VFP through ODBC drivers, and then query those linked servers with Ucanaccess JDBC (I used that also in the past, see Re: How to create datasource connection for ODBC (MS Access) in teiid server , though I can't remember if linked tables were available...). I still didn't test this.

• as last resort, also making a windows schedule to periodically replicate those tables in mssql, through local ODBC, so that I can read them with jtds JDBC...

Anyone knows the best approach, or has a better solution, or can suggest a free/cheap but reliable JDBC driver that can allow to reach those VFP tables in Teiid?

Any help is appreciated...

[rareddy]

You could not find JDBC driver for this?

Download Foxpro JDBC Driver | Foxpro Diagram Designer: DbSchema

Relational Junction dBase JDBC Driver

[m.ardito]

Thanks, I already googled for that, of course.

Are you suggesting them since you know/tried them, or just as they are the only matching results?

DbSchema is offering a jdbc driver from HXTT (HXTT DBF JDBC Drivers ), which is commercial (350$ single server) as is the other one (599$ single server).

But I always used (and would prefer) either an open source driver, or an official driver, but it seems they both don't exist for this data source, I would trust better those...

Also, even if trial versions will work well, I'll have to convince my employer to buy, which could be difficult...

I always have troubles asking to buy something outside Europe... even online, through credit cards or paypal accounts.

Sometime they are even reluctant to buy on Amazon Marketplace... because not all vendors always comply with local fiscal rules, iirc.

This is (also) why, as I wrote above, even a "workaround" would fit in my planned usage: I feel this data is not particularly important, atm, as my "internal customer" initially asked me to replicate those tables into SQLserver using a windows scheduled job... maybe once a day, or even less frequently . I could do just that, and read the data coped into SQL server.

I am just trying to make this data a bit more "live" using a somewhat more direct access, even in a suboptimal way. And, as always happens, even if they "undervalue" this data source, once it's in place, they could (will) want more... if that happens, then the jdbc path could be unavoidable, and justified... meanwhile, my life is harder .

I know it could seems silly, but this is how I have to work here, every day... .

Thanks, anyway.

[van.halbert]

Have you looked at this jdbc type 4 compatible driver [1].   Unfortunately, its not open source, but we had someone use this drive to get the simple ETL capabilities for migrating their data, along with a data integration tool (just throwing their comment out there). 

[1] http://www.caigen.com/index.html

van

[m.ardito]

Thank you, it is also less expensive, I will keep that in mind...

although this web site does not even show a company address... let alone a VAT number...

http://caigen.com/contactus.html  just two email address, and the site seems to be dated 2011...

I doubt that we could purchase this, for the same reasons...

another possible solution was the jdbc-odbc bridge but that was deprecated and later unsupported by java 8....

Let's see what happens next... thx