Skip site navigation (1) Skip section navigation (2)

Visual FoxPro, PostgreSQL, and Dates (Long)

From: Avery Payne <apayne(at)pcfruit(dot)com>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Visual FoxPro, PostgreSQL, and Dates (Long)
Date: 2006-08-25 20:28:45
Message-ID: 44EF5D7D.8040002@pcfruit.com (view raw or flat)
Thread:
Lists: pgsql-odbc
I'm in the process of extending a FoxPro app that can pump data via ODBC 
(v8.2.2) from an accounting package based on FoxPro (.dbf files) into 
PostgreSQL 8.1.  I'm aware of the different dbf-to-postgresql tools out 
there but none of them suit my purpose, and the data is live sometimes, 
so I don't have the luxury of having it offline or modified.  I also 
intend to eventually develop some middleware for an accounting app that 
redirects data access into PostgreSQL as well, so the current data pump 
application is both a prototype and a learning tool.

My issue arises with the way FoxPro and PostgreSQL treat dates.  While 
date formatting is a non-issue, the way empty date fields are handled 
creates one.  FoxPro has a very "container-oriented" approach to its 
data that allows date fields to be empty.  PostgreSQL only allows (a) 
valid date data or (b) null values in date fields, and the concept of an 
"empty" date runs against how things work.  Given that my source data 
contains empty date fields in several of 100+ tables in this accounting 
application, it is not feasible to perform "data cleansing" and format 
the empty fields with dates (because this changes the semantics that 
FoxPro uses).  Predictably, when using the tool, the ODBC driver reports 
an error as soon as there is an attempt to insert a row with an empty 
date.  I have turned ODBC logging on and discovered that an empty date 
is literally being passed as an empty string with a typecast attached to 
it, ie. ''::date is the value being passed.  If there was any data in 
the string I'm sure the method used would work wonderfully (I assume the 
typecast is used to force an interpretation of the date string, which in 
turn allows for multiple date formats without the burden of determining 
which format is in use).  I have already tried setting date fields in 
PostgreSQL to a default value (either NULL or a specific 
"before-the-app-existed" date like 1900-01-01) but this fails because 
the evaluation of the data occurs before the default value has a chance 
to be assigned.  I'm sure there is some obscure method to "trick" 
PostgreSQL into dealing with these properly, but that would really run 
against what I'm trying to accomplish.  FoxPro also has a concept of 
NULL, but how it handles it is distinctly different from NULLs in 
PostgreSQL; and in fact, what FoxPro calls an "empty field" would be 
much closer in analogy to what PostgreSQL considers a NULL.

My question is simple: how hard would it be to extend the ODBC driver 
slightly to include three new options?  The options would take the form 
of three radio buttons under a single heading, "Empty Date Handling", 
and would allow for the following options: (A) No-Op, which would do 
nothing new (the driver continues to operate as it currently does, and 
empty dates are flagged as errors), (B) NULL Conversion, which treats 
all "empty" dates as NULLs, and all NULL date values are converted to 
"empty" when retrieved (this would make FoxPro happy, and would probably 
also work well with Access, Paradox, dBase, etc as they allow for empty 
date fields as well), (C) Set to User-Defined, which would allow the 
user to enter a specific date value into a text box.  When an empty date 
is encountered, it is replaced with this value.  Data retrieval is 
unaffected because the value retrieved is valid for both systems.

These three options would probably go a long ways toward integrating 
"non-standard" databases like FoxPro and Access with PostgreSQL, 
especially the "NULL Conversion".  If it isn't feasible, or would 
require extensive rework, I need to know what could be done; as a last 
resort, I could use MySQL as a backend (it allows empty dates) but I'm 
not wild about this concept at the moment because of issues it has with 
data validation (things like "Feb. 30th" as a date are considered 
"valid", but PostgreSQL is smart enough to recognize this as a 
nonsensical date).  Data cleansing is a remote possibility but it cannot 
be performed at this juncture and frankly is impractical with 100+ 
tables.  The type conversion of dates is really my best hope.  Can it be 
done?  Will it be done?  Or am I just out of luck?

Regards,
Avery

Note: the opinions expressed are not those of my employer, nor do they 
represent them.

pgsql-odbc by date

Next:From: noreplyDate: 2006-08-25 23:16:39
Subject: [ psqlodbc-Bugs-1000714 ] Driver gets progressively slower Multi-threading retrieval
Previous:From: Ezequias Rodrigues da RochaDate: 2006-08-25 13:06:22
Subject: Problem with VPN

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group