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 <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.
>
> ---------------------------(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(at)comcast(dot)net