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

[Lars Ingebrigtsen <larsi@gnus.org>]

Eli Zaretskii <eliz@gnu.org> writes:

>> I’m trying make use of sqlite functionalities added recently and found
>> that the query result of blob type entries are multibyte string rather
>> than unibyte. That caused issues when I tried to create image out of the
>> query result: the image appears to be broken, even though the data is
>> good. Adding string-as-unibyte solves the issue, but I guess the
>> return value of a blob entry is probably best to be unibyte by default?
>
> If we want to support non-text data in a blob, then yes, I think you
> are right.

I'm not sure there's any way to do that reliably.  If we change blob
selections to not decode, then if you have

create table if not exists test7 (col1 text, col2 blob)

and then

(format "insert into test7 values ('foó', '%s')" binary)

will have to encode the entire string in utf-8, meaning you get garbage
back when you select.  (While today you get encoded binary back.)

And if just say "well, don't do that, then; use prepared statements",
then

"insert into test7 values (?, ?)" ["foo" binary]

could work...  if we knew the type of the columns here, but I can't find
any way to determine that on a statement that has been executed yet
(sqlite3_column_type/sqlite3_column_decltype only seems to work on
selects; perhaps I'm missing something -- anybody know?)

We could then say "well, don't encode unibyte strings", but then we
might be inserting that, unencoded, into a text column, and we'd be
decoding when we get back, leading to more garbage.

Finally, we could introduce a new syntax, like:

"insert into test7 values (?, ?)" ["foo" (:binary binary)]

or something, but...  yuck.

-- 
(domestic pets only, the antidote for overdose, milk.)
   bloggy blog: http://lars.ingebrigtsen.no