Re: Visual FoxPro, PostgreSQL, and Dates (Long)

>>>> [ many helpful replies and suggestions snipped, see the thread]
>>>>
>>>> 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.
>>>>
>>>> [more stuff snipped]
>>>>
>
> For some possible solutions short of changing the ODBC driver see message
> below. Message also explains why a BEFORE trigger will not work as suggested
> above.
> http://archives.postgresql.org/pgsql-general/2003-06/msg00713.php
>
Thanks for your reply. After reading the thread you provided, it seems
that petitioning to add the "Empty-Date-to-NULL" functionality to the
ODBC driver would be the right thing to do. It should be a fairly
simple modification as we are literally replacing ''::date in the output
string to the server in all cases when the option is enabled, and doing
nothing when disabled. This should not interfere with ANY other
situation (because by default someone passing ''::date to the server is
going to error out *anyway* because it's ALWAYS invalid for ALL cases,
UPDATE will need this to apply, INSERT would apply, SELECT/DELETE would
be nonsensical, etc.) and I believe that the ODBC driver is already
returning NULL date fields as "empties" from SELECT statements when used
in FoxPro, etc. So adding this simple code with a switch to turn it
off/on would not only solve the issue, but as I mentioned earlier, I
think it would allow a broader audience of users to move their
applications to PostgreSQL via ODBC, without alot of painful code
changes. At the same time, PostgreSQL continues to follow it's dictum
of not accepting empty dates. It's a win-win for everyone. If I find
the time and the correct compiler (I don't have VC++ v6) I'll try to
patch it myself and submit it.

Thanks again to everyone here for their support and suggestions. This
has been very informative and I hope that the ODBC maintainer(s) will
consider this petition for a new function.