Visual FoxPro, PostgreSQL, and Dates (Long)

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.