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...