MySQL - question marks

Question

when inserting from my app some chinese characters they get written into DB as '???'. Needless to say it all works fine from within the built in command line mysql client.

Connection string:

--user-db-uri = jdbc:mysql://localhost/tigasedb?user=tigase_user&password=tigase_passwd&useUnicode=true&characterEncoding=utf8&noAccessToProcedureBodies=true

Code:

try {
            conn_valid_st.setQueryTimeout(query_timeout);
            st = conn.prepareStatement("SET character_set_client = utf8");
            st.execute();
            st.close();
            st = conn.prepareStatement("SET character_set_connection = utf8");
            st.execute();
            st.close();
            st = conn.prepareStatement("SET character_set_results = utf8");
            st.execute();
            st.close();
            st = conn.prepareStatement("SET collation_connection = utf8_general_ci");
            st.execute();
            st.close();
            st = conn.prepareStatement("SET NAMES utf8");
            st.execute();
            st.close();
            st = conn.prepareStatement("SET CHARACTER SET utf8");
            st.execute();
            st.close();
        } catch (SQLException ex) {
            // Ignore for now, it seems that PostgreSQL does not support this method
            // call yet
            if (null != st)
                st.close();

            log.log(Level.WARNING, "DB server said: {0}", ex.toString());
        }

What is it that eludes me ?

EDIT:

create table ... ENGINE=InnoDB default character set utf8 collate utf8_unicode_ci ROW_FORMAT=DYNAMIC;

inserted characters: 在健身房

Verification is done by the built in command line mysql client.

EDIT: http://docs.oracle.com/cd/E17952_01/refman-5.0-en/connector-j-reference-charsets.html did not help

Check the value of the system property "file.encoding". If that is not "UTF-8", then you need to explicitly specify "UTF-8" as the character encoding whenever you decode bytes to characters. For example, when you call a String constructor with a byte[], or use an InputStreamReader.

Problems reading/writing UTF-8 data in MySQL from Java using JDBC connector 5.1 - did not help

Declared string variables in stored procedures as _loc VARCHAR(128) CHARSET utf8 - did not help

What characters are you inserting? How do you verify what the database has stored? What charset do you use for the column? — Joni, Jul 31 '13 at 11:12
The command line client may not be able to display Chinese correctly, depending on terminal emulator and locale settings. You can use the hex function to see what's actually stored: `select hex(yourcolumn) from yourtable where ...` — Joni, Jul 31 '13 at 13:31
@Joni in hex it is 3F3F3F3F. Checked the string value just before insertion in java code and it is ok... — kellogs, Jul 31 '13 at 16:48
You seem to be doing everything right. I can think of only one thing more to check though: is the column defined with a utf8 charset and collation? In MySQL each column can have a charset and collation of its own; they don't have to use the default for the table. — Joni, Jul 31 '13 at 22:44

score 0 · Answer 1 · answered Jul 31 '13 at 11:11

0

Your database table column should be created with proper charset and collation. You can define theit while creating or altering a table:

col_name {CHAR | VARCHAR | TEXT} (col_length)
    [CHARACTER SET charset_name]
    [COLLATE collation_name]

Here is the mysql link to help you learn more about it:

http://dev.mysql.com/doc/refman/5.0/en/charset-column.html

answered Jul 31 '13 at 11:11

Juned Ahsan

67,789
12
98
136

@kellogs So you mean to say it is saved correctly in db? Then where are you seeing the ??? marks – Juned Ahsan Jul 31 '13 at 11:22

score 0 · Answer 2 · answered Aug 02 '13 at 08:08

It works fine with current 5.1.26 driver version:

createTable("testBugF1", "(`str` varchar(10) ) default character set utf8 collate utf8_unicode_ci ");
String str = "在健身房";

Properties props = new Properties();
props.put("characterEncoding", "utf8");
Connection c = getConnectionWithProps(props);

this.stmt = c.createStatement();
this.stmt.executeUpdate("insert into testBugF1 values (\""+ str +"\")");
this.rs = this.stmt.executeQuery("select * from testBugF1");
assertTrue(this.rs.next());
String res = this.rs.getString(1);
System.out.println(res);
assertEquals(str, res);

Do you use an old driver? How do you insert characters?

MySQL - question marks

2 Answers2