Re: UTF-8 -> ISO8859-1 conversion problem - Mailing list pgsql-general

Thanks for the detailed reply, you've confirmed what I suspected. :)

I guess I have some work to do!


On Fri, 2004-10-29 at 10:19, J. Michael Crawford wrote:
>    In my experience, there are just some characters that don't want to be
> converted, even if they appear to be part of the normal 8-bit character
> system.  We went to Unicode databases to hold our Latin1 characters because
> of this.  There was even a case where the client was cutting and pasting
> ascii text into our database, and it just wouldn't take some of the
> letters, giving the same error you reported.
>
>    I'm going to send a more detailed post on the topic, but in general,
> we've found that there are four things that need to be done (four, if
> you're not serving up web pages) for Latin1 characters to work on multiple
> platforms.
>
>    1.  Create the database in Unicode so that it will hold anything you
> throw at it.
>
>    2.  When importing data, set the encoding in the script that loads the
> data, or if there's no script, use the "SET CLIENT_ENCODING TO (encoding)"
> command.  Setting the encoding in a tool like pgManager is not always
> enough.  Use this to be sure.
>
>    3.  When retrieving data in a java application, the JVM encoding will
> vary from JVM to JVM, and no attempt on our part to change the JVM encoding
> or translate the encoding of the database strings has worked, either to or
> from the database.  We spent weeks going through every permutation
> getBytes("ISO-8859-1") and related calls we could find, but to no
> avail.  The JVM will tell you it has a new encoding, but postgres will
> return gibberish.  You can translate the bytes, or get a translated string,
> but it's all the same garbage.  The solution: set the client encoding
> manually through a jdbc prepared statement.  Once you set the client
> encoding properly, all seems to be fine:
>
> String DBEncoding = "anEncoding"  //use a real encoding, either returned
> from the jvm or explicitly stated
> PreparedStatement statement = dbCon.prepareStatement("SET CLIENT_ENCODING
> TO '" + DBEncoding + "'");
> statement.execute();
>
>    4.  If writing html for a web page, make sure the encoding of the web
> page matches the encoding of the strings you're throwing at it.  So if you
> have a Linux JVM that has a "UTF-8" encoding, the web page will need the
> html equivalent:
>
> <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
>
> ---
>
>    This is likely far more information than you require, but I thought I'd
> add it anyway so that the information is in the archives.  It took us
> months to solve our problem, even with help from the postgres community, so
> I at least want the basics to be posted while I get my act together and
> write something with more detail.
>
>     - Mike
>
>
> At 12:12 PM 10/29/2004, Cott Lang wrote:
>  >ERROR: could not convert UTF-8 character 0x00ef to ISO8859-1
>  >
>  >Running 7.4.5, I frequently get this error, and ONLY on this particular
>  >character despite seeing quite a bit of 8 bit. I don't really follow why
>  >it can't be converted, it's the same character (239) in both character
>  >sets. Databases are in ISO8859-1, JDBC driver is defaulting to UTF-8.
>  >
>  >Am I flubbing something up?  I'm probably going to (reluctantly) convert
>  >to UTF-8 in the database at some point, but it'd sure be nice if this
>  >worked without that. :)
>  >
>  >thanks!
>  >
>  >
>  >
>  >
>  >
>  >
>  >
>  >---------------------------(end of broadcast)---------------------------
>  >TIP 8: explain analyze is your friend
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html