SQLite: insert binary data from command line

Question

I have this SQLite table:

create table mytable (
aid INTEGER NOT NULL PRIMARY KEY, 
bid INTEGER NOT NULL, 
image BLOB
);

And I want to insert a binary file into the image field in this table. Is it possible to do it from the sqlite3 command line interface? If so, how? I'm using Ubuntu.

Thank you!

Answer 1

The sqlite3 command line interface adds the following two “application-defined” functions:

• readfile
  which typically is used as: INSERT INTO table(blob) VALUES (readfile('myimage.jpg'))
• writefile
  which writes a file with the contents of a database blob and returns the count of bytes written.

Answer 2

You may use a syntax like :

echo "insert into mytable values(1,1, \"`cat image`\")" | sqlite3 yourDb

i'm not sure for the " around blob's value. Note the backquotes around cat command, means the cat command will be executed before the echo.

[EDIT]

Blob are stored as hexa digit with "X" prefix. You can use "hexdump" unix command to produce the hexa string, but it would be better to write a command line tool that read image and do the insert. More details on this post : http://comments.gmane.org/gmane.comp.db.sqlite.general/64149