sqlite - Insert data into blob

Question

I'm trying to insert binary data into a blob using SQLite3's shell, which means regular SQL statements. Here's my table:

CREATE TABLE MYTABLE
    (ID INTEGER,
     BINDATA BLOB NOT NULL,
     SOMEFK INTEGER REFERENCES OTHERTABLE(ID) NOT NULL,
     PRIMARY KEY(ID)
);

And this is the kind of insert statement I'm trying:

INSERT INTO MYTABLE (BINDATA, SOMEFK)
VALUES (__READBINDATA('/tmp/somefile'), 1);

With __READBINDATA(file) being the function I am looking for. Is that possible?

CL. · Accepted Answer · 2012-10-12 21:10:59Z

8

There is no built-in or shell function to read a file into a blob.

However, with the help of the hexdump tool, it's possible to transform a file's contents into a blob literal:

echo "insert into mytable(bindata, somefk) " \
     "values(x'"$(hexdump -v -e '1/1 "%02x"' /tmp/somefile)"', 1);"

This command can then be piped into the sqlite3 shell.

answered Oct 12, 2012 at 21:05

CL.

182k18 gold badges241 silver badges282 bronze badges

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

1 Answer 1