UTF-8 settings and woes

[robert kuzelj]

hi,

i am trying to write utf-8 data via java into sql but
it wont work as expected.

first my setup

- suse 9.0
- kde 3.2

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_set_database   | utf8                       |
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character-sets-dir       | /usr/share/mysql/charsets/ |
| character_set_results    | utf8                       |
| version                  | 4.1.1-alpha-standard       |
| version_comment          | Official MySQL RPM         |
| version_compile_machine  | i686                       |
| version_compile_os       | pc-linux                   |
| wait_timeout             | 28800                      |
+--------------------------+----------------------------+

now i have the following script
[code]
[EMAIL PROTECTED]:> more example_insert.sql
insert into CTPE_V01_00.CUSTOMER (ID, FAMILY_NAME, GIVEN_NAME)
       values ('01', 'Käßsel', 'Böb');
insert into CTPE_V01_00.CUSTOMER (ID, FAMILY_NAME, GIVEN_NAME)
       values ('02', 'Ægÿl', 'Àlbért');
insert into CTPE_V01_00.CUSTOMER (ID, FAMILY_NAME, GIVEN_NAME)
       values ('03', '???????????', '???????????');
[/code]

which i execute and afterwards display is completely ok

[code]
mysql < example_insert.sql

mysql> use CTPE_V01_00;
mysql> select FAMILY_NAME, GIVEN_NAME from CUSTOMER;
+------------------------+------------------------+
| FAMILY_NAME            | GIVEN_NAME             |
+------------------------+------------------------+
| Käßsel               | Böb                   |
| Ægÿl                 | Àlbért               |
| ??????????? | ??????????? |
+------------------------+------------------------+
3 rows in set (0,00 sec)

mysql>
[/code]

now i want to do that with java code instead. i tried
the following connectors

- mysql-connector-java-3.0.12-production-bin.jar
- mysql-connector-java-3.1.1-alpha-bin.jar

here's my code

[code]
public void testMySql()
{
  String url = "jdbc:mysql://localhost/" +
               "?autoReconnect=true" +
               "&useUnicode=true" +
               "&characterEncoding=utf8";

  String cls = "com.mysql.jdbc.Driver";
  String user = "...";
  String pwd = "...";

  execute(url, cls, user, pwd);
}

public void execute(String _con, String _class,
                    String _user, String _pwd){
  Connection con;
  Statement stmt;
  try{
     Class.forName(_class);}
  catch (java.lang.ClassNotFoundException e){
     System.err.print("ClassNotFoundException: ");
     System.err.println(e.getMessage());}

  try{
    String sql1 = "insert into CTPE_V01_00.CUSTOMER " +
                  "(ID, SHORT_ID, FAMILY_NAME, GIVEN_NAME) " +
                  "values ('01', '01','Käßsel', 'Böb');";
    String sql2 = "insert into CTPE_V01_00.CUSTOMER " +
                  "(ID, SHORT_ID, FAMILY_NAME, GIVEN_NAME) " +
                  "values ('02', '02','Ægÿl', 'Àlbért');";
    String sql3 = "insert into CTPE_V01_00.CUSTOMER " +
                  "(ID, SHORT_ID, FAMILY_NAME, GIVEN_NAME) " +
                  "values ('03', '03','???????????'," +
                  " '???????????');";

    con = DriverManager.getConnection(_con, _user, _pwd);
    stmt = con.createStatement();
    stmt.executeUpdate(sql1);
    stmt.executeUpdate(sql2);
    stmt.executeUpdate(sql3);
    stmt.close();
    con.close();}
  catch (SQLException ex){
    ex.printStackTrace();}}

[/code]

now this results in the following output on the console
[code]
mysql> select FAMILY_NAME, GIVEN_NAME from CUSTOMER;
+----------------------------------------------+----------------------------------------------+
| FAMILY_NAME | GIVEN_NAME |
+----------------------------------------------+----------------------------------------------+
| KäÃsel | Böb |
| Ãgÿl | Ãlbért |
| инÑеÑнаÑион | инÑеÑнаÑион |
+----------------------------------------------+----------------------------------------------+
3 rows in set (0,00 sec)
[/code]

<sigh> not exactly what i expected.

what is also interesting is if i use DBFace (a eclipse plugin) in
combination with the drivers i mentioned the result is better - it is
not correct but it is better (not so much garbage).

any help is greatly appreciated.

tia

ciao robertj

smime.p7s
Description: S/MIME Cryptographic Signature