2

I'm working in C++ and I'm dealing with an SQLite database. Consider this:

wchar_t s[] = L"This is my wide string. ĂÎȘȚÂăîșțâ";

What should I do to launch a query containing this string? I saw that if I declare my query, for example:

char q[] = "SELECT * FROM 'mydb' WHERE 'something' LIKE '%ĂÎȘȚÂ%'";

The compiler is smart enough to somehow convert those wide chars and the query actually works as expected. But still, how should I declare/create the query array of chars so as to include those pesky characters? I tried with string and its c_str() method, wstring, etc.

Remy Lebeau
  • 555,201
  • 31
  • 458
  • 770
Petru Dimitriu
  • 365
  • 1
  • 5
  • 14

1 Answers1

2

You aren't composing your SQL with string concatenation, aren't you? Use placeholders and bind the string to the right placeholder:

char q[] = "SELECT * FROM 'mydb' WHERE 'something' LIKE ?";
// ... create sqlite statement
wstring p = L"ĂÎȘȚÂăîșțâ";
sqlite3_bind_text16(stmt, 1, (L"%"+p+L"%").c_str(), -1, SQLITE_TRANSIENT);

However, this code will work only on systems that use UTF-16 for wide strings, which is basically only Windows. It's recommended to not use wchar_t because it's not portable. Use narrow char UTF-8 encoded strings instead. SQLite assumes all narrow char strings are UTF-8, even for file names, even on Windows. Using this approach, you can get the original code to work just by saving the file as UTF-8 without BOM. This will make the string

char q[] = "SELECT * FROM 'mydb' WHERE 'something' LIKE '%ĂÎȘȚÂ%'";

UTF-8 encoded on major compilers (including MSVC and GCC).

Remy Lebeau
  • 555,201
  • 31
  • 458
  • 770
Yakov Galka
  • 70,775
  • 16
  • 139
  • 220
  • Wonderful answer. But how do I use narrow char UTF-8 encoded strings? (Forgive me, this might be a silly request, but I'm not so experienced.) – Petru Dimitriu Jul 03 '12 at 18:15
  • 1
    @PetruDimitriu: Well, this depends on how you want to "use" it. If you read and write it to/from files or SQLite databases, or if you do most other trivial tasks like searching ASCII substrings, then you just forget that it's UTF-8 and treat it as cookies—essentially no code will be changed. It becomes more tricky if you want to write the text to the screen, since there's no portable way to do this with Unicode support. See the linked article how to work with them. – Yakov Galka Jul 03 '12 at 18:21
  • So first I prepare the statement with that placeholder "?", then I bind my text to it, right? – Petru Dimitriu Jul 03 '12 at 22:44
  • @PetruDimitriu: yes, see [the documentation](http://www.sqlite.org/c3ref/bind_blob.html) for details. – Yakov Galka Jul 04 '12 at 08:20
  • Can you explain, what is `stmt` parameter, I googled a bit and found out it needs to be created using `sqlite3_prepare16` but cannot manege to understand how to use it. – ST3 Aug 23 '13 at 08:58
  • @user2623967: `sqlite3_prepare_v2(db, q, -1, &stmt, 0)` where `db` is your database connection and `q` is the SQL query string from above. – Yakov Galka Aug 24 '13 at 07:21
  • And, what about `&stmt`?? – ST3 Aug 24 '13 at 07:23
  • @user2623967: see [documentation](http://www.sqlite.org/c3ref/prepare.html). If you can't deduce it from there, read about pointers. – Yakov Galka Aug 24 '13 at 16:00
  • `("%"+p+"%")` will not work, as you cannot concatenate `char[]` strings with `wstring`. Use `(L"%"+p+L"%")` instead. – Remy Lebeau Mar 18 '22 at 23:09
  • @RemyLebeau yes, looks like a typo – Yakov Galka Mar 18 '22 at 23:14