Add file to blob fileld in sqlite db

Question

How can I add image file, for example, with name '001.png' to sqlite3 database using its command prompt?

sqlite3> INSERT INTO test (id, name, blob_field) VALUES (NULL, 'Bob', ?????);

CL. · Accepted Answer · 2013-05-05 20:27:10Z

1

Depending on the OS you're using, you could convert the file into a hexdump and use that to construct an SQL command with a blob literal:

(printf "INSERT INTO test(id, name, blob_field) VALUES(NULL, 'Bob', 0x" ; \
 hexdump -v -e '/1 "%02x"' 001.png ; printf ");" ) | sqlite3 my.db

answered May 5, 2013 at 20:27

CL.

182k18 gold badges241 silver badges282 bronze badges

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

Comments

Axarydax · Accepted Answer · 2013-05-05 13:08:01Z

0

Use hex literal like 0x123456789abcdef

so

INSERT INTO test (id, name, blob_field) VALUES (NULL, 'Bob', 0xB0B1);

answered May 5, 2013 at 13:08

Axarydax

16.7k22 gold badges95 silver badges157 bronze badges

How can I get hex literal of 001.png file?

Read the file into a byte array in your language, and then format each byte as a hex character. Are you doing this in C# or Java?

I have found examples how to do it in C and many other langueges, but I want to write *.sql file to fill database.

yes, but you can generate the SQL file with python or other programming, for example