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

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

From: Avery Payne <apayne(at)pcfruit(dot)com>
To: aklaver(at)comcast(dot)net
Cc: pgsql-odbc(at)postgresql(dot)org,ALVARO ARCILA <arcila_alvaro(at)hotmail(dot)com>
Subject: Re: Visual FoxPro, PostgreSQL, and Dates (Long)
Date: 2006-08-29 00:41:29
Message-ID: 44F38D39.8000707@pcfruit.com (view raw or flat)
Thread:
Lists: pgsql-odbc
>>>> [ 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.

In response to

Responses

pgsql-odbc by date

Next:From: anomatosDate: 2006-08-29 08:45:02
Subject: Record locking issue using ODBC from .NET
Previous:From: Adrian KlaverDate: 2006-08-28 18:36:16
Subject: Re: Visual FoxPro, PostgreSQL, and Dates (Long)

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