Reading Visual FoxPro DBF Data

[briansms]

Hi, I'm currently evaluating TOS for a possible TIS subscription and we do a lot of work with Visual FoxPro databases and tables, migrating data to SQL Server, Oracle, etc. I can successfully make an ODBC connection and load the schema into the Metadata DB Connections node. All of the table schemas came in, however when trying to read the data, I get nothing but errors when trying both tDBInput and tFileInputXbase. Is there a different way DBF/Xbase tables should be handled?
- Brian

OS is Windows 2000 Pro SP 4, TOS 2.4.0RC1_r13964
tDBInput Component - Using the ODBC connection in the local repository
Opening the SQL Builder from the component properties shows all of the schema and db structure, but when trying to do a simple Select * from Equip I get:
Error Executing SQL dialog Option value changed
Trying to just use a tLogRow component to output the rows during a Trace results in the following error:
Starting job EquipToAssets at 10:36 21/05/2008.
Exception in component tDBInput_1
java.sql.SQLException: Data source name not found and no default driver specified
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(Unknown Source)
connecting to socket on port 5161
connected
at sun.jdbc.odbc.JdbcOdbcConnection.initialize(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at mmrca.equiptoassets_0_1.EquipToAssets.tDBInput_1Process(EquipToAssets.java:1206)
at mmrca.equiptoassets_0_1.EquipToAssets.runJobInTOS(EquipToAssets.java:2815)
at mmrca.equiptoassets_0_1.EquipToAssets.main(EquipToAssets.java:2724)
disconnected
Job EquipToAssets ended at 10:36 21/05/2008.

[Anonymous]

Hi Brian,
I see you are using version 2.4RC1. I tried the same in version 2.3.2. And I have no problem.
I have a little DBF file. I downloaded my FoxPro ODBC driver form:
http://msdn.microsoft.com/en-us/vfoxpro/bb190233.aspx
Then from the ODBC data source administrator I made a System ODBC connection to Access dBase III source. Everything works great! I can make a Metadata connection and then pull data correctly.
From your screen shot, the only difference is, seems like you are missing the data source parameter. The ODBC name. Please take a look at my screen shot.
Also what version of dBase are you using? III, IV, V?
Brian, if you like, you can upload a sample of your DBF file, and I can take a look at this on my machine.
Thanks,
Parham
Talend Technical Group

[briansms]

Hi Parham,
I agree that the screen shot is missing that parameter, but I've tried this so many ways I've lost count, and adding it makes no difference. At the time of that screen shot, I was executing a query on-the-fly in the SQL Builder, so there I should see be able to retrieve sample data rows, right? All I can do is drill through the table schema. One other disturbing thing that I just noticed is that although the schema was retrieved, none of the field lengths are populated, which is also not good.
Unfortunately I updated my TOS 2.3.2 to 2.3.3 r13892, but I just went back and tried doing the same schema retrieval and SQL Builder query in 2.3.3 using each of the other FoxPro, VFP and dBase drivers listing in my ODBC Administrator and still none worked.
Note that the screen shot that you attached is not using the VFP ODBC driver for your DBF3, but rather the Access dBASE one, which I don't have. I checked my main Windows XP box which has VFP 6, VFP 9 and Access 2003 installed and it doesn't even have that Access dBase driver. Not sure where you got that, but I don't think it came from the VFP ODBC download you referenced. The Windows 2000 box that I'm running TOS in already has the drivers, they come with the 2000 and XP OS (see attached image).
I'm not using dBase, but rather Visual FoxPro 6 and Visual FoxPro 9, both free tables and database containers. In many instances dBase V drivers would also work, but again, so should the Microsoft Visual FoxPro drivers. I've never had a problem with any other product using the VFP drivers.
Do you forsee OLEDB support in the future? That would support all of the newer VFP data formats, which has included varchars for several years now.
- Brian

[Anonymous]

Brian,
we are aware of the bug with the Metadata wizard where the field lengths are not being retrieved correctly. We will make a bugtracker topic for this and will be most likely fixed in the next minor release.

[Anonymous]

I had not seen this topic before, but last week I was at customer offices and they had to read dBase files (and it should be compatible with FoxPro as far as I understood). So I updated 104, it may be useful.

[briansms]

Hi Pierrick,
Parham brought your component to my attention since I've been in touch with him regarding this topic. Unfortunately we've standardized all of our projects on Java. But if we ever do try out Perl, we'll give your component a try. I do appreciate the option though.

Thanks,
- Brian

[Anonymous]

tFileInputXBase was one the first contributed component : 3, and jefmony had made it compliant with both Java and Perl. I've emailed him to ask him if he can make his component compliant with Talend Open Studio 3.0.

[Anonymous]

Parham, plegall, Brian I just read through 4922 and I have a question: If the product from Pentaho can read the DBF file could that component be used here? I am not familiar with the architecture enough to determine whether what they have can be used and if so how, but it is an idea. Thanks.
Gábor

[briansms]

Hi Gábor,
Talend can read the DBF file data using the Generic ODBC connector, it's just that it doesn't read the schema properly from the Metadata Manager > Retrieve Schema. Field lengths, decimal precision and nullable flag are not brought in. It appears to be a bug with the Metadata Manager and I've talked to Talend about it. If you make a connection, drag over a table to create a tDBInput component, use Guess Schema, the values do get read. See 6184 for some new details. You'll still have to make the DB Type to TalendType mappings in the mapping_MsOdbc.xml file, otherwise everything comes in as a String (which isn't always a bad thing).
Note however, your DBF has to be defined with only field data types supported in VFP 6 or earlier; char instead of varchar, numeric instead of double, etc. The newer VFP 9 types are not supported, but that is no fault of Talend, it is simply because Microsoft stopped support of the ODBC driver back in '99 after VFP 6 was released.
BTW: I evaluated a few ETL solutions prior to adopting TIS, including Pentaho. I found the Talend products to be more flexible, intuitive and easier to use.

- Brian

[Anonymous]

Thank you, Brian,
For my immediate needs I am using Access as an intermediary to read the (one) DBF file . In that way I can pull in the results of a query from Access and since it is a query result set the data has already gone through some processing. It is probably my limitation, but I could not dump any data out of my DBF file via ODBC.
In any case I just thought to bring the solution from Pentaho to the attention of the people interested. Since Pentaho is also an open source project maybe those who understand what is going on under the hood can get some ideas from their solution.
I checked out their suite and it is interesting, but you are right the Talend ETL is easier to use and it also looks nicer to look at.
Thanks again,
Gábor