Sqlite - Insert data into blob

Question

I am trying to insert binary data into blob using SQLite3 shell, which means regular SQL statements. Here is my table:

CREATE TABLE MYTABLE (ID INTEGER, BINDATA BLOB NOT NULL, SOMEFK INTEGER REFERENCES OTHERTABLE(ID) NOT NULL, PRIMARY KEY(ID) ); 

And here is what the insert statement I am trying is:

 INSERT INTO MYTABLE (BINDATA, SOMEFK) VALUES (__READBINDATA('/tmp/somefile'), 1); 

With __READBINDATA(file) is the function I'm looking for. Is it possible?

Answer 1

There is no built-in or shell function for reading a file in blob.

However, using the hexdump tool, hexdump can convert the contents of the file to blob literal :

 echo "insert into mytable(bindata, somefk) " \ "values(x'"$(hexdump -v -e '1/1 "%02x"' /tmp/somefile)"', 1);" 

This command can then be passed to the sqlite3 shell.