CODEWITHSUNDEEP
imagesqliteblob
uerden
uerden

Reputation: 185

How can we insert an image in sqlite database(table)?

I guess that it's valid for MySQL, however, I cannot find anything about SQLite.

Basically, I have a table which is named 'CUSTOMER'. So I create an attribute like this:

.. Image BLOB .. after that my insert statement looks like this:

INSERT INTO CUSTOMER(1,LOAD_FILE(D:/Project/Images/X.jpg));

However, the LOAD_FILE tag is not working and I don't know how to insert an image or if we can do that.

Upvotes: 2

Views: 5407

Answers (3)

MikeT
MikeT

Reputation: 57083

You can store an image as a BLOB, but you'd have to insert it as a a series of bytes using something like :-

    INSERT INTO CUSTOMER (image_column, other_column) 
                VALUES(x'0001020304........','data for the first other column');

So you'd need to convert the file into a hex string to save it.

However, it's not really recommended to store images but to rather store the path to the image and then retrieve the file when you want to display/use the image.

Saying that, SQLite can, for smaller images (say 100K), actually be more efficient 35% Faster Than The Filesystem.

Upvotes: 2

Hyller Vianna
Hyller Vianna

Reputation: 1

You must use the cmd command line (windows) to insert the attachment. The sqllitespy (version 1.9.13) does not support de command from the program command line.

You should acess you database first with the CMD and after that;

update (your table) set (column) = readfile ('dir where the files are stored'||num||´.jpg);

Upvotes: 0

Shawn
Shawn

Reputation: 52614

If you're using the sqlite3 shell, the relevant function is readfile().

If you're doing this from your own program, you have to read the file into a byte array and bind it as a blob to the desired column in an insert. The exact details vary depending on language and sqlite bindings, but you shouldn't ever have to convert it to a blob literal string and embed that directly into a statement.

Upvotes: 4

Related Questions