character encoding problems

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]