Re: character encoding problems

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]