ogr_fdw odbc access to Foxpro

[Linn111]

Sorry, Issue got submitted before text was completed. Please close issue #36.

I am running Windows 7 64-bit, but have posgresql -32-bit installed. I installed ogr_fdw from http://www.postgresonline.com/downloads/fdw_win32_94_bin.zip (not sure who compiled this version for Windows).

I have successfully connected to an old Microsoft Visual Foxpro database using a 32-bit odbc DSN. This same database attaches just fine via odbc to a Microsoft Access 2002 front end. It also appears to work when attached to postgresql via ogr_fdw, except that there seems to be some strange datatype mismatching going on.

In the postgresql examples below flag2 is specified as flag2 varchar(1)and fid is specified as fid integer. These definitions match the specifications in the Visual Foxpro database. When I run the following query, I get a null result, which is incorrect.

Testdb=# SET client_min_messages = debug; SET Testdb=# Select fid, descint, flag1, flag2 Testdb-# from "table$" where flag2 = 'N' and fid = 1; DEBUG: OGR SQL: (flag2 = 'N') AnD (fid = 1) fid | desc_int | flag1 | flag2 -----+----------+-------+------- (0 rows)

But if I augment the field variables flag2 and fid in the SQL query (probably causing an automatic internal data cast to char and integer, respectively) it works correctly:

Testdb=# Select fid, descint, flag1, flag2 Testdb-# from "table$" where '' || flag2 = 'N' and fid + 0 = 1; DEBUG: OGR SQL: fid | desc_int | flag1 | flag2 -----+----------+-------+------- 1 | ? | N | N (1 row)

These two queries should return identical results.

A straightforward CAST operation doesn't work:

Testdb=# Select fid, descint, flag1, flag2 Testdb-# from "table$" where cast (flag2 as varchar(1)) = 'N' and Testdb-# cast (fid as integer) = 1; DEBUG: OGR SQL: (flag2 = 'N') AnD (fid = 1) fid | desc_int | flag1 | flag2 -----+----------+-------+------- (0 rows)

I always get a null result with any data type unless I force a "cast" to the variable(s) through a null operation.

Any Ideas?

[robe2]

@Linn111 I'm the one that built those binaries. Though not sure the issue you are having is the compile rather than something else in ogr.

Does something like:

SELECT * FROM "table_$" LIMIT 10;

work? Just checking to see if its just a filter issue.

Also if you could provide sample FoxPro tables that would help. Which version of Foxpro are you using? are these connected to a .dbc file or free standing dbfs? Sorry it's been a long time since I've worked with FoxPro so my terminology might be a bit off.

There is also in that zip package a file ogrinfo.exe -- you should be able t use that to run the same query and see if it outputs anything or behaves the same -- that will narrow it down to an issue with ogrfdw or gdal itself.

Thanks, Regina.

[Linn111]

Regina,

Thanks for responding.

Unfortunately I removed PosgreSQL from my computer as it was not working out for me for other reasons (no update capability, yet). so I can't do the test you suggested.

The FoxPro files were generated by "The Master Genealogist" (TMG), a genealogy program based on VPF, so I don't have VFP either, just the files. I am currently accessing the VFP database files via MS Access using the MS VFP ODBC driver, and that works well. TMG has been discontinued and I am looking for an open source approach to preserve my genealogy data in the future. Also, I can query the same files via PyODBC with no issues (but PyODBC does not provide a database of its own). The DBF files themselves seem pretty solid.

I think the VFP file are from VFP version 6, but I am not sure. They are free standing .dbf's with indexes, and there is no .dbc file.

Thanks. Sorry I couldn't be of mere help.

John

---

From: Regina Obe [mailto:notifications@github.com] Sent: Tuesday, September 15, 2015 2:02 PM To: pramsey/pgsql-ogr-fdw Cc: John Linn Subject: Re: [pgsql-ogr-fdw] ogr_fdw odbc access to Foxpro (#37)

@Linn111 https://github.com/Linn111 I'm the one that built those binaries. Though not sure the issue you are having is the compile rather than something else in ogr.

Does something like:

SELECT * FROM "table_$" LIMIT 10;

work? Just checking to see if its just a filter issue.

Also if you could provide sample FoxPro tables that would help. Which version of Foxpro are you using? are these connected to a .dbc file or free standing dbfs? Sorry it's been a long time since I've worked with FoxPro so my terminology might be a bit off.

There is also in that zip package a file ogrinfo.exe -- you should be able t use that to run the same query and see if it outputs anything or behaves the same -- that will narrow it down to an issue with ogrfdw or gdal itself.

Thanks, Regina.

Reply to this email directly or view https://github.com/pramsey/pgsql-ogr-fdw/issues/37#issuecomment-140502945 it on GitHub. https://github.com/notifications/beacon/ANac1WClDHAzyY914yhj6QuflDEig6f7ks5 oyGKhgaJpZM4F1dKh.gif

[robe2]

John,

Oh well good luck with finding an alternative genealogy software. Have you looked at webtrees, one of my friends uses that and I think she likes it Looks like it imports GEDCOM format (which sounds like TMG can export) and has MySQL backend (grudgingly, which I guess at least is relational.)

https://www.webtrees.net/index.php/en/

I haven't tried ogr with anything but the DBase IV format of Dbase family. So maybe I'll try later with newer Dbase. For Dbase stuff, I generally don't bother with ODBC because the GDAL shape /dbf driver reads them directly. Not sure if you were using ODBC or the Shp driver.

[Linn111]

Regina,

Looks like it imports GEDCOM format

Were it that easy. TMG was so good because it contains a lot of additional information that cannot be exported to GEDCOM. Plus I have written several custom reports using MS Access that read the .dbf directly. And I now add and edit some information by directly updating the .dbf files. TMG doesn't know the difference so I can use both at the same time. So my first choice SQL doesn't support ODBC or OLE DB (that I know of), Postgresql supports reading (sort of, I could make it sork) but not updating, MS SQL Server Express supports reading and writing, but breaks unmercifully when thing go wrong (and is not Open Source), and pyODBC has no database of its own so no migration path, and the Access 2002 that I am using is unsupported. Sigh...

I am using the OCDB driver as that is what MS Access likes and it works.

But thanks.

John

---

From: Regina Obe [mailto:notifications@github.com] Sent: Tuesday, September 15, 2015 4:28 PM To: pramsey/pgsql-ogr-fdw Cc: John Linn Subject: Re: [pgsql-ogr-fdw] ogr_fdw odbc access to Foxpro (#37)

John,

Oh well good luck with finding an alternative genealogy software. Have you looked at webtrees, one of my friends uses that and I think she likes it Looks like it imports GEDCOM format (which sounds like TMG can export) and has MySQL backend (grudgingly, which I guess at least is relational.)

https://www.webtrees.net/index.php/en/

I haven't tried ogr with anything but the DBase IV format of Dbase family. So maybe I'll try later with newer Dbase. For Dbase stuff, I generally don't bother with ODBC because the GDAL shape /dbf driver reads them directly. Not sure if you were using ODBC or the Shp driver.

Reply to this email directly or view https://github.com/pramsey/pgsql-ogr-fdw/issues/37#issuecomment-140552149 it on GitHub. https://github.com/notifications/beacon/ANac1ebCpFfmD_jNOdEVrsTcSAya3uwPks5 oyITKgaJpZM4F1dKh.gif

[robe2]

Suggest to close this one out too as not enough info.

[robe2]

I think you should just close this one. Not enough info to do anything about it. i also can't test easily except under 32-bit because there is no 64-bit ODBC driver for Dbase or Foxpro. Besides for accessing dbase, I'd just use the shapefile driver.