Inserting the image as BLOB
If I have a table:
CREATE TABLE images( id INTEGER PRIMARY KEY, image BLOB);
and I have an image:
what would be the proper syntax to insert that image into that table?
While there may be a way to do this via the
sqlite3 shell, with some sort of plain-text encoding of the text, the right way to do it is to write a program that calls
sqlite3_bind_blob() or similar.
What programming language are you using?
I have a very similar question, only I need the coding to be done with PHP..
I found (3) techniques that I have been advised with.
First, is to SAVE the image to an "images" folder and only store the filename and file path in your database.
Second is to ENCODE the image using "base64_encode()" and save the DATA as a text field, then DECODE that DATA and using base64_decode() and save it as an image file.
The third option is the one I think you are most interested in and I am still trying to sort out how to save an image file to an SQLITE database BLOB field using PHP.
Check this article: https://makitweb.com/upload-and-store-an-image-in-the-database-with-php/
If you have an answer, feel free to share..
I hope my response was helpful..
Here is an old Windows shell session showing such an operation:
> sqlite3 myPics.sdb
SQLite version 3.35.0 2021-03-12 15:10:09
Enter ".help" for usage hints.
sqlite> create table images(id integer primary key, image blob);
sqlite> insert into images (image) values (readfile('VCAinvite.PNG'));
sqlite> select id from images;
sqlite> select (writefile('VCAinvite_copy.png', image)) from images where id=1;
> fc VCAinvite.PNG VCAinvite_copy.png
Comparing files VCAinvite.PNG and VCAINVITE_COPY.PNG
FC: no differences encountered
And, on memory (or address space) limited systems, another right way is to use the blob streaming APIs, starting with sqlite3_blob_open(...) and proceeding with calls to APIs mentioned in the "See also" section there.
SQLite can handle BLOBs that are much larger than many systems have enough memory with which to do the bind_blob() thing. The streaming APIs facilitate BLOB handling when large BLOBs are to be stuffed into or retrieved from a DB.
My sqlite-utils command-line tool has a command for doing this:
sqlite-utils insert-files gifs.db images horse.png
I wrote more about that here: https://simonwillison.net/2020/Jul/30/fun-binary-data-and-sqlite/
The same way you write an integer into an SQLite3 table.
a = 57; db = sqlite3_open('database') statement = db.prepare('insert into sometable (somecolumn) values (?)') statement.bind_integer(1, a) statement.execute()
except you want to load up the variable with the contents of the file.
a = file('image.jpg').read() db = sqlite3_open('database') statement = db.prepare('insert into sometable (somecolumn) values (?)') statement.bind_blob(1, a) statement.execute()
and to write it you do:
db = sqlite3_open('database') statement = db.prepare('select somecolumn from sometable;') statement.execute() a = statement.column_blob(1) open('image.jpg').write(a) statement.close()
You will have to translate into PHP yourself.