Re: 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

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

---------------------------(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: 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: 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 majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Porting problem from Informix to Postgres...

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster