Re: character encoding problems

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.