Visual FoxPro, PostgreSQL, and Dates (Long)

I'm in the process of extending a FoxPro app that can pump data via ODBC
(v8.2.2) from an accounting package based on FoxPro (.dbf files) into
PostgreSQL 8.1. I'm aware of the different dbf-to-postgresql tools out
there but none of them suit my purpose, and the data is live sometimes,
so I don't have the luxury of having it offline or modified. I also
intend to eventually develop some middleware for an accounting app that
redirects data access into PostgreSQL as well, so the current data pump
application is both a prototype and a learning tool.

My issue arises with the way FoxPro and PostgreSQL treat dates. While
date formatting is a non-issue, the way empty date fields are handled
creates one. FoxPro has a very "container-oriented" approach to its
data that allows date fields to be empty. PostgreSQL only allows (a)
valid date data or (b) null values in date fields, and the concept of an
"empty" date runs against how things work. Given that my source data
contains empty date fields in several of 100+ tables in this accounting
application, it is not feasible to perform "data cleansing" and format
the empty fields with dates (because this changes the semantics that
FoxPro uses). Predictably, when using the tool, the ODBC driver reports
an error as soon as there is an attempt to insert a row with an empty
date. I have turned ODBC logging on and discovered that an empty date
is literally being passed as an empty string with a typecast attached to
it, ie. ''::date is the value being passed. If there was any data in
the string I'm sure the method used would work wonderfully (I assume the
typecast is used to force an interpretation of the date string, which in
turn allows for multiple date formats without the burden of determining
which format is in use). I have already tried setting date fields in
PostgreSQL to a default value (either NULL or a specific
"before-the-app-existed" date like 1900-01-01) but this fails because
the evaluation of the data occurs before the default value has a chance
to be assigned. I'm sure there is some obscure method to "trick"
PostgreSQL into dealing with these properly, but that would really run
against what I'm trying to accomplish. FoxPro also has a concept of
NULL, but how it handles it is distinctly different from NULLs in
PostgreSQL; and in fact, what FoxPro calls an "empty field" would be
much closer in analogy to what PostgreSQL considers a NULL.

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.

These three options would probably go a long ways toward integrating
"non-standard" databases like FoxPro and Access with PostgreSQL,
especially the "NULL Conversion". If it isn't feasible, or would
require extensive rework, I need to know what could be done; as a last
resort, I could use MySQL as a backend (it allows empty dates) but I'm
not wild about this concept at the moment because of issues it has with
data validation (things like "Feb. 30th" as a date are considered
"valid", but PostgreSQL is smart enough to recognize this as a
nonsensical date). Data cleansing is a remote possibility but it cannot
be performed at this juncture and frankly is impractical with 100+
tables. The type conversion of dates is really my best hope. Can it be
done? Will it be done? Or am I just out of luck?

Regards,
Avery

Note: the opinions expressed are not those of my employer, nor do they
represent them.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

ALVARO ARCILA wrote:
>
> Hi,
>
> After reading your email I've noticed the following...please correct
> me if I'm wrong...
>
> You have a problem dealing with datefields while using
> VisualFoxpro+postgresql, the problem is that VisualFoxpro sends
> nothing when a date field has nothing while postgresql at least needs
> NULL to insert that value...
>
> I think that you should try to look for a different approach to solve
> your problem instead of trying to change the odbc driver or your
> Visual Fox Pro app ...
My main reason for lobbying to include a new feature in the ODBC driver
comes from the need to reduce the load on the server. Yes, I can
certainly create a fix server-side, but in my case I will eventually
have 50-70 workstations hitting the server on a regular basis. Rather
than have the server execute 50x the code server-side, it would be nicer
to break up the load and have each of the 50 clients use local
processing power to address the issue. By keeping some of the
processing local to the client, the server can expend more CPU time on
processing queries and updates.
>
> Maybe you could try with a trigger that operates before insert
> operation of the table that contains the date fields, this trigger has
> to evaluate the value of the datefield that the app sends and if it
> has nothing makes it equal to null....
If all else fails, this sounds like a perfectly viable solution. I was
trying to avoid it because there are ~90-100 tables for the accounting
system data alone, and another ~150 tables for each company stored.
There are 4 companies, so that's about 100+(150x3)=550 tables, needing
potentially 550 triggers. Multiply that by activity from 50
workstations at half-load (say, 25 are active at any time) and you have
*a lot* of server-side activity. The prototype "data-pump" tool I was
referring to earlier can be easily modified to auto-generate trigger
code, so it's not too big a burden. However, when it comes time to
write the middleware that will be using this regularly, it will require
careful coding to maintain those triggers.

I was originally thinking of creating a "SAFEDATE" domain, that was
typecast from a date. The constraints would be paired with a function
to automatically perform the 'empty string'-to-NULL conversion, but when
I checked the documentation for domains, it warned against doing this
for security reasons, apparently because there is lax checking on the
typecast of what is returned at runtime from a function. So I stayed
away from this concept. I'm also not entirely sure that it would do
what I'm thinking, ie. I think the constraints would only be enforced at
a INSERT/UPDATE and would not necessarily change the data before it is
committed to the table.
>
> I think this way you don't have to wait for a new version of the
> psqlODBC driver with the special functionally that you desire and you
> don't have make major chages to your VisualFoxpro app....
>
> I hope this cuold be helpfull....
It certainly is. I had wondered if there was a way to accomplish this
server-side, and there is, so there is still hope to make everything
work correctly. Thanks for taking the time to reply; I was not
relishing the idea of having to move to MySQL.
>
> Best regards,
>
> Alvaro Arcila
>
>
>> From: Avery Payne
>> To: pgsql-odbc@postgresql.org
>> Subject: [ODBC] Visual FoxPro, PostgreSQL, and Dates (Long)
>> Date: Fri, 25 Aug 2006 13:28:45 -0700
>>
>> [snipped background text - I've left the request below for context.]
>>
>> 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.
>>
>> These three options would probably go a long ways toward integrating
>> "non-standard" databases like FoxPro and Access with PostgreSQL,
>> especially the "NULL Conversion". If it isn't feasible, or would
>> require extensive rework, I need to know what could be done; as a
>> last resort, I could use MySQL as a backend (it allows empty dates)
>> but I'm not wild about this concept at the moment because of issues
>> it has with data validation (things like "Feb. 30th" as a date are
>> considered "valid", but PostgreSQL is smart enough to recognize this
>> as a nonsensical date). Data cleansing is a remote possibility but
>> it cannot be performed at this juncture and frankly is impractical
>> with 100+ tables. The type conversion of dates is really my best
>> hope. Can it be done? Will it be done? Or am I just out of luck?
>>
>> Regards,
>> Avery
>>
>> Note: the opinions expressed are not those of my employer, nor do
>> they represent them.
>
>


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

On Monday 28 August 2006 09:35 am, Avery Payne wrote:
> ALVARO ARCILA wrote:
> > Hi,
> >
> > After reading your email I've noticed the following...please correct
> > me if I'm wrong...
> >
> > You have a problem dealing with datefields while using
> > VisualFoxpro+postgresql, the problem is that VisualFoxpro sends
> > nothing when a date field has nothing while postgresql at least needs
> > NULL to insert that value...
> >
> > I think that you should try to look for a different approach to solve
> > your problem instead of trying to change the odbc driver or your
> > Visual Fox Pro app ...
>
> My main reason for lobbying to include a new feature in the ODBC driver
> comes from the need to reduce the load on the server. Yes, I can
> certainly create a fix server-side, but in my case I will eventually
> have 50-70 workstations hitting the server on a regular basis. Rather
> than have the server execute 50x the code server-side, it would be nicer
> to break up the load and have each of the 50 clients use local
> processing power to address the issue. By keeping some of the
> processing local to the client, the server can expend more CPU time on
> processing queries and updates.
>
> > Maybe you could try with a trigger that operates before insert
> > operation of the table that contains the date fields, this trigger has
> > to evaluate the value of the datefield that the app sends and if it
> > has nothing makes it equal to null....
>
> If all else fails, this sounds like a perfectly viable solution. I was
> trying to avoid it because there are ~90-100 tables for the accounting
> system data alone, and another ~150 tables for each company stored.
> There are 4 companies, so that's about 100+(150x3)=550 tables, needing
> potentially 550 triggers. Multiply that by activity from 50
> workstations at half-load (say, 25 are active at any time) and you have
> *a lot* of server-side activity. The prototype "data-pump" tool I was
> referring to earlier can be easily modified to auto-generate trigger
> code, so it's not too big a burden. However, when it comes time to
> write the middleware that will be using this regularly, it will require
> careful coding to maintain those triggers.
>
> I was originally thinking of creating a "SAFEDATE" domain, that was
> typecast from a date. The constraints would be paired with a function
> to automatically perform the 'empty string'-to-NULL conversion, but when
> I checked the documentation for domains, it warned against doing this
> for security reasons, apparently because there is lax checking on the
> typecast of what is returned at runtime from a function. So I stayed
> away from this concept. I'm also not entirely sure that it would do
> what I'm thinking, ie. I think the constraints would only be enforced at
> a INSERT/UPDATE and would not necessarily change the data before it is
> committed to the table.
>
> > I think this way you don't have to wait for a new version of the
> > psqlODBC driver with the special functionally that you desire and you
> > don't have make major chages to your VisualFoxpro app....
> >
> > I hope this cuold be helpfull....
>
> It certainly is. I had wondered if there was a way to accomplish this
> server-side, and there is, so there is still hope to make everything
> work correctly. Thanks for taking the time to reply; I was not
> relishing the idea of having to move to MySQL.
>
> > Best regards,
> >
> > Alvaro Arcila
> >
> >> From: Avery Payne
> >> To: pgsql-odbc@postgresql.org
> >> Subject: [ODBC] Visual FoxPro, PostgreSQL, and Dates (Long)
> >> Date: Fri, 25 Aug 2006 13:28:45 -0700
> >>
> >> [snipped background text - I've left the request below for context.]
> >>
> >> 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.
> >>
> >> These three options would probably go a long ways toward integrating
> >> "non-standard" databases like FoxPro and Access with PostgreSQL,
> >> especially the "NULL Conversion". If it isn't feasible, or would
> >> require extensive rework, I need to know what could be done; as a
> >> last resort, I could use MySQL as a backend (it allows empty dates)
> >> but I'm not wild about this concept at the moment because of issues
> >> it has with data validation (things like "Feb. 30th" as a date are
> >> considered "valid", but PostgreSQL is smart enough to recognize this
> >> as a nonsensical date). Data cleansing is a remote possibility but
> >> it cannot be performed at this juncture and frankly is impractical
> >> with 100+ tables. The type conversion of dates is really my best
> >> hope. Can it be done? Will it be done? Or am I just out of luck?
> >>
> >> Regards,
> >> Avery
> >>
> >> Note: the opinions expressed are not those of my employer, nor do
> >> they represent them.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

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/msg0071 3.php
--
Adrian Klaver
aklaver@comcast.net

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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/msg0071 3.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.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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.






---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings