C++ – Writing unicode strings in c++ to sqlite DB

csqlite

I'm currently building a manager game using sqlite and c++. I've found tutorials on covering almost every area needed to create my game but a minor issue has been bugging me for ages.

when I write a string in c++ for example "Andreas Klöden" and uses it with a sqlite statement to insert the data in my sqlite DB the special characters with umlaut ö are lost, this goes for many european names like Léfevre, Würth etc.

I have searched for an answer at it seems that c++ strings are in ANSII or something like that. Somehow I should write unicode strings instead to avoid the issue shown below:

http://imageshack.dk//viewimage.php?file=/imagesfree/0Ej53884.png

does anyone know how to write unicode strings in c++ so I can export/import the correct data from the sqlite DB?

Best Answer

SQLite "supports" UTF-8 (the quotes are here because basically UTF8 uses only ASCII characters so there is nothing to "support" actually). So one of the best (and also backwards-compatible) ways is to use the UTF-8 encoding. wchar_t support in SQLite is also present, but there is only one reason to use it: if you code already depends on the wchar_t.

Read this if "UTF-8" is not a familiar topic: http://www.joelonsoftware.com/articles/Unicode.html

The call to C API

sqlite_exec(db_conn, "insert somevalue into sometable values(\"utf encoded string\""), NULL, NULL, "error");

would do the job.

The actual conversion can be done manually if you have to support only limited amount of codepages or use the ICU library for full support. There are also libs like http://utfcpp.sourceforge.net/

CodeProject also has a lot of UTF-8 sample codes:

http://www.codeproject.com/Articles/14637/UTF-8-With-C-in-a-Portable-Way

http://www.codeproject.com/Articles/5281/UTF-8-Encoding-and-Decoding

etc.

Related Solutions

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

There are a few steps to see the tables in an SQLite database:

List the tables in your database:
```
.tables
```
List how the table looks:
```
.schema tablename
```
Print the entire table:
```
SELECT * FROM tablename;
```
List all of the available SQLite prompt commands:
```
.help
```

SQLite – UPSERT not INSERT or REPLACE

Assuming three columns in the table: ID, NAME, ROLE

BAD: This will insert or replace all columns with new values for ID=1:

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (1, 'John Foo', 'CEO');

BAD: This will insert or replace 2 of the columns... the NAME column will be set to NULL or the default value:

INSERT OR REPLACE INTO Employee (id, role) 
  VALUES (1, 'code monkey');

GOOD: Use SQLite On conflict clause UPSERT support in SQLite! UPSERT syntax was added to SQLite with version 3.24.0!

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL.

GOOD but tedious: This will update 2 of the columns. When ID=1 exists, the NAME will be unaffected. When ID=1 does not exist, the name will be the default (NULL).

INSERT OR REPLACE INTO Employee (id, role, name) 
  VALUES (  1, 
            'code monkey',
            (SELECT name FROM Employee WHERE id = 1)
          );

This will update 2 of the columns. When ID=1 exists, the ROLE will be unaffected. When ID=1 does not exist, the role will be set to 'Benchwarmer' instead of the default value.

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );

Best Answer

Related Solutions

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

SQLite – UPSERT *not* INSERT or REPLACE

Related Topic

SQLite – UPSERT not INSERT or REPLACE