Thread: LATIN1/9 conversion....
All
I have a recurrent error in my postgres 8.0.4 on redhat 9 (yes, I know
its old - first things first...) server. It only occurs from the
MSAccess/Windows clients I have, not from Java/JDBC or
pgAdminIII/Windows clients; hence the question here.
The log shows (anonymised) up to 6 times a second while a user is
active:
Feb 8 16:03:28 (server) postgres[8625]: [7-1] 2006-02-08
16:03:28 GMT (user)(database) ERROR: conversion between latin9 and
LATIN1 is not supported.
Now, I have added statement debugging to the log together with a "set
client_encoding to 'latin1'" in the ODBC driver, and got this:
Feb 8 15:48:18 (server) postgres[8277]: [6-1] 2006-02-08
15:48:18 GMT (user) (database) LOG: statement: select version()
Feb 8 15:48:18 (server) postgres[8277]: [7-1] 2006-02-08
15:48:18 GMT (user) (database) LOG: statement: set DateStyle to 'ISO'
Feb 8 15:48:18 (server) postgres[8277]: [8-1] 2006-02-08
15:48:18 GMT (user) (database) LOG: statement: set geqo to 'OFF'
Feb 8 15:48:18 (server) postgres[8277]: [9-1] 2006-02-08
15:48:18 GMT (user) (database) LOG: statement: set extra_float_digits
to 2
Feb 8 15:48:18 (server) postgres[8277]: [10-1] 2006-02-08
15:48:18 GMT (user) (database) LOG: statement: set client_encoding to
'latin1'
Feb 8 15:48:18 (server) postgres[8277]: [11-1] 2006-02-08
15:48:18 GMT (user) (database) LOG: statement: select oid from pg_type
where typname='lo'
Feb 8 15:48:18 (server) postgres[8277]: [12-1] 2006-02-08
15:48:18 GMT (user) (database) LOG: statement: select
pg_client_encoding()
Feb 8 15:48:18 (server) postgres[8277]: [13-1] 2006-02-08
15:48:18 GMT (user) (database) LOG: statement: set client_encoding to
'latin9'
Feb 8 15:48:18 (server) postgres[8277]: [14-1] 2006-02-08
15:48:18 GMT (user) (database) ERROR: conversion between latin9 and
LATIN1 is not supported
You can clearly see my latin1 request followed shortly by the latin9. I
have examined the ODBC code and it looks like the culprit is a code page
of 1252.
Am I tracing this right? What do I do about? Suggestions, please!
Tim Clarke
Hello, I'm not much experienced in encoding. > You can clearly see my latin1 request followed shortly by the latin9. I > have examined the ODBC code and it looks like the culprit is a code page > of 1252. It could be interesting to see mylog output from Windows. I don't understand well why it choose latin1 and then latin9. Don't you specify 'set client_encoding to latin1' in DataSource settings? I'm new developer since 08.01 version so please be patient with me. > Am I tracing this right? What do I do about? Suggestions, please! You could use way of unicode driver. It use UTF-8 so no error occurs with latin 1 x latin 9. It means you may use "PostgreSQL Unicode" driver instead of "PostgreSQL ANSI" To other developers: Another one user points to problems with win1252 -> Latin 9 mapping. It seems to me that Latin 9 isn't right default choise for win1252. What do you mean? Regards, Luf
> -----Original Message----- > From: Ludek Finstrle [mailto:luf@pzkagis.cz] > Sent: 09 February 2006 15:21 > To: Tim Clarke > Cc: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] LATIN1/9 conversion.... > > > Hello, > > I'm not much experienced in encoding. No problem, Ludek (Luf?) - thanks for replying :-) > > You can clearly see my latin1 request followed shortly by > the latin9. I > > have examined the ODBC code and it looks like the culprit > is a code page > > of 1252. > > It could be interesting to see mylog output from Windows. I'm not running Postgres on Windows, just have some 'doze clients. > I don't understand well why it choose latin1 and then latin9. Seems to be hardcoded in the ODBC driver? File > Don't you specify 'set client_encoding to latin1' in DataSource > settings? Yup - you can see it in the log I originally sent - ODBC driver then sends the latin9 request! > I'm new developer since 08.01 version so please be patient with me. Patience? Always :-) > > Am I tracing this right? What do I do about? Suggestions, please! > > You could use way of unicode driver. It use UTF-8 so no error occurs > with latin 1 x latin 9. > It means you may use "PostgreSQL Unicode" driver instead of > "PostgreSQL ANSI" We have found that the euro symbol and other accented characters are not correctly stored if we do that. This database is currently coming across nightly from an Oracle database so I'll be able to switch to Unicode once that transition is complete. Real soon now. > To other developers: > Another one user points to problems with win1252 -> Latin 9 mapping. > It seems to me that Latin 9 isn't right default choise for win1252. > What do you mean? > > Regards, > > Luf > HTH Tim Clarke
> > > You can clearly see my latin1 request followed shortly by > > the latin9. I > > > have examined the ODBC code and it looks like the culprit > > is a code page > > > of 1252. > > > > It could be interesting to see mylog output from Windows. > > I'm not running Postgres on Windows, just have some 'doze clients. :-) mylog is output from psqlODBC driver. You can enable it in ODBC manager or by adding debug=1 in connection string (I don't know which way do you use). > > I don't understand well why it choose latin1 and then latin9. > > Seems to be hardcoded in the ODBC driver? File It's strange for me that it's choosing twice. > > Don't you specify 'set client_encoding to latin1' in DataSource > > settings? > > Yup - you can see it in the log I originally sent - ODBC driver then > sends the latin9 request! This is the answer :-) Hmmm. The driver doesn't care this one setting. It override it automatically :-( Are you able to compile the driver yourself? If so I can post you the hint how to disable automatic encoding detection. > > It means you may use "PostgreSQL Unicode" driver instead of > > "PostgreSQL ANSI" > > We have found that the euro symbol and other accented characters are not > correctly stored if we do that. This database is currently coming across > nightly from an Oracle database so I'll be able to switch to Unicode > once that transition is complete. Real soon now. I'm not sure if we are both talking about same thing. When you use LATIN1 PgSQL database and UNICODE psqlODBC driver there is the problem? I'm a little bit confused how it depend on "This database is currently coming across nightly from an Oracle database". My English isn't good. Maybe I only understand you bad. What psqlODBC driver do you use? 08.00.0102 os some 08.01.XXXX? Regards, Luf
Luf. > -----Original Message----- > From: Ludek Finstrle [mailto:luf@pzkagis.cz] > Sent: 10 February 2006 10:58 > To: Tim Clarke > Cc: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] LATIN1/9 conversion.... > > :-) mylog is output from psqlODBC driver. You can enable it in ODBC > manager or by adding debug=1 in connection string (I don't know which > way do you use). Lol - don't use that often - forgot the default file name. Sample files attached. > It's strange for me that it's choosing twice. I requested the latin1 setting manually in the ODBC Datasource (page 2) "connect settings". Immediately after sending this the driver sends a latin9 request. > This is the answer :-) > Hmmm. The driver doesn't care this one setting. It override it > automatically :-( Are you able to compile the driver yourself? > If so I can post you the hint how to disable automatic encoding > detection. I'm sure the driver is doing the setting for the right reason, its probably just me that has some settings wrong that means its choosing latin9 when we don't need it to. > > > It means you may use "PostgreSQL Unicode" driver instead of > > > "PostgreSQL ANSI" > > > > We have found that the euro symbol and other accented > characters are not > > correctly stored if we do that. This database is currently > coming across > > nightly from an Oracle database so I'll be able to switch to Unicode > > once that transition is complete. Real soon now. > > I'm not sure if we are both talking about same thing. > When you use LATIN1 PgSQL database and UNICODE psqlODBC > driver there is > the problem? No, we tried a unicode database with the unicode ODBC psqldriver. MSAccess couldn't write the correct characters to the database. > I'm a little bit confused how it depend on "This database is currently > coming across nightly from an Oracle database". My English isn't > good. Maybe I only understand you bad. We have a nightly script (based around the ora2pg project - http://www.samse.fr/GPL/) that sucks our live Oracle database to the developing Postgres replacement database. We have found that if the postgres database is unicode, we corrupt the international characters and euro symbols here, too. > What psqlODBC driver do you use? 08.00.0102 os some 08.01.XXXX? I have had this problem with evry driver from 08.01.01xx to the current 08.01.02 > Regards, > > Luf Regards and thanks for your continuing attention. Tim Clarke
Attachment
> > This is the answer :-) > > Hmmm. The driver doesn't care this one setting. It override it > > automatically :-( Are you able to compile the driver yourself? > > If so I can post you the hint how to disable automatic encoding > > detection. > > I'm sure the driver is doing the setting for the right reason, its I'm not sure ;-) I even think it makes this the bad way. > > > > It means you may use "PostgreSQL Unicode" driver instead of > > > > "PostgreSQL ANSI" > > > > > > We have found that the euro symbol and other accented > > characters are not > > > correctly stored if we do that. This database is currently > > coming across > > No, we tried a unicode database with the unicode ODBC psqldriver. > MSAccess couldn't write the correct characters to the database. Could you try LATIN1 database with Unicode psqlODBC driver? Does it still break the euro symbol? > I have had this problem with evry driver from 08.01.01xx to the current > 08.01.02 Ok. I'll try send you changed psqlODBC 08.01.0200 without autodetecting client_encoding. > Regards and thanks for your continuing attention. Not at all. I was in doubt if autodetecting the encoding after user connection settings is the right way some time ago. Regards, Luf
Luf. > -----Original Message----- > From: Ludek Finstrle [mailto:luf@pzkagis.cz] > Sent: 10 February 2006 11:32 > To: Tim Clarke > Cc: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] LATIN1/9 conversion.... > > I'm not sure ;-) I even think it makes this the bad way. > > Could you try LATIN1 database with Unicode psqlODBC driver? > Does it still break the euro symbol? Yes, the only working combination of oracle transfer, java/Linux and MSAccess/doze clients we can get working is the one we have now. > Ok. I'll try send you changed psqlODBC 08.01.0200 without > autodetecting > client_encoding. Great :) > Not at all. I was in doubt if autodetecting the encoding after user > connection settings is the right way some time ago. Very prescient of you! > Regards, > > Luf Regards Tim Clarke
Ludek Finstrle <luf@pzkagis.cz> writes: > Could you try LATIN1 database with Unicode psqlODBC driver? > Does it still break the euro symbol? > By the way latin9 is the "fixed" version of the obsolete latin1. Among the fixes there was the addition of the euro symbol. <http://czyborra.com/charsets/iso8859.html#ISO-8859-15> My two cents.
Marc. That is a useful interjection, ISTR trying many of the combinations of export coding in latinx from Oracle and postgreSQL database latiny coding. Not sure if I hit 9 tho. The instant we the overnight oracle data transfer out of the way I shall try to dump and rebuild the database in unicode. I hope that cures these issues once and for all... Tim Clarke > -----Original Message----- > From: pgsql-odbc-owner@postgresql.org > [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Marc Herbert > Sent: 10 February 2006 13:56 > To: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] LATIN1/9 conversion.... > > > Ludek Finstrle <luf@pzkagis.cz> writes: > > > Could you try LATIN1 database with Unicode psqlODBC driver? > > Does it still break the euro symbol? > > > > By the way latin9 is the "fixed" version of the obsolete latin1. Among > the fixes there was the addition of the euro symbol. > > <http://czyborra.com/charsets/iso8859.html#ISO-8859-15> > > My two cents.
So what is the upshot of all of this. I have a client who is seeing the same error messages. They have tried the unicode driver but get connection errors which eventually cause IIS to crash. Dave On 10-Feb-06, at 9:23 AM, Tim Clarke wrote: > Marc. > That is a useful interjection, ISTR trying many of the combinations of > export coding in latinx from Oracle and postgreSQL database latiny > coding. Not sure if I hit 9 tho. The instant we the overnight oracle > data transfer out of the way I shall try to dump and rebuild the > database in unicode. I hope that cures these issues once and for > all... > > Tim Clarke > >> -----Original Message----- >> From: pgsql-odbc-owner@postgresql.org >> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Marc Herbert >> Sent: 10 February 2006 13:56 >> To: pgsql-odbc@postgresql.org >> Subject: Re: [ODBC] LATIN1/9 conversion.... >> >> >> Ludek Finstrle <luf@pzkagis.cz> writes: >> >>> Could you try LATIN1 database with Unicode psqlODBC driver? >>> Does it still break the euro symbol? >>> >> >> By the way latin9 is the "fixed" version of the obsolete latin1. >> Among >> the fixes there was the addition of the euro symbol. >> >> <http://czyborra.com/charsets/iso8859.html#ISO-8859-15> >> >> My two cents. > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend >
Haven't got an "upshot" yet since we don't appear to have a problem except for the error messages. Sorry, Dave. It only seems to occur here when we connect a M$ Access front-end database to Postgres and it just bleats as it connects. I don't have an issue storing any characters that I need to. We're throwing out all our M$ apps here anyway due to the huge savings generated; moving all to web front ends. Tim Clarke > -----Original Message----- > From: Dave Cramer [mailto:pg@fastcrypt.com] > Sent: 11 October 2006 20:28 > To: Tim Clarke > Cc: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] LATIN1/9 conversion.... > > > So what is the upshot of all of this. I have a client who is seeing > the same error messages. > > They have tried the unicode driver but get connection errors which > eventually cause IIS to crash. > > Dave > On 10-Feb-06, at 9:23 AM, Tim Clarke wrote: > > > Marc. > > That is a useful interjection, ISTR trying many of the > combinations of > > export coding in latinx from Oracle and postgreSQL database latiny > > coding. Not sure if I hit 9 tho. The instant we the overnight oracle > > data transfer out of the way I shall try to dump and rebuild the > > database in unicode. I hope that cures these issues once and for > > all... > > > > Tim Clarke > > > >> -----Original Message----- > >> From: pgsql-odbc-owner@postgresql.org > >> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Marc Herbert > >> Sent: 10 February 2006 13:56 > >> To: pgsql-odbc@postgresql.org > >> Subject: Re: [ODBC] LATIN1/9 conversion.... > >> > >> > >> Ludek Finstrle <luf@pzkagis.cz> writes: > >> > >>> Could you try LATIN1 database with Unicode psqlODBC driver? > >>> Does it still break the euro symbol? > >>> > >> > >> By the way latin9 is the "fixed" version of the obsolete latin1. > >> Among > >> the fixes there was the addition of the euro symbol. > >> > >> <http://czyborra.com/charsets/iso8859.html#ISO-8859-15> > >> > >> My two cents. > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 6: explain analyze is your friend > > > >
Hello, I'm pretty interested about your web front end. I'm thinking about it and I'm currently looking around about some projects, framework or classes to speed the dev. Do you have some advices about it ? -- +-----------------+ | IoGuiX | | igxnews@free.fr | +-----------------+ Tim Clarke wrote: > Haven't got an "upshot" yet since we don't appear to have a problem > except for the error messages. Sorry, Dave. It only seems to occur here > when we connect a M$ Access front-end database to Postgres and it just > bleats as it connects. I don't have an issue storing any characters that > I need to. We're throwing out all our M$ apps here anyway due to the > huge savings generated; moving all to web front ends. > > Tim Clarke > >> -----Original Message----- >> From: Dave Cramer [mailto:pg@fastcrypt.com] >> Sent: 11 October 2006 20:28 >> To: Tim Clarke >> Cc: pgsql-odbc@postgresql.org >> Subject: Re: [ODBC] LATIN1/9 conversion.... >> >> >> So what is the upshot of all of this. I have a client who is seeing >> the same error messages. >> >> They have tried the unicode driver but get connection errors which >> eventually cause IIS to crash. >> >> Dave >> On 10-Feb-06, at 9:23 AM, Tim Clarke wrote: >> >>> Marc. >>> That is a useful interjection, ISTR trying many of the >> combinations of >>> export coding in latinx from Oracle and postgreSQL database latiny >>> coding. Not sure if I hit 9 tho. The instant we the overnight oracle >>> data transfer out of the way I shall try to dump and rebuild the >>> database in unicode. I hope that cures these issues once and for >>> all... >>> >>> Tim Clarke >>> >>>> -----Original Message----- >>>> From: pgsql-odbc-owner@postgresql.org >>>> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Marc Herbert >>>> Sent: 10 February 2006 13:56 >>>> To: pgsql-odbc@postgresql.org >>>> Subject: Re: [ODBC] LATIN1/9 conversion.... >>>> >>>> >>>> Ludek Finstrle <luf@pzkagis.cz> writes: >>>> >>>>> Could you try LATIN1 database with Unicode psqlODBC driver? >>>>> Does it still break the euro symbol? >>>>> >>>> By the way latin9 is the "fixed" version of the obsolete latin1. >>>> Among >>>> the fixes there was the addition of the euro symbol. >>>> >>>> <http://czyborra.com/charsets/iso8859.html#ISO-8859-15> >>>> >>>> My two cents. >>> ---------------------------(end of >>> broadcast)--------------------------- >>> TIP 6: explain analyze is your friend >>> >> > > ---------------------------(end of broadcast)--------------------------- > TIP 1: 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
We've been using Coldfusion for our public web-site but that is expensive. For purely internal maintenance forms I'm seriously considering Ruby on Rails right now. But this is off the list topic so email me privately if you want more information. Tim Clarke > -----Original Message----- > From: ioguix [mailto:igxnews@free.fr] > Sent: 12 October 2006 09:00 > To: Tim Clarke > Cc: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] LATIN1/9 conversion.... > > > Hello, > > I'm pretty interested about your web front end. I'm thinking about it > and I'm currently looking around about some projects, framework or > classes to speed the dev. > > Do you have some advices about it ? > -- > +-----------------+ > | IoGuiX | > | igxnews@free.fr | > +-----------------+ > > > > Tim Clarke wrote: > > Haven't got an "upshot" yet since we don't appear to have a problem > > except for the error messages. Sorry, Dave. It only seems > to occur here > > when we connect a M$ Access front-end database to Postgres > and it just > > bleats as it connects. I don't have an issue storing any > characters that > > I need to. We're throwing out all our M$ apps here anyway due to the > > huge savings generated; moving all to web front ends. > > > > Tim Clarke > > > >> -----Original Message----- > >> From: Dave Cramer [mailto:pg@fastcrypt.com] > >> Sent: 11 October 2006 20:28 > >> To: Tim Clarke > >> Cc: pgsql-odbc@postgresql.org > >> Subject: Re: [ODBC] LATIN1/9 conversion.... > >> > >> > >> So what is the upshot of all of this. I have a client who > is seeing > >> the same error messages. > >> > >> They have tried the unicode driver but get connection > errors which > >> eventually cause IIS to crash. > >> > >> Dave > >> On 10-Feb-06, at 9:23 AM, Tim Clarke wrote: > >> > >>> Marc. > >>> That is a useful interjection, ISTR trying many of the > >> combinations of > >>> export coding in latinx from Oracle and postgreSQL database latiny > >>> coding. Not sure if I hit 9 tho. The instant we the > overnight oracle > >>> data transfer out of the way I shall try to dump and rebuild the > >>> database in unicode. I hope that cures these issues once and for > >>> all... > >>> > >>> Tim Clarke > >>> > >>>> -----Original Message----- > >>>> From: pgsql-odbc-owner@postgresql.org > >>>> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of > Marc Herbert > >>>> Sent: 10 February 2006 13:56 > >>>> To: pgsql-odbc@postgresql.org > >>>> Subject: Re: [ODBC] LATIN1/9 conversion.... > >>>> > >>>> > >>>> Ludek Finstrle <luf@pzkagis.cz> writes: > >>>> > >>>>> Could you try LATIN1 database with Unicode psqlODBC driver? > >>>>> Does it still break the euro symbol? > >>>>> > >>>> By the way latin9 is the "fixed" version of the obsolete > latin1. > >>>> Among > >>>> the fixes there was the addition of the euro symbol. > >>>> > >>>> <http://czyborra.com/charsets/iso8859.html#ISO-8859-15> > >>>> > >>>> My two cents. > >>> ---------------------------(end of > >>> broadcast)--------------------------- > >>> TIP 6: explain analyze is your friend > >>> > >> > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 1: 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 >