Can't write UTF8 chars

[Eran Kaplan]

Hi all,

I don't succeed to write to my MySql db with UTF-8.
I can read Characters in UTF-8 but I can't write.
If I try to save aaaéaaa, I get in my db browser (Navicat) aaa?aaa.
With my DB browser I can read and write aaaéaaa freely.


I tried anything I can think of:
• Start mysql server with --character-set-server=utf8
• Define a new table with only UTF8 columns
• Use the JVM flag -Dfile.encoding=utf8
• Compile with: javac -encoding UTF-8
• connection url: ?useUnicode=yes&characterEncoding=utf8
• Convert the String object itself: new String(aString.getBytes("utf8"),"utf8");
• I even tried to translate the whole query to UTF-8
• statement.executeUpdate("set names utf8");
• statement.executeUpdate("set character_set_results=utf8");

What did I miss???

I'm using:
OS: windows XP
Java: Sun 1.6.0_15
driver: mysql-connector-java-5.0.4.jar
Mysql server: 5.1.37


Here's the source code:

import java.sql.*;

public class SampleJdbcCharset {

	public static void main(String[] args) throws Exception {

		Connection conn = null;
		Statement stmt = null;
		
		try {
			conn = openConn();

			stmt = conn.createStatement();
			stmt.executeUpdate("set names utf8");
			stmt.executeUpdate("set character_set_results=utf8");

			String tableName = "charsutf8";

			stmt.execute("truncate table " + tableName);

			String aStr = "aaaéaaa" + System.currentTimeMillis();
			
			byte[] strBytes = aStr.getBytes("utf8");
			String aStrUtf8 = new String(strBytes,"utf8");
			String values = "'" + aStrUtf8 + "','" + aStrUtf8 + "','" + aStrUtf8 + "'";

			String insertStr = "insert into "+tableName+" values (" + values + ")";
			stmt.execute(insertStr);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			stmt.close();
			conn.close();
		}
	}



	private static Connection openConn() throws Exception {

		String url = "jdbc:mysql://localhost:3306/";
		String dbName = "charset-test";
		String connParams = "?useUnicode=yes&characterEncoding=utf8";
		String driver = "com.mysql.jdbc.Driver";
		String userName = "root";
		String password = "";

		Class.forName(driver).newInstance();
		String connStr = url+dbName+connParams;
		Connection conn = DriverManager.getConnection(connStr,userName,password);
		return conn;
	}
}

Any help will be appreciated,
Eran

[Ulf Dittmer]

String aStr = "aaaéaaa" + System.currentTimeMillis();
byte[] strBytes = aStr.getBytes("utf8");
String aStrUtf8 = new String(strBytes,"utf8");

This part is odd. For starters, it's better to use the official encoding names: "UTF-8" instead of "utf8".

But Java uses UTF-16 for String objects, so aStrUtf8 is not actually in UTF-8, but UTF-16. The second and third line perform the exact opposites of one another - aStr should be identical to aStrUtf8.

[Sean Clark]

Hey,

I had the exact same problem and it was down to the default character encoding on the JVM (it was set to American ASCII so all unknown characters got a ?).

Anyway you can check the encoding with a simple bit of code, this link shows how to do it:
http://www.rgagnon.com/javadetails/java-0505.html

Good luck!

Sean

[Eran Kaplan]

Thanks for the answers.

Eventually, I changed my MySql server from the .zip without installer version to the .msi Windows Essentials version and it worked.

Ulf Dittmer, you are right. I got confused by some code sample in the web.

Thanks again.