2

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).

Kamil
  • 1,456
  • 4
  • 32
  • 50
  • Try the following commands (which create the string "x×x"): `create table t(x); insert into t values(cast(x'78c39778' as text));` How does this look in the Windows console and in the output file? – CL. Nov 28 '13 at 15:55
  • @CL. in windows console: INSERT INTO "t" VALUES('x├Śx'); and in the file: INSERT INTO "t" VALUES('x×x'); – Kamil Nov 28 '13 at 17:10

2 Answers2

3

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).

Community
  • 1
  • 1
CL.
  • 173,858
  • 17
  • 217
  • 259
  • 1
    In Windows console one should type `chcp 65001` to change encoding to UTF-8 and then open `sqlite3.exe` in current window. This encoding change isn't permanent and should be done every time when there's need to change something in a database. – Kamil Dec 17 '13 at 00:29
-1

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.

user2956477
  • 1,208
  • 9
  • 17