1

In my C++ application (On Windows x64 being developed using VS2012) I am using MySQL Connector/C++ to connect MySQL database. I've table ALL_USERNAMES that has a column:

USERNAME VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci

However, I am not sure how can I fire utf8 queries through my C++ application. Method sql::Statement::executeUpdate accepts sql::SQLString type. It is essentially std::string.

So if I do something like this:

std::string strQuery = "INSERT INTO ALL_USERNAMES (USERNAME) VALUES ('अतुल')"
executeUpdate(strQuery);

And when I check the table record through MySQL Workbench I see USERNAME as all question marks (???????).

I couldn't find anything about this in Connector/C++ documentation. Any idea how to solve this?

What I wanted to try:

In MySQL C API there are functions like:

mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "utf8"); 
mysql_options(&mysql, MYSQL_INIT_COMMAND, "SET NAMES utf8");

which I wanted to try as I suspect it will solve my problem. But I couldn't find its counterpart in Connector/C++.

UPDATE:

SHOW CREATE TABLE gives me this:

'ALL_USERNAMES', 'CREATE TABLE `all_usernames` (\n  `USERNAME` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8'

And my client code is here (this was before I written my own answer):

sql::Connection *m_pConnection = NULL;
sql::Statement *m_pStatement = NULL;
sql::Driver *driver = NULL;

driver = get_driver_instance();
m_pConnection = driver->connect("tcp://127.0.0.1:3306", "username", "password");
m_pConnection->setSchema("DBNAME");
m_pStatement = m_pConnection->createStatement();
int RetVal = m_pStatement->executeUpdate("INSERT INTO ALL_USERNAMES (USERNAME) VALUES ('अतुल')");
ROMANIA_engineer
  • 54,432
  • 29
  • 203
  • 199
Atul
  • 3,778
  • 5
  • 47
  • 87

2 Answers2

2

  • Do not use the deprecated mysql_* interface; use mysqli_* or PDO.

  • Use mysqli_set_charset('utf8');, not the commands indicated.

  • Question marks occur because:

    • The client has a valid character (good), and
    • The SET NAMES agrees with the encoding that the client has (good), but
    • The target column's CHARACTER SET does not include the intended character. So...

  • Declare the USERNAME to be CHARACTER SET ut8. (Please provide SHOW CREATE TABLE if you have already done such.)

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • As per current implementation I don't call any `mysql_*` or `my_sqli_*` functions. I've updated question with `SHOW CREATE TABLE` output and C++ source (in Visual Studio 2012 on Windows x64). Please have a look. Thanks. – Atul Jul 05 '15 at 13:19
  • The source code above was giving me warnings `warning C4566: character represented by universal-character-name '\u0905' cannot be represented in the current code page (1252)` which I thought because `std::string` can't accept unicode literals. So I have to use `std::wstring` and `_T()` for unicode for that. Then all it needed is to convert that unicode to `std::string`. And for that I used method I mentioned in my answer. Please let me know is this approach correct? – Atul Jul 05 '15 at 13:25
  • FYI, \u0905 represents DEVANAGARI LETTER A = अ – Rick James Jul 06 '15 at 22:21
  • Yes it's 'A' of Devnagari :) – Atul Jul 07 '15 at 01:20
1

I found the way to do this with the help of an answer in this question.

  1. First get the unicode string in wstring for example:

    std::wstring wname = _T("अतुल");

  2. Then convert the string above to utf-8 string using this function:

    #include <codecvt>
#include <string>

// convert wstring to UTF-8 string
std::string wstring_to_utf8 (const std::wstring& str)
{
    std::wstring_convert<std::codecvt_utf8<wchar_t>> myconv;
    return myconv.to_bytes(str);
}

    Thus our string becomes:

    std::string name = wstring_to_utf8(wname);

  3. You can now use the obtained std::string in MySQL query:

    std::string strQuery = "INSERT INTO ALL_USERNAMES (USERNAME) VALUES ('" + name + "')";
Community
  • 1
  • 1
Atul
  • 3,778
  • 5
  • 47
  • 87