SQLite3: non-ascii unicode chars' update not working?

Question

I'm using SQLite3 under win32, I'm always getting the non-ascii characters transformated into dangling ones when updating a WVARCHAR column using SQLite3 on my C program, here is an example of my code that unfortunately doesn't update correctly the non-ascii chars like 'é':

    #include <stdio.h>
    #include <sqlite3.h>

    int main(){
      sqlite3_stmt *stmt;
      sqlite3 *sqdb;

      sqlite3_initialize();
      sqlite3_open("sqlite_unicode_test", &sqdb);
       const char* table_check = {
      "CREATE TABLE IF NOT EXISTS mytable("
       "id INTEGER PRIMARY KEY AUTOINCREMENT,"
       "mycolumn1 WVARCHAR,"
       "mycolumn2 WVARCHAR"
       ");"
       };
      sqlite3_prepare_v2(sqdb, table_check, -1, &stmt, 0);
      sqlite3_step(stmt);
      sqlite3_finalize(stmt);
      sqlite3_prepare16(sqdb, L"UPDATE mytable SET mycolumn1=? mycolumn2=? WHERE(id=0)", -1, &stmt, 0);
      sqlite3_bind_text16(stmt, 1, L"e", -1, SQLITE_STATIC); //e is stored correctly on the database
      sqlite3_bind_text16(stmt, 2, L"é", -1, SQLITE_STATIC); //however é not stored correctly: modified by a dangled character
      sqlite3_step(stmt);
      sqlite3_finalize(stmt);
      sqlite3_close(sqdb);
    }

I don't know why this is happening, I posted before a question about that (SQLite3: non-ascii characters not updated correctly?) but I don't think this is related to sqlite3 win32 binaries because I tested building SQLite by myself using gcc and there is always the same problem. so what's wrong with the SQLite unicode? and how can I exactly solve the problem? I need help.

@ojblass what do you mean by 'can you show your connection statement'? do you mean a screenshot from the shell? — user3457200, May 23 '14 at 14:53
obviously before you prepare a statment you have to open a db connection with some options... — ojblass, May 23 '14 at 14:56

score 1 · Accepted Answer · edited May 23 '17 at 12:20

1

I think there are two things you must do :

When you create the database you must specify utf-16 (I think UTF-8 is the default).

In addition when before you bind the character you must convert the accented character to UTF-16 and not pass a UNICODE string.

See this question and this question for more information.

edited May 23 '17 at 12:20

Community

1
1

answered May 23 '14 at 15:09

ojblass

21,146
22
83
132

thanks a lot, that really helped me to solve the problem! thanks again. – user3457200 May 23 '14 at 15:56

SQLite3: non-ascii unicode chars' update not working?

1 Answers1