Re: Visual FoxPro, PostgreSQL, and Dates (Long) - Mailing list pgsql-odbc

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 <apayne@pcfruit.com>
> >> 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/msg00713.php
--
Adrian Klaver
aklaver@comcast.net