JDBC to mysql Character encoding

Question

Ok so i'll demonstrate my problem on tutorial code from https://developers.google.com/apps-script/jdbc

function insert() {
  var conn = Jdbc.getConnection("jdbc:mysql://<host>:<port>/<instance>", "user", "password");

  conn.setAutoCommit(false);
  var stmt = conn.prepareStatement("insert into person (lname,fname) values (?,?)");
  var start = new Date();
  for (var i = 0; i < 5000; i++) {
    // Objects are accessed using 1-based indexing
    stmt.setObject(1, 'firstName' + i);
    stmt.setObject(2, 'lastName' + i);
    stmt.addBatch();
  }
  var res = stmt.executeBatch();
  conn.commit();
  conn.close();
  var endTime = new Date();
  Logger.log("time is " + (endTime.getTime() - start.getTime()) + "ms");
  Logger.log("res has " + res.length);
}

Im from slovakia and we have special characters like žťščľýá after i change this line

stmt.setObject(1, 'firstName' + i);

to

stmt.setObject(1, 'žťščľýá' + i);

I'll get ???? in my db.I have utf8_general_ci in my db and my table picture

I've seen this JDBC Service with MySQL : UTF-8 encoding / decoding and tried Utilities.newBlob("").setDataFromString("foo", "UTF-8").getDataAsString("UTF-16") also this JDBC character encoding where lot of people are suggesting to use ?characterEncoding=utf8 after my connection link but i'll get error message.

Arun Nagarajan · Answer 1 · 2013-05-07T14:31:13.243

I am successfully able to write to Google Cloud SQL (MySQL 5.5) using the characters you have there. That is the only MySQL DB that I have access to.

You an see that a simple table I have called animals get the right data in it. I even tried Japanese and it worked well.

enter image description here

Here is the code I used -

function writeEncodingIssue() {
  var conn = Jdbc.getCloudSqlConnection("jdbc:google:rdbms://<MY_INTSANCE_NAME>/mysql");
  conn.setAutoCommit(false);
  var stmt = conn.prepareStatement("insert into animals (name) values (?)");
  for (var i = 0; i < 2; i++) {
    stmt.setObject(1, 'žťščľýá' + i);//ショッピング
    stmt.addBatch();
  }
  var res = stmt.executeBatch();
  conn.commit();
  conn.close();
}

Perhaps you can try to write a test program in Java or something else to isolate this to an Apps Script issue?

EDIT: I've bee able to reproduce this issue with MySQL. The issue tracker item has been updated. The Apps Script team is investigating this.

JDBC to mysql Character encoding

1 Answers1