Text shown as Blob binary data
(1.1) By RomiG (RomioGeek) on 2020-09-01 11:10:10 edited from 1.0 [source]
I am facing one issue where I am binding a string "Comp® FP PAC D50" to a table column using "sqlite3_bind_text". But after query is executed, i can see DB is having blob data instead of the given string. Also, when i try to print it using sqlite command line tool, i see "▒" instead of trademark symbol "®".
Expected text column value: Comp® FP PAC D50
Sqlite Command line on putty: Comp▒ FP PAC D50
Actual Text column value (Binary data) in sqlite browser: 43 6f 6d 70 ae 46 50 20 50 41 43 20 44 35 30
(2) By Richard Hipp (drh) on 2020-09-01 11:30:12 in reply to 1.1 [link] [source]
SQLite wants its text to be UTF (usually UTF-8, but it can also use UTF-16). The 0xae byte is not a valid UTF-8 character. Probably you meant to use U+00ae which is encoded as two bytes "0xc2 0xae" in UTF-8.
SQLite will happily store whatever string of bytes you hand it in a TEXT column. And you will get back those exact same bytes using the sqlite3_column_text() interface (assuming you didn't ask SQLite to do translations between UTF8 and UTF16). But when the "sqlite3.exe" command-line tool goes to print those bytes out, it assumes that the bytes represent UTF. Similarly, built-in SQL functions such as LENGTH() and SUBSTR() assume UTF.
(3) By RomiG (RomioGeek) on 2020-09-08 11:55:33 in reply to 2 [link] [source]
Thanks Richard for details. It helped to solve the issue.