hi mark,
If you specify UTF-8 as the characterEncoding connection property, then that is the transform that is used from client -> server. The transform that is used from server -> client is whatever character set the column in the table is set to when you created the table (or conversely if you use cast/convert in SQL to change it to some other character set).
so you say ;-) and i still have my doubts about it (but more on that later). what i have done now is to extract a junit test as requested by you (TestUTF8.java). i executed the test on two different machines now (suse linux 9.0 and WinXp both running mysql 4.1.1-a). as you would expect the tests run perfectly well.
but again there is the problem that every other client displays garbage. now i have tried 3 additional clients on my xp-machine (SqlExplorer in intellij, mysqlcc.0.9.4 and mysql on a console). when running the junit tests all oft these win-clients display garbage but that was to be expected.
now i again made the reverse check. i executed some insert and select scripts on the console (cygwin) and displayed the results (attached con_create.sql and con_select.sql) again no real surprise. sqlexplorer displayed the data correct as did mysqlcc. only on the console the results were garbled. this was to be expected as i dont know how to set the locale on windows. when i piped the results of the con_select.sql into a file and displayed this via a UTF-8-enabled editor everything was sound (attached out.txt)
so the facts are as this - if i write with my own test-app only my test-app is able to read the data correctly. - if i write via a sql-script (executed via the shell or via a java-application like sqlexplorer [that btw uses the same driver and the same connection-str as my own test-app]) every client app can display the data correctly (except the windows console) ONLY my app is not able to read it correctly (well it has to switch to getBytes instead of getString then it works).
at the moment my guess is that in my test-app a double transformation is taking place, that does not happen in the other java-apps (JFaceDBC and SqlExplorer).
any comments?
ciao robertj
ID FAMILY_NAME GIVEN_NAME 01 Käßsel Böb 02 Ægÿl Àlbért 03 интернацион интернацион
drop database if exists UTF8_TEST; create database UTF8_TEST; use UTF8_TEST;
drop table if exists UTF8_TEST;
create table UTF8_TEST
( ID CHAR (02),
FAMILY_NAME CHAR (32),
GIVEN_NAME CHAR (32),
) TYPE = INNODB;
insert into UTF8_TEST (ID, FAMILY_NAME, GIVEN_NAME) values ('01', 'Käßsel', 'Böb');
insert into UTF8_TEST (ID, FAMILY_NAME, GIVEN_NAME) values ('02', 'Ægÿl',
'Àlbért');
insert into UTF8_TEST (ID, FAMILY_NAME, GIVEN_NAME) values ('03',
'интернацион', 'интернацион');
use UTF8_TEST; select * from UTF8_TEST order by ID;
package org.pragmatico.ctpe.test.service.umlaut;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import junit.framework.TestCase;
import junit.framework.Test;
import junit.framework.TestSuite;
public class TestUTF8 extends TestCase
{
public TestUTF8(String _name)
{
super(_name);
}
public static Test suite()
{
TestSuite suite;
suite = new TestSuite(TestUTF8.class);
return suite;
}
public void testUTF8() throws Exception
{
String url =
"jdbc:mysql://localhost/?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8";
String cls = "com.mysql.jdbc.Driver";
String user = "";
String pwd = "";
Connection con = null;
Statement stmt;
try
{
Class.forName(cls);
con = DriverManager.getConnection(url, user, pwd);
stmt = con.createStatement();
stmt.executeUpdate( "drop database if exists UTF8_TEST");
stmt.executeUpdate( "create database UTF8_TEST");
stmt.executeUpdate( "use UTF8_TEST");
stmt.executeUpdate( "drop table if exists UTF8_TEST");
stmt.executeUpdate( "create table UTF8_TEST" +
"( ID CHAR (02)," +
" FAMILY_NAME
CHAR (32)," +
" GIVEN_NAME
CHAR (32)," +
") TYPE = INNODB");
stmt.executeUpdate("insert into UTF8_TEST (ID, FAMILY_NAME, GIVEN_NAME) " +
"values ('01', 'Käßsel', 'Böb')");
stmt.executeUpdate("insert into UTF8_TEST (ID, FAMILY_NAME, GIVEN_NAME) " +
"values ('02', 'Ægÿl', 'Àlbért')");
stmt.executeUpdate("insert into UTF8_TEST (ID, FAMILY_NAME, GIVEN_NAME) " +
"values ('03', 'интернацион',
'интернацион')");
ResultSet rs = stmt.executeQuery("select * from
UTF8_TEST.UTF8_TEST order by ID");
assertTrue(rs.next());
assertEquals("Käßsel", rs.getString("FAMILY_NAME"));
assertEquals("Böb", rs.getString("GIVEN_NAME"));
assertTrue(rs.next());
assertEquals("Ægÿl", rs.getString("FAMILY_NAME"));
assertEquals("Àlbért", rs.getString("GIVEN_NAME"));
assertTrue(rs.next());
assertEquals("интернацион", rs.getString("FAMILY_NAME"));
assertEquals("интернацион", rs.getString("GIVEN_NAME"));
}
catch(Exception ex)
{
ex.printStackTrace();
}
}
public static void main(String[] _args)
{
junit.textui.TestRunner.run(suite());
}
}
smime.p7s
Description: S/MIME Cryptographic Signature
