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 <apayne(at)pcfruit(dot)com>
>> To: pgsql-odbc(at)postgresql(dot)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.
>
>