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

Hiroshi Inoue wrote:
> Avery Payne wrote:
>>
>> 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.
>
> Unfortunately there are already too many options. So I'm not sure
> if we would be able to support the new option.
>
> Anyway which conversion do you expect for the option, 1), 2) or both ?
>
> 1) clients' '' Date -> backends' NULL
> 2) backends' NULL Date -> clients' ''
>
> regards,
> Hiroshi Inoue
Both.

In Summary:

Option(A) - do nothing. The driver behaves like it always has. This
maintains compatibility with existing programs that are properly coded
and expect empty dates to generate an error. It also requires no code
changes. :-)

Option(B) - empty dates turn into NULL and are stored as NULL. NULLs
retrieved on a date field are converted to empty dates. NULLs that are
implicitly passed as a parameter for a date in a INSERT/UPDATE are left
untouched, but when they are retrieved the conversion will show an empty
date. This setting is to appease all of the windows-based applications
that work with backends that allow for empty dates (and there are many,
many, many of them). The majority of those applications don't bother
with the concept of NULL because in most cases, Microsoft's programming
environments (1) typically return an empty value from GUI forms instead
of NULL and (2) programmers usually just pass these values directly to
the existing backend database, so you end up with most MS programmers
not bothering with processing NULL as a value because it just adds more
code and complexity for little gain (from their point of view).

Option(C) - empty dates are filled with a specific value stored in the
driver settings. No changes are made upon retrieval because only valid
values could have been stored in the backend to begin with. This pretty
much supports the current way of bridging an application to PostgreSQL,
ie. fill your database with junk data markers and code a bunch of
workarounds. Typically this manifests itself by populating the date
fields in the database with a "marker" date, ie. 1900-01-01 in the case
of MS SQL. If you think about it, this is a rather nasty workaround and
fills your tables with junk data, hardly what anyone would call good
database hygiene. This option is just as needed as Option (B) because
the only way to do this is to store junk data markers in advance in the
current database, then port the data over. In some cases, it isn't
viable (the data may be live and cannot be copied or converted offline)
so having the ability to do this "live" is very valuable.

Again, thank you for taking the time to hear me out and even consider this.

PS. I revisited my claim on NULLs returned to FoxPro; it turns out that
FoxPro DOESN'T translate NULL to an empty date, instead it displays it
as FoxPro's version of NULL. My apologies for saying this when it
wasn't true, it was an incorrect assumption on my part that I had tested
it.