Geek Answers Handbook

SQLite: inserting binary data from the 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 the binary into the image field in this table. Is it possible to do this from sqlite3 command line sqlite3 ? If so, how? I am using Ubuntu.

Thanks!

+4
source share
2 answers

You can use syntax, for example:

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

I am not sure about the meaning of "around blob". Pay attention to back quotes around the cat command, means that the cat command will be executed before the echo.

[EDIT]

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

+3
source

The sqlite3 command line interface adds the following two functions defined by the application:

  • readfile
    which is commonly used as: INSERT INTO table(blob) VALUES (readfile('myimage.jpg'))
  • writefile
    which writes a file with the contents of the database BLOB and returns the recorded bytes.
+9
source

More articles:


All Articles