Re: character encoding problems

Hi,

It is off-topic, but I think you are downloading from webservers. A webserver sends an header (Content-Type) with the charset of the page in it.
For example: www.opera.com sends me 'Content-Type: text/html; charset=utf-8' or their can be a header in the html document '<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />'.

This charset is what you need to put in your InputStreamReader. If you aren't using http, you should use something else to determine the charset. If you do use http see http://jakarta.apache.org/commons/httpclient/index.html. This package will handle the charset for you.

NB: If a webserver sends you iso8859-1 you can better use windows-1252 to read the info. That is something of a historical issue. See: http://en.wikipedia.org/wiki/Windows-1252 and http://en.wikipedia.org/wiki/ISO_8859-1.

Ronald.

On Tue May 22 13:11:17 CEST 2007 Daniel Kirk <[email protected]> wrote:
> Hi Ronald,
> 
> Thanks for your email. I guess I'm on a hiding to nothing because of 
> your first point:
> Is the remote side sending you correct UTF-8?
> 
> The data can come from over 100 different sources, and to expect them 
> all to encode using UTF-8 is forlorn. I thought that by setting my 
> reader to use UTF-8 encoding that it would be read in UTF-8 format - but 
> from what you're saying that's not the case if it's not actually sent in 
> UTF-8 format. Is that right? If I can find out what encoding they're 
> using, is it possible to covert it to UTF-8?
> 
> regards
> 
> Daniel Kirk
> Managing Editor
> -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
> web: http://www.sportspunter.com
> http://www.toptipper.com
> http://www.pickswarehouse.com
> email : [email protected]
> phone : +61(0)410 409 237
> fax : +61(0)2 66462847
> Internet Digital Media Australia
> ABN : 51275059681
> -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~- 
> 
> 
> 
> Ronald Klop wrote:
> 
> > On Tue May 22 06:35:21 CEST 2007 Daniel Kirk <[email protected]> 
> > wrote:
> >
> > Hi There,
> >
> > I'm having troubles trying to convert 'everything' to UTF-8 format.
> >
> > I'm using the db drivers from /mysql-connector-java-3.1.11-bin.jar
> > connected to a version 5.0.27 database on a linux platform.
> >
> > I get a lot of data containing characters in multiple languages eg
> > Swedish (as below), Norwegian, Greek etc
> >
> > Firstly, I have robots downloading data off the 'net. They should be
> > using UTF-8 encoding:
> > br = new BufferedReader(new InputStreamReader(is,
> > "UTF-8"));
> >
> > The robots then put some names of sports teams in a db, and they
> > seemed
> > to be stored as you'd hope:
> > mysql> select * from MissingTeams where competitionid = 336;
> > +----------------------+---------------+-------------+
> > | team | competitionid | bookmakerid |
> > +----------------------+---------------+-------------+
> > | B?linge | 336 | 54 |
> > | Sunnan? SK | 336 | 54 |
> > | KIF ?rebro DFF | 336 | 54 |
> > | Kopparbergs/G?teborg | 336 | 54 |
> > | Falk?pings KIK | 336 | 54 |
> > | LdB FC Malm? | 336 | 54 |
> > | Ume? IK | 336 | 54 |
> > | Link?pings FC | 336 | 54 |
> > +----------------------+---------------+-------------+
> >
> >
> > But when I read this in through a result set, the encoding problem
> > kicks in:
> >
> > I'm just using a simple system.out.println:
> > while(rs.next()) {
> > String team = rs.getString(1);
> > if (competitionid == 336) System.out.println("alias is :
> > " + team);
> >
> > Which produced output like:
> >
> > alias is : B???linge
> > alias is : Falk???pings KIK
> >
> > So I tried connecting using this as part of the query string:
> > &characterEncoding=UTF-8
> >
> > Which did not have any effect at all.
> >
> > So I tried setting all the character set variables before my query.
> >
> > Databases.executeUpdate("set character set 'utf8'", con);
> > Databases.executeUpdate("set character_set_connection =
> > 'utf8'", con);
> > Databases.executeUpdate("set character_set_database =
> > 'utf8'", con);
> > Databases.executeUpdate("set character_set_server = 'utf8'",
> > con);
> >
> > And the output from my query changed, but still not right:
> > alias is : B??linge
> > alias is : Falk??pings KIK
> >
> > So what I did next was change the character set variables where I add
> > the data into the "Missing Teams" table as above - so that its
> > Connection also used 'UTF-8'.
> >
> > The result was that the characters were converted into a ? into
> > the db:
> >
> > mysql> select * from MissingTeams where competitionid = 336;
> > +----------------------+---------------+-------------+
> > | team | competitionid | bookmakerid |
> > +----------------------+---------------+-------------+
> > | B?linge | 336 | 54 |
> > | Sunnan? SK | 336 | 54 |
> > | KIF ?rebro DFF | 336 | 54 |
> > | Kopparbergs/G?teborg | 336 | 54 |
> > | Falk?pings KIK | 336 | 54 |
> > | LdB FC Malm? | 336 | 54 |
> > | Ume? IK | 336 | 54 |
> > | Link?pings FC | 336 | 54 |
> > +----------------------+---------------+-------------+
> >
> > Does anyone have any suggestions how I can write the data to the db
> > using the correct encoding, and read it back out also using the
> > correct
> > encoding?
> >
> > thanks
> >
> > -- 
> > Daniel Kirk
> > Managing Editor
> > -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
> > web: http://www.sportspunter.com
> > http://www.toptipper.com
> > http://www.pickswarehouse.com
> > email : [email protected]
> > phone : +61(0)410 409 237
> > fax : +61(0)2 66462847
> > Internet Digital Media Australia
> > ABN : 51275059681
> > -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
> >
> >
> > -- 
> > MySQL Java Mailing List
> > For list archives: http://lists.mysql.com/java
> > To unsubscribe:
> > http://lists.mysql.com/[email protected]
> >
> > There are a lot of places where this can go wrong.
> > Check on every boundary between systems if the charset is handled 
> > correctly.
> >
> > - Is the remote side sending you correct UTF-8?
> > - Is the data correct in the database? Send your 'SHOW VARIABLES' and 
> > 'SHOW CREATE TABLE ...'.
> > - When you read data from the database is it correct?
> > - Does your System.out.println() print the data correct. (if you have 
> > unix, does your xterm understand UTF-8?)
> >
> > In stead of printing the String you can print the values of the bytes 
> > of String.getBytes("UTF-8"). So you are sure, the byte codes are correct.
> >
> > Ronald.
> 
> 
> -- 
> MySQL Java Mailing List
> For list archives: http://lists.mysql.com/java
> To unsubscribe: http://lists.mysql.com/[email protected]
>