How can blob response from sqlite3 be saved into a variable in Bash?

Question

linux

In bash script for query in sqlite3

SELECT values ​​FROM tables WHERE keyname='namefirst';

I receive the answer in the form of binary data.
Since this is a BLOB field and it contains 00h characters, only the first "{" character is returned.
In HEX, respectively, this entry in the database looks like:

7B 00 22 00 6E 00 61 00 6D 00 65 00 22 00 3A 00 22 00 6A 00 6F 00 68 00 6E 00 22 00 7D

, which corresponds

{�"�n�a�m�e�"�:�"�j�o�h�n�"�}

I can't figure out how to make a request or process the string further to get a normal response

{"name":"john"}

.

Answer 1

You can't process the string because your base is most likely in utf-8 (check, type ".show"), and the value is stored in utf-16. It's hard to do this on a clean base.
As a shortcut, output (.mode quote) and then convert via xxd

sqlite3 -quote $fileIn "SELECT values FROM tables WHERE keyname='namefirst'" | xxd -r -p > $fileOut

Answer 2

I would think about properly setting encodings, collate or something. Because it can be trite double-byte unicode.