How to print sqlite to file in utf-8?

Question

I've opened sqlite3.exe in windows console and made a database with special characters. .dump showed me the sql query with special characters. Then I changed output to file: .output file.sql And executed the .dump command.
The special characters were missing when I imported the database using .read file.sql.
I used pragma encoding="UTF-8"; but it didn't change anything (I don't know if it should).

Answer 1

The Windows console makes it hard to use UTF-8 correctly, and the Microsoft compiler has lots of bugs that make it impossible to use UTF-8 with portable I/O functions.

If you have entered data in the Windows console, those strings are not valid UTF-8. If a non-ASCII string is output with correct characters in the Windows console, it is not valid UTF-8.

To ensure that your data is valid UTF-8, you have to go through files. Alternatively, use any SQLite shell that does not use the console (such as the SQLite Manager Firefox extension).

Answer 2

This work fine for CP852, but could be used for any codepage known by iconv.

chcp 852 >NUL
echo INSERT into NAMES (name,timestamp) VALUES ('ěščřžýáíé','1429001515'); | iconv.exe -f cp852 -t utf-8 | ..\utilities\sqlite3.exe test.db

Windows can handle unicode internaly, but if you print it on console (by 'echo' command for example) than character mismatch. Using on-the-fly reencoding solve this problem.