dbMigration with odbc // curiosity // solved?

FFF · Post by **FFF** » Sat Mar 20, 2021 10:08 am

Guys,
for syncing dbf with Postgres tables i use dbMigration, a sub tool of Database.net.
Usually that's all great, but now i found some peculiarity: it reads via odbc a series of dbfs as source - the tool lists all tables in the folder, some preselected for processing, as given by the commandline - but one table is missing. The table is physically there, is used by my apps, it even exists a copy in the folder, which IS shown.
The odbc connection uses the MS VisualFoxpro driver. I even made another user conn, using another version of the driver on my machine - the result is exactly the same.

In the developer's opinion, the odbc driver is at fault, but i can't imagine, what and why the problem exists at all.
Anyone an idea?

wriedmann · Post by **wriedmann** » Sat Mar 20, 2021 10:33 am

Hi Karl,
if you build a small VO sample app using this ODBC driver (the StdMDI SQL Application should be enough): do you see your table?
Wolfgang

FFF · Post by **FFF** » Sat Mar 20, 2021 2:18 pm

Hi Wolfgang,
thx, didn't know, this sample exists

-
built it, run, selected the datasource, "open a slq-table" - lists the dbfs in the folder, but not that dratted one.
Opened explorer, made a copy of the file, and made a copy of the pre-version (which was corrupt) - the latter is shown, while the first is invisible like its parent.
There's no problem at all opening it with the rdd/dbserver.
Strange...

wriedmann · Post by **wriedmann** » Sat Mar 20, 2021 2:37 pm

Hi Karl,
maybe this table has a field that is not valid for FoxPro?
Wolfgang

FFF · Post by **FFF** » Sat Mar 20, 2021 2:49 pm

8 fields, each "character", length 2, 4, 5 and 60...

FFF · Post by **FFF** » Tue Mar 30, 2021 7:00 pm

Guys,
to bring this to a conclusion: just tried and let cmVODBX make a "safe copy" of my problem dbf, what basically makes a new dbf using the struc and copy recordwise.
And behold, this one gets listed by odbc.

Ok, compared both of them with WinMerge and there's ONE difference, the first four hex values in the header are
07 15 02 10 in the "problem" and
03 15 03 1E in the working version

Had had a look to file format definitions, where 0x03 means FoxBASE+/Dbase III plus, no memo (which is correct, this file has no memo field)
but i find no info for 0x07 in the first byte

As all my checks seem to work fine with the copy, i'm solved - but nevertheless, would like to understand what went wrong here.

robert · Post by **robert** » Tue Mar 30, 2021 10:22 pm

Karl,
First byte 0x07 is VO Ansi DBF. And first byte 0x87 is VO Ansi DBF with DBT.
When you create a DBF with the DBFNTX driver in VO and when you have SetAnsi(TRUE) then you get this type of file.
X# can read/write that.
See https://github.com/X-Sharp/XSharpPublic ... s.prg#L105 for a list of types that X# knows about.
Robert

FFF · Post by **FFF** » Wed Mar 31, 2021 8:12 pm

Robert,
thx for this. All of my dbfs are CDX, all created with VO many moons ago. The ones with Memo are "0xF5", i.e. Foxpro2 (!)? The memo-less ones 0x03, i.e. "FoxBaseDBase3NoMemo".
Slightly confusing, the whole thing, especially, why 0x07 isn't seen by odbc 3.00...

robert · Post by **robert** » Wed Mar 31, 2021 8:17 pm

Karl,

FFF wrote:Robert,
Slightly confusing, the whole thing, especially, why 0x07 isn't seen by odbc 3.00...

I do not think that the ODBC driver recognizes NTX files as well.

Robert

mainhatten · Post by **mainhatten** » Tue Apr 06, 2021 10:16 pm

FFF wrote:In the developer's opinion, the odbc driver is at fault, but i can't imagine, what and why the problem exists at all.
Anyone an idea?

As the original problem is already solved, let me add some vfp view:
DO NOT USE VFP ODBC DRIVER IF YOU CAN CODE AROUND IT.
The vfp ODBC driver has not been updated (IIRC from version 6) to keep in synch with newer features and additions.

Code depending on it might work for long stretches, but then you need to access a table sporting one of the benefits added later, for instance BINARY index, and boom - you are out of luck and have to code around it, ask for other table or import format - things we dislike to do as it runs against DRY.

OLE DB as access technology much safer, either in old COM/ActiveX surroundings as well as in ADO.Net - if the tool you use needs an ODBC driver (might be Linux based), use one from Advantage (from Advantage 10 or higher), it will work much further with newer vfp table features.

If everything fails, fire up vfp as a COM object, access table in native vfp and pass the info back as stream, XML, JSON or create a file format to import in a cursor.

Edit: see
http://fox.wikis.com/wc.dll?Wiki~VFPODBCdriver
for more details, like field types VarChar, AutoInc under "Can't" header...

regards
thomas

Main menu