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!

tzot · Accepted Answer · 2021-06-18 14:51:21Z

26

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.

edited Jun 18, 2021 at 14:51

answered Mar 12, 2016 at 17:21

tzot

96.6k30 gold badges151 silver badges210 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bat_ventzi Over a year ago

This works on versions greater or equal to SQLite Release 3.8.6 On 2014-08-15

Arcadien · Accepted Answer · 2012-07-02 12:25:56Z

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

edited Jul 2, 2012 at 12:25

answered Jul 2, 2012 at 11:56

Arcadien

2,27816 silver badges26 bronze badges

2 Comments

MobileCushion Over a year ago

Thanks for your answer. Unfortunately, this is only working for non-binary files. I am able to insert text but not jpg images for example, nor other type of binary data. Do you have any idea why?

Arcadien Over a year ago

Maybe you need to encode binary file prior to insert them, with something like uuencode? the "cat" command maybe produce unusable output for a db insert

Collectives™ on Stack Overflow

SQLite: insert binary data from command line

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related