Visual FoxPro, PostgreSQL, and Dates (Long) - Mailing list pgsql-odbc
From | Avery Payne |
---|---|
Subject | Visual FoxPro, PostgreSQL, and Dates (Long) |
Date | |
Msg-id | 44EF5D7D.8040002@pcfruit.com Whole thread Raw |
List | pgsql-odbc |
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.
pgsql-odbc by date: