Thread: Re: Representing Empty DATE values as NULL

I'm sorry that I haven't written in awhile with my findings.  I have
completed the FoxPro->ODBC->Access test and the Access ODBC driver
returns a NULL for empty dates.  I've also read up a bit more on ODBC
and have figured out the following:

1. ODBC doesn't really support the idea of an empty date.  There just
isn't a representation for it.  However...
2. ODBC does support returning NULL values, so...
3. Empty dates represented as NULL will have to be an adequate solution
to the issue.  This means...
4. ODBC sending data from client->odbc->server with empty dates
translating as NULL should be acceptable, maybe even for the majority of
cases, and
5. ODBC sending data from server->odbc->client with NULL as a date is
the only real representation that can be afforded to any client.

The option to pass "empty dates" as NULLs in client->odbc->server might
be one you wish to make available on a permanent basis; or better yet,
just quietly perform the translation in all cases as a default
behavior.  Any comments or suggestions about making it permanent?  Will
it mess something up elsewhere?

P.S. I want to personally thank you, Hiroshi, for your tireless time and
effort in looking into this issue.  Your willingness to entertain this
new feature is a refreshing break from other open source projects that I
have interacted with in the past, many of which didn't bother to even
look at requests, much less think about them.  My hat is off to you.

Hello Avery,
"Avery Payne" <apayne@pcfruit.com> wrote:

AP> I'm sorry that I haven't written in awhile with my findings.  I
AP> have completed the FoxPro->ODBC->Access test and the Access ODBC
AP> driver returns a NULL for empty dates.  I've also read up a bit
AP> more on ODBC and have figured out the following:
AP>
AP> 1. ODBC doesn't really support the idea of an empty date.  There
AP> just isn't a representation for it.  However...
AP> 2. ODBC does support returning NULL values, so...
AP> 3. Empty dates represented as NULL will have to be an adequate
AP> solution to the issue.  This means...
AP> 4. ODBC sending data from client->odbc->server with empty dates
AP> translating as NULL should be acceptable, maybe even for the
AP> majority of cases, and
AP> 5. ODBC sending data from server->odbc->client with NULL as a date
AP> is the only real representation that can be afforded to any client.
AP>
AP> The option to pass "empty dates" as NULLs in client->odbc->server
AP> might be one you wish to make available on a permanent basis; or
AP> better yet, just quietly perform the translation in all cases as a
AP> default behavior.  Any comments or suggestions about making it
AP> permanent?  Will it mess something up elsewhere?

Excuse my interrupting, I also have no idea what an empty date is. IFAIK,
such a concept neither exists in ODBC nor in Access.  What is it?

In my experience, Access can do with NULL quite fine and the Access ODBC
driver too.
--
Sincerely,
Dmitriy Ivanov
Common Lisp ODBC interface - lisp.ystok.ru/ysql.html