Inserting the image as BLOB
(1) By oneeyeman on 2021-03-12 22:50:18 [link]
Hi, ALL, If I have a table: CREATE TABLE images( id INTEGER PRIMARY KEY, image BLOB); and I have an image: ~/Images/horse.png what would be the proper syntax to insert that image into that table? Thank you.
(2) By Warren Young (wyoung) on 2021-03-12 22:54:57 in reply to 1 [link]
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()`](https://sqlite.org/c3ref/bind_blob.html) or similar.
(3) By DMVB123 on 2021-03-12 23:00:05 in reply to 1 [link]
Hello, 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..
(4) By Larry Brasfield (larrybr) on 2021-03-12 23:03:59 in reply to 1 [link]
Here is an old Windows shell session showing such an operation:<code> \> 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; 1 sqlite> select (writefile('VCAinvite_copy.png', image)) from images where id=1; 193428 sqlite> .q \> fc VCAinvite.PNG VCAinvite_copy.png Comparing files VCAinvite.PNG and VCAINVITE_COPY.PNG FC: no differences encountered \> </code>
(5) By Larry Brasfield (larrybr) on 2021-03-12 23:11:13 in reply to 2 [link]
And, on memory (or address space) limited systems, another *right* way is to use the blob streaming APIs, starting with <u>[sqlite3_blob_open(...)](https://sqlite.org/c3ref/blob_open.html)</u> 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.
(6) By Simon Willison (simonw) on 2021-03-12 23:52:32 in reply to 1 [link]
My sqlite-utils command-line tool has a command for doing this: [https://sqlite-utils.datasette.io/en/stable/cli.html#inserting-binary-data-from-files](https://sqlite-utils.datasette.io/en/stable/cli.html#inserting-binary-data-from-files) 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/](https://simonwillison.net/2020/Jul/30/fun-binary-data-and-sqlite/)
(7) By Keith Medcalf (kmedcalf) on 2021-03-13 00:01:13 in reply to 1
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.