CODEWITHSUNDEEP
javamysqlunicodejdbcutf-8
Peter Kelly
Peter Kelly

Reputation: 14411

Inserting Korean characters via JDBC through MYSQL stored procedure giving error

I believe I am using the correct queryString params for jdbc connection

jdbc:mysql://localhost:3306/databasename?useUnicode=true&characterEncoding=utf8&noAccessToProcedureBodies=true

My database is correctly set-up to support utf-8 (default character variables). 

character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8

My sproc defines each parameter as utf8 where necessary 

IN _shortDesc MEDIUMTEXT character set utf8,

CALLing the sproc through MySQLWorkbench works as expected. However, calling it from Java results in an exception

java.sql.SQLException: Incorrect string value: '\xAC\xED\x00\x05t\x00...' for column '_shortDesc'

Interestingly, changing sproc definition for relevant fields to utf16 or ucs2 results in no exception but incorrect Korean characters in the table.

I have also tried using a byte array when building the sql parameters in Java

getBytes(Charset.forName("UTF-8"))

But the exception is still thrown.

What is the correct way to set this up to correctly pass CJK characters through Java to MySQL via a stored procedure?

EDIT I am using SimpleJdbcCall from the spring framework

DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql://server/test?characterEncoding=utf8");
ds.setUsername("user");  
ds.setPassword("pass");

// must explicitly declare params
SimpleJdbcCall jdbc = new SimpleJdbcCall(ds)
    .withProcedureName("sp_gms_addGameTranslationsTest");

SqlParameterSource in = new MapSqlParameterSource()
    .addValue("_test", "그러나 사디라");

jdbc.execute(in);

Upvotes: 1

Views: 1754

Answers (1)

Peter Kelly
Peter Kelly

Reputation: 14411

The root problem was I am using springframework SimpleJdbcCall to execute the stored procedure. That simplifies configuration by using databases metadata to figure things out for you. By explicitly declaring my input parameters when using that object, the call works fine.

e.g.

DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql://server/test?characterEncoding=utf8");
ds.setUsername("user");  
ds.setPassword("pass");

// must explicitly declare params
SimpleJdbcCall jdbc = new SimpleJdbcCall(ds)
    .withProcedureName("sp_gms_addGameTranslationsTest")
    .declareParameters(
        new SqlParameter("_test", Types.VARCHAR)
);

SqlParameterSource in = new MapSqlParameterSource()
    .addValue("_test", "그러나 사디라");

jdbc.execute(in);

I realise this answer was impossible to arrive at from my original question, so I have added an edit.

Upvotes: 1

Related Questions