SQLite Unicode with Visual C++ 2015

Question

This is my first SQLite + VC 2015 program, my project is in UTF-8. I have SQLite table in which I want to save Chinese. For example, I have table:

Cities { Id TEXT PRIMARY KEY, Desc TEXT }

Then, I have a dialog with a textfield, user input the City name there, and a CString variable m_szName link to it.

And, I have a piece code to insert the city into table:

stringstream sql;
sql << "INSERT OR REPLACE INTO Cities "
    << " (Id,Desc) VALUES ('1001','" << m_szName.GetBuffer() << "')";

Now the problem is, m_szName.GetBuffer() returns TCHAR*, so above program has syntax error. If I use "wstringstream sql", above code is good, but then it's not accepted by sqlite3_exec since it only accepts (char*).

I tried to convert TCHAR* to char* here and there, but nothing works. Please help, thanks.

Answer 1

sqlite3_exec() is one of the few functions that does not have a UTF-16 version, so you have to correctly convert the string contents into UTF-8:

CStringA str_utf8 = CW2A(m_szName.GetBuffer(), CP_UTF8);

However, your code will blow up when the name contains a quote. It would be a much better idea to use parameters, where it is also possible to use UTF-16 strings directly:

const char *sql = "INSERT ... VALUES ('1001', ?)";
sqlite3_stmt *stmt;
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
if (rc != SQLITE_OK) {
    cerr << sqlite3_errmsg(db);
    return;
}
sqlite3_bind_text16(stmt, 1, m_szName.GetBuffer(), -1, SQLITE_STATIC);
rc = sqlite3_step(stmt);
if (rc != SQLITE_DONE)
    cerr << sqlite3_errmsg(db);
sqlite3_finalize(stmt);