Thread: 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.
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. > >
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
>>>> [ 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/msg00713.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.
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
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.