Re: Representing Empty DATE values as NULL - Mailing list pgsql-odbc

>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.

"Empty Date" is a Visual FoxPro concept.  It is exactly what it sounds like:
a date field "empty", but not set to NULL.  Such fields are directly stored in FoxPro tables
in a pseudo-BCD format; so the date 2001-12-31 is stored as 20011231, with each
digit taking one byte.  The problem with this is that FoxPro uses what is commonly
called "data centric" commands to move data around; one of those commands is
APPEND, which takes one table and attempts to "append" all of the data onto the
end of another table, matching fields by name, and discarding data that has
no matching field name.  When you attempt to APPEND data that has a row
with an empty date in it, PostgreSQL immediately rejects it (because it's nonsensical)
and the APPEND stops in its tracks.  This is but one of many different methods that
can be used to insert data into a table without performing validity checks, there are
others like SCATTER/GATHER, UPDATE FROM, INSERT INTO, etc. that also
perform no validity checks.  Because of this, most FoxPro programs perform
data validation up-front, during the point of entry; but this is error-prone and
it is often easier for the programmer to simply accept "empty" fields into a
table than it is to hunt down all fields that are missing data and force them
to a default value of NULL.  Note that NULL is also treated much differently in
FoxPro than elsewhere, so most programs don't even bother with NULL and
instead just pass along data in a "raw" format directly to the table.  There is
also the EMPTY() function which looks at any field or variable and returns
a value of True if the data passed to it is considered "empty".  Taken together,
the net affect on FoxPro programming is to focus on data movement, and worry
about data validation either before, or typically after, the data is "moved".

I know this is horrid and represents many different (and very poor) programming
practices that people should avoid, but it is unfortunately quite common.  One of
my primary job functions is to make modifications to the accounting package at
my work to adapt it to changing business needs (let's just say that the environment
I work in constant shifts and it's not unusual to need as many as three
different patches a week to accommodate new business practices).  I can't name the
package here in the forum, but anyone with a little sense and some google searching
can determine which of the major medium-sized accounting packages are
written in FoxPro exclusively.

The latest version of this accounting package has the option to switch to SQL Server,
which would fix several technical issues that we encounter (like table size limits of
just 2 Gbyte).  The switchover is possible because the package was written
with a "data API" layer that you call in place of the native FoxPro commands,
which abstract away what kind of backend you are using.  However, my employer
operates on a very thin profit margin, so money is very tight.  This means it is very
attractive to attempt to write our own data API that intercepts calls from the package
and redirects them to some other database, like, oh, I don't know, maybe one
with an elephant for a mascot? :-)  But in order to accommodate existing designs and
make the transition much easier, I need to resolve this "empty date" issue.  Hiroshi to
the rescue!  His work allows me to make this a reality.  Now all I have to do is check for
NULL values when fetching dates, something that I can work around using a special
option that allows me to substitute data that is remotely fetched using a type of
"pseudo-view" option.  To bad that same option doesn't work in reverse, otherwise
I wouldn't have to bother with this mess.

Note that you can also see "empty" dates in Access.  Create a new table with an autonumber
field named "MyID", a date field named "MyDate", and a text field named "MyText".
Open the table after you have saved its definition; then proceed to fill in several rows of data
using just the "MyText" field alone.  Each time you do this, the MyID field will auto-increment
and a new number appears; and your text is saved as you move off of each row; but
the "MyDate" field remains -->>EMPTY<<--, which is not the same as NULL.  This is a common
theme in Microsoft's smaller database products, where the "databases" function more like
storage containers than actual database engines.  Use this with Visual Basic and you
have an environment where MS programmers are most likely to take the raw value
of a field (typically an empty string, "") and pass it directly into the table that is mapped
to the form's field.  To prevent PostgreSQL from choking on this, set "Server Side Prepare"
to ON and select option 0x8 when using Hiroshi's experimental driver, and the empty date
fields will be automagically translated to a sane value, NULL, instead of just "  /  /    ".

Hope this has answered your questions...