imagesqliteblob

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.


Solution

  • 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.