Are encodings guaranteed to be preserved in SQLite?

(1) By Gary (1codedebugger) on 2022-11-05 02:46:11 [source]

Hello, I'm using Tcl with SQLite and would like to know, if the data to be written to SQLite is encoded in Tcl using [encoding convertto utf-8...] or another encoding before being written, is it always guaranteed that when the data is later extracted from SQLite it will be of the same encoding, or could it be altered due to the way SQLite may hold it internally? I'm expecting to distribute this project as a SQLite database and a Tcl starkit which may be run on different platforms.

I recently had an issue due to my failing to adequately consider the encoding of some text before it was written to SQLite and had to encode it after extracting it. It is an easy fix and causes no issues but it annoys me that I did it wrong and I was going to re-write the data to SQLite properly encoded but wondered if that would guarantee that it wouldn't need to be encoded again after extraction.

Thank you.

(2) By Richard Hipp (drh) on 2022-11-05 11:17:56 in reply to 1 [link] [source]

SQLite only understands UTF8, UTF16LE and UTF16BE. SQLite might try to convert between those three, if you ask it too. But if you never request an encoding change, none will be performed - SQLite returns exactly what you put in.

"Requesting an encoding change" in the previous sentence means doing something like inserting a UTF16 string into a UTF8 database, or extracting a UTF16 string from a UTF8 database. You are probably doing everything in UTF8, which means you don't need to worry about any of that.

(3) By Gary (1codedebugger) on 2022-11-05 18:25:21 in reply to 2 [link] [source]

Thank you.