Storing large images as BLOB inside a SQLite DB using Bash

Question

I'm creating a little script to import a big directory of images inside a SQLite database, I'm perfectly aware that SQLite is not the perfect place to store big blobs but this is how i must do it.

Actually, I'm trying using hexdump:

sqlite3 name.db "INSERT INTO table (image) values(x'"$(hexdump -v -e '1/1 "%02x"' ./filename.jpg)"');"

But occasionally, on rather big images, it returns an error:

sqlite3: Argument list too long

How would you work around this problem?

Answer 1

When the SQL command is given as a parameter to sqlite3, the entire output of hexdump must be generated before the parameter can be actually used.

A pipe does not have size limits:

(echo -n "INSERT INTO table (image) values(x'"
 hexdump -v -e '1/1 "%02x"' ./filename.jpg
 echo "');") | sqlite3 name.db

Alternatively, you could write the command(s) into a file, and tell sqlite3 to .read it.

The SQLite shell in version 3.8.6 or later has the readfile() function for reading a file directly.