How can I access Visual FoxPro tables from Delphi?

1
votes

I've got a legacy collection of DBF, FPT and CDX files left from file organizing software. Judging from CDX and FTP suffixes, I presume it is a Visual FoxPro database. From the first glance into DBF file, it looks like it has field descriptors there. I'd like to examine what data stored there and use or extract any useful stuff.

00000000:  31 0B 06 0E-2F 00 00 00-08 02 28 01-01 00 00 01  1/   (  
00000010:  00 00 00 00-00 00 00 00-00 00 49 40-01 7F 00 00            I@
00000020:  4E 41 4D 45-00 00 00 00-00 00 00 43-01 00 00 00  NAME       C
00000030:  FF 00 00 00-00 00 00 00-00 00 00 00-00 00 00 00   
00000040:  43 41 54 5F-52 4F 4F 54-49 44 00 42-00 01 00 00  CAT_ROOTID B 
00000050:  08 00 04 00-00 00 00 00-00 00 00 00-00 00 00 00   
00000060:  50 52 4E 54-5F 49 44 00-00 00 00 42-08 01 00 00  PRNT_ID    B
00000070:  08 00 04 00-00 00 00 00-00 00 00 00-00 00 00 00   
00000080:  41 54 54 52-49 42 53 00-00 00 00 49-10 01 00 00  ATTRIBS    I
00000090:  04 00 04 00-00 00 00 00-00 00 00 00-00 00 00 00   
000000A0:  49 53 5F 45-58 43 4C 55-44 45 00 49-1A 01 00 00  IS_EXCLUDE I
000000B0:  04 00 04 00-00 00 00 00-00 00 00 00-00 00 00 00   
000000C0:  43 41 54 5F-49 44 00 00-00 00 00 42-1E 01 00 00  CAT_ID     B
000000D0:  08 00 0C 00-00 00 00 00-00 00 00 00-00 00 00 00   
000000E0:  54 53 00 00-00 00 00 00-00 00 00 37-26 01 00 00  TS         7&
000000F0:  08 00 14 00-00 00 00 00-00 00 00 00-00 00 00 00

What I tries so far:

  • upgrading out-of-box Windows 7 ODBC driver stub to version 6.01.8629.01

  • creating User DNS as "Free Tables Directory" and pointing its path to the file collection

  • no tables are listed for that OBDC DNS in variety of tools, like SQL Explorer and SQL Server Import/Export Wizard.

  • manual attempt to SELECT * FROM any DBF file gives me [Microsoft][ODBC Visual FoxPro Driver]Not a table. error.

Also, I tried:

  • creating BDE alias with FOXPRO driver
  • opening any of DBF files in Database Desktop

Both attempts to open a table resulted in Corrupt table/index header. error.

Also, I just tried tDBF component but it looks abandoned and loaded with Kylix Libc dependencies thus not compiles in Delphi w/o fixing that.

Tons of questions:

  • How do I confirm what files are really Visual FoxPro?
  • Does symptoms described above indicate real data corruption or just incompatibility/misconfiguration?
  • And finally, how to use something modern to access tables and start examining data?
3
databasedelphifoxprodbfvisual-foxpro
In relation to the second point - the VFP ODBC driver will open Visual FoxPro and any other version right back to (probably) DOS products like dBase and Clipper - so if it's choking like that it's more likely to be either not actually DBF format, or corrupt as opposed to a config problem. I'm looking at where the field descriptors start there, and to me that's wrong for DBF, they should come later. – Alan B
@Alan B, from what I piece together the field descriptor should start at byte offset 32 which seems to be the case above. (I am no DBF expert) – Lars
It looks like it, structure explained here. Quickest check is to open the .dbf with excel. – Sertac Akyuz
@Sertac Akyuz +1 for Excel, sweet! – Lars

3 Answers

3
votes

Q: How do I confirm what files are really Visual FoxPro?

Based on your dbf data dump it would seem to be Visual FoxPro. The first byte has a value of 0x31 which is Visual FoxPro, autoincrement enabled according to Microsoft's MSDN documentation of the .dfb Table file structure http://msdn.microsoft.com/en-US/library/st4a0s68(v=vs.80).aspx

Additionall information about the file extensions you mention:

  • .dbf = Data for fixed-length fields.
  • .cdx = Compound index.
  • .fpt = Data for memo fields, images and objects.

Q: Does symptoms described above indicate real data corruption or just incompatibility/misconfiguration?

Sorry cannot help you here...

Q: And finally, how to use something modern to access tables and start examining data?

I’ve previously managed to access dfb files by using Delphi’s ADO (dbGo) components. I downloaded the FoxPro OLE DB Provider that is included in ”Microsoft Access Database Engine 2010 Redistributable” http://www.microsoft.com/en-us/download/confirmation.aspx?id=13255.

An initial connection string looks as follows:

Provider=VFPOLEDB.1;Data Source=[MY_FOXPRO_DIR_PATH];Password="";Collating Sequence=MACHINE

When I study my code I have used a much longer one and unfortunately don't have any notes why. I'm throwing it in anyway:

Provider=VFPOLEDB.1;Data Source=[MY_FOXPRO_DIR_PATH];Mode=Share Deny None;Extended Properties="";User ID="";Mask Password=False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE;DSN="";DELETED=True;CODEPAGE=1252;MVCOUNT=16384;ENGINEBEHAVIOR=90;TABLEVALIDATE=3;REFRESH=5;VARCHARMAPPING=False;ANSI=True;REPROCESS=5
0
votes

Why don't you try to find a copy of Visual Fox Pro, version 9, and see if it will open the files. (The UniversalThread has a VFP section and VFP experts where you can ask the question as well.)

If you can open it in VFP, you can simply copy it to a .CSV or tab-delimited file and you should be able to import it into whatever system you want.

Sometimes VFP tables (.DBF, .CDX, .FPT) are linked to a "data base" (kind of container), which expands their field names and adds things like triggers, etc. Look for a .DBC file somewhere. Or they can be free-standing. I'm not sure what happens if you try to open a VFP table what was part of a DBC (data base container) and you don't have the container available.

0
votes

How do I confirm what files are really Visual FoxPro?
dbf extension and the first byte h31 indicates that it is a Visual FoxPro Table.

Does symptoms described above indicate real data corruption or just incompatibility/misconfiguration?
The Microsoft ODBC driver has some problems with VisualFoxPro

And finally, how to use something modern to access tables and start examining data?
Best method in my opinion is to use the Apollo database engine from Vista Software. http://www.apollodb.com/default.asp