Cannot execute mysql query which contains Chinese character in google apps script

Question

I have a google apps script like the following:

// Initialize DB variables
var address = 'my-address'
var user = 'my-user-name'
var password = 'my-password'
var db = 'my-db'
var dbUrl = 'jdbc:mysql://' + address + '/' + db

function testUtf8() {

  // Generate an SQL query
  var conn = Jdbc.getConnection(dbUrl, user, password)
  var stmt = conn.createStatement();
  var results = stmt.executeQuery('SELECT * FROM my_table WHERE col_name=\'中文\';')

  // Display query result
  var colCount = results.getMetaData().getColumnCount()
  while (results.next()) {
    var colCount = results.getMetaData().getColumnCount()  // No result is retured even though there is a value of `中文` in col_name.
    for (var col = 0; col < colCount; col++) { 
      Logger.log(results.getString(col + 1))
    }
  }

  // Clean up
  stmt.close();
  conn.close();
}

The SQL query fails to find the data even if I have a value of 中文 in col_name in the table.

When I change the query from SELECT * FROM my_table WHERE col_name='中文'; to SELECT * FROM my_table WHERE col_name='abc';, it successfully returns the data.

So this must be a problem of encoding.

How can I successfully execute an SQL query which contains Chinese character?

Get the hex of `中文`. If it is `D6D0CEC4`, you need `CHARACTER SET gbk`. If you get `E4B8ADE69687`, you need `CHARACTER SET utf8mb4`. — Rick James, Jun 18 '18 at 02:58
Do you have a column named `中文`? Or are you looking for text with that value? — Rick James, Jun 18 '18 at 02:59
https://stackoverflow.com/questions/5405236/jdbc-mysql-utf-8-string-writing-problem refers to getting question marks; that is a _different_ problem. So, I reopened this; it is _not_ a dup of that one. — Rick James, Jun 18 '18 at 03:01

score 0 · Accepted Answer · answered Jun 15 '18 at 07:28

Changing

var dbUrl = 'jdbc:mysql://' + address + '/' + db to var dbUrl = 'jdbc:mysql://' + address + '/' + db

to

var dbUrl = 'jdbc:mysql://' + address + '/' + db + '?useUnicode=true&characterEncoding=UTF-8'

solves the problem.

It tells jdbc to use utf-8 encoding.

Cannot execute mysql query which contains Chinese character in google apps script

1 Answers1