Search code examples
sqliteblobbinaryfiles

SQLite: insert binary data from command line

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!

Solution

  • 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