Thread: Porting problem from Informix to Postgres...
On our organization we are porting an old, internally developed app, that use Informix (SE 7.XX, on a sun box) as database backend and Gupta Centura Team Developer 1.5.1 as development environment. Centura uses Informix via a native driver, for postgres we pass along odbc. Our postgres environment are a set of intel box, loaded with debian woody, so postgres 7.2.1. We have solved many problem, i've also make a little perl script that convert a informix's dbexport dump into a set of file suitable to import into postgres. Now we are moving the app... we was happy, because seems that the only modification needed was to alter some of the more complex query, because of the difference on informix and postgres sql dialects. But a big problem now arise: at the very beginning of this app, there was a bug in handling of date in centura, so the guys circumvent this converting all date in strings, and handle ``manually''. Not sufficient, they don't do this with a wrapper function around date field, but with ``casting'' data in varchar in the selects (with select ... into ...) and only local computation. It's dumb, i know. ;( So now, on informix, a set of centura statement like: Call SqlPrepareAndExecute(hsql,'select dt_fatt from fatture into :una')Call SqlFetchNext(hsql,err) make on string/variable :una a date like: '01-08-2001' for january 8, 2001, but on postgres we got: '2001-01-08' There's a way to alter globally the date format (apart PGDATESTYLE variable, just tried with no luck), or there's a way to setup query to have the correct date format? I've tried to use the mailing list search robots, but looking for informix on this list (and on -general) make nothing. ;( Many thanks. -- dott. Marco Gaiarin GNUPG Key ID: 240A3D66 Associazione ``La Nostra Famiglia'' http://www.sv.lnf.it/Polo FVG - Via della Bontà, 7 - 33078 - San Vito al Tagliamento (PN) gaio(at)sv.lnf.it tel+39-0434-842711 fax +39-0434-842797 Urbani, dimissioni please http://punto-informatico.it/p.asp?i=49171
Marco Gaiarin wrote: > On our organization we are porting an old, internally developed app, > that use Informix (SE 7.XX, on a sun box) as database backend and Gupta > Centura Team Developer 1.5.1 as development environment. > > Centura uses Informix via a native driver, for postgres we pass along > odbc. Our postgres environment are a set of intel box, loaded with > debian woody, so postgres 7.2.1. > > We have solved many problem, i've also make a little perl script that > convert a informix's dbexport dump into a set of file suitable to > import into postgres. > > > Now we are moving the app... we was happy, because seems that the only > modification needed was to alter some of the more complex query, > because of the difference on informix and postgres sql dialects. > > > But a big problem now arise: at the very beginning of this app, there > was a bug in handling of date in centura, so the guys circumvent this > converting all date in strings, and handle ``manually''. > Not sufficient, they don't do this with a wrapper function around date > field, but with ``casting'' data in varchar in the selects (with select > ... into ...) and only local computation. > It's dumb, i know. ;( > > > So now, on informix, a set of centura statement like: > > Call SqlPrepareAndExecute(hsql,'select dt_fatt from fatture into :una') > Call SqlFetchNext(hsql,err) > > make on string/variable :una a date like: > > '01-08-2001' > > for january 8, 2001, but on postgres we got: > > '2001-01-08' > > > There's a way to alter globally the date format (apart PGDATESTYLE > variable, just tried with no luck), or there's a way to setup query to have > the correct date format? > Wouldn't set datestyle to postgres; (or something else, try iso,mdy, I think it depends on your locale) do the trick? You can also try to put it in postgresql.conf if I'm not wrong Michalis > I've tried to use the mailing list search robots, but looking for> informix on this list (and on -general) make nothing.;(>>> Many thanks.>
O Marco Gaiarin έγραψε στις Sep 22, 2004 : > > On our organization we are porting an old, internally developed app, > that use Informix (SE 7.XX, on a sun box) as database backend and Gupta > Centura Team Developer 1.5.1 as development environment. > > Centura uses Informix via a native driver, for postgres we pass along > odbc. Our postgres environment are a set of intel box, loaded with > debian woody, so postgres 7.2.1. > > We have solved many problem, i've also make a little perl script that > convert a informix's dbexport dump into a set of file suitable to > import into postgres. > > > Now we are moving the app... we was happy, because seems that the only > modification needed was to alter some of the more complex query, > because of the difference on informix and postgres sql dialects. > > > But a big problem now arise: at the very beginning of this app, there > was a bug in handling of date in centura, so the guys circumvent this > converting all date in strings, and handle ``manually''. > Not sufficient, they don't do this with a wrapper function around date > field, but with ``casting'' data in varchar in the selects (with select > ... into ...) and only local computation. > It's dumb, i know. ;( > > > So now, on informix, a set of centura statement like: > > Call SqlPrepareAndExecute(hsql,'select dt_fatt from fatture into :una') > Call SqlFetchNext(hsql,err) > > make on string/variable :una a date like: > > '01-08-2001' > > for january 8, 2001, but on postgres we got: > > '2001-01-08' Just a dummy workaround (i am sure you'll find something better), dynacom=# SET DateStyle TO German ; SET dynacom=# SELECT replace(now()::date::text,'.','-'); replace ------------22-09-2004 > > > There's a way to alter globally the date format (apart PGDATESTYLE > variable, just tried with no luck), or there's a way to setup query to have > the correct date format? > > I've tried to use the mailing list search robots, but looking for > informix on this list (and on -general) make nothing. ;( > > > Many thanks. > > -- -Achilleus
Marco, > Centura uses Informix via a native driver, for postgres we pass along > odbc. Our postgres environment are a set of intel box, loaded with > debian woody, so postgres 7.2.1. You do realize that this is a 2-year-old version of Postgresql, yes? It seems to me that if you're going to take the trouble of porting an application, you should port it to something current -- Debian Stable or not. Particularly since, in a year, you can expect that the PostgreSQL community will probably stop doing security/stability patches for 7.2. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
Mandi! Jeff In chel di` si favelave... > 1. use to_char to format the date however you like ...this seems the best solution, many thanks to all! ;) -- dott. Marco Gaiarin GNUPG Key ID: 240A3D66 Associazione ``La Nostra Famiglia'' http://www.sv.lnf.it/Polo FVG - Via della Bontà, 7 - 33078 - San Vito al Tagliamento (PN) gaio(at)sv.lnf.it tel+39-0434-842711 fax +39-0434-842797 Urbani, dimissioni please http://punto-informatico.it/p.asp?i=49171