[SQL] 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-842711fax +39-0434-842797 Urbani, dimissioni please http://punto-informatico.it/p.asp?i=49171 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Porting problem from Informix to Postgres...
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. > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Porting problem from Informix to Postgres...
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Porting problem from Informix to Postgres...
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] LIKE operator and string comparison
I used the following SQL code to match '\foo\bar' SELECT text FROM test WHERE text LIKE 'foo%' But if I choose to use string comparison, instead of 4 escape characters, I only need 2. SELECT text FROM test WHERE text = '\\foo\\bar' Why is that? I am using PostgreSQL 7.4, and the SQL code was entered through psql. Thanks! Wei ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] LIKE operator and string comparison
Wei Weng <[EMAIL PROTECTED]> writes: > But if I choose to use string comparison, instead of 4 escape characters, I > only need 2. > Why is that? Backslash is an escape character for LIKE. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] LIKE operator and string comparison
Tom Lane wrote: Wei Weng <[EMAIL PROTECTED]> writes: But if I choose to use string comparison, instead of 4 escape characters, I only need 2. Why is that? Backslash is an escape character for LIKE. regards, tom lane What about in regular strings? I do need to use backslash to escape the other backslash in order to get the '\'. Why isn't that the case in LIKE operation? Thanks Wei ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] LIKE operator and string comparison
Wei Weng <[EMAIL PROTECTED]> writes: > What about in regular strings? I do need to use backslash to escape the > other backslash in order to get the '\'. Why isn't that the case in LIKE > operation? It is. You write four backslashes in order to describe a string constant value containing two backslashes. When the LIKE code sees that, it interprets it as one quoted backslash. See LIKE ... ESCAPE if you'd like to use a different escape character in the LIKE pattern. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
