Subject: Re: bug#54591: 29.0.50; sqlite-select returns blob result as multibyte string

From: Johannes Grødem <[email-masked]>
Date: Fri, 01 Apr 2022 20:34:49 +0200

Lars Ingebrigtsen <[email-masked]> writes:
>> (Of course, if there's a way of knowing the type, we could DTRT
>> automatically for each type.)
>
> Yes, I'm hoping somebody else could have a look at the sqlite
> documentation and tell me I'm missing some obvious way to get that
> information.  :-)

I might be misunderstanding the issue, but SQLite column types are more
like documentation than actual rules to be enforced, unless STRICT
tables are enabled.

https://www.sqlite.org/datatype3.html

In Python's SQLite3 API, ordinary strings are inserted as SQLite TEXTs
and bytestrings are inserted as SQLite BLOBs, and returned as the same
when you query. It's using a function similar to row_to_value for this,
except that it for some reason doesn't switch on SQLITE_BLOB, it just
calls sqlite3_column_blob and tests if it returns NULL or not and then
it does a switch on the remaining types.

Couldn't the Emacs interface just use vectors of byte values for BLOBs
both ways? Or is there another data type that makes more sense?

By the way, if you want to insert BLOBs in the query itself you can do
it like this, but I guess this doesn't need Emacs support, except maybe
a helper function for the conversion:

  INSERT INTO foo VALUES (X'deadcafe');