How to Store Image in Sqlite Database

How to store image in SQLite database

You have to use "blob" to store image.

ex: to store a image in to db:

public void insertImg(int id , Bitmap img ) {   


    byte[] data = getBitmapAsByteArray(img); // this is a function

    insertStatement_logo.bindLong(1, id);       
    insertStatement_logo.bindBlob(2, data);

    insertStatement_logo.executeInsert();
    insertStatement_logo.clearBindings() ;

}

 public static byte[] getBitmapAsByteArray(Bitmap bitmap) {
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    bitmap.compress(CompressFormat.PNG, 0, outputStream);       
    return outputStream.toByteArray();
}

To retrieve a image from db:

public Bitmap getImage(int i){

    String qu = "select img  from table where feedid=" + i ;
    Cursor cur = db.rawQuery(qu, null);

    if (cur.moveToFirst()){
        byte[] imgByte = cur.getBlob(0);
        cur.close();
        return BitmapFactory.decodeByteArray(imgByte, 0, imgByte.length);
    }
    if (cur != null && !cur.isClosed()) {
        cur.close();
    }       

    return null;
}

Storing images in sqlite - Django

Django's ImageField and FileField are both just links to the location where the file is actually uploaded.
If you read Django's documentation on how to use imageField you may observe a upload_to attribute.

Both Fields are implemented in Django's ORM and are DB agnostic (i.e. should work on SQLite, MySQL or any other DB supported by Django).

You can check this out for examples on how to mange files.

The first example in the link shows a Car model and uploads the image to cars under the MEDIA_ROOT folder.

from django.db import models

class Car(models.Model):
    name = models.CharField(max_length=255)
    price = models.DecimalField(max_digits=5, decimal_places=2)
    photo = models.ImageField(upload_to='cars')

Alternative

If you really need the image to live in your database you can always utilize django's BinaryField and save the whole image as BLOB.

from django.db import models

class Car(models.Model):
    name = models.CharField(max_length=255)
    price = models.DecimalField(max_digits=5, decimal_places=2)
    photo_as_blob = models.BinaryField()  # save photo as binary object

As you can see from other answers it is not generally considered a good practice to save big files directly in DB.

how to store a jpg in an sqlite database with python

Finally I got it working thanks to Andrej Kesely's comment. The working solution is

imgobj = base64.b64encode(open('./fotocopies/checks/624.jpg').read())
con = sqlite3.connect("pybook.db")
cur = con.cursor()
qf="UPDATE data_fotocopies SET fotocopy='%s' WHERE refid=%d AND reftype=0"%(lite.Binary(fotocopy_blob),id)
cur.execute(qf) #yes, it is dangerous for injection`

and retrieving the image from the database is done as:

qf="SELECT fotocopy FROM data_fotocopies WHERE refid=%d and reftype=0"%self.check_id
self.cur.execute(qf)
try:
    fd=base64.b64decode(self.cur.fetchall()[0][0])
    byting = GLib.Bytes(fd)
    self.fotocopy = Gio.MemoryInputStream.new_from_bytes(byting)
    ...
self.fotocopy_ent=self.builder.get_object("fotocopy")         # as it is made in glade
pixbuf = GdkPixbuf.Pixbuf.new_from_stream(self.fotocopy,None) #finally the pixbuf although 
                                                              #it produces errors if I have
                                                              #no stream/image to "feed" it.
self.fotocopy_ent.set_from_pixbuf(pixbuf)

Still can't figure out why all other solutions I've found don't work. I use Python 2.7.6 ang gtk3, but this one I subit does.

Thank you all for your help.

Saving png image in database through android app

The recommended way is to store the image as a file not in the database and then store the path or enough of the path to uniquely identify the image in the database.

To store an image you have to store it as a BLOB based upon a byte[] (byte array);
However, using the Android SDK, there is a limitation that only images less than 2MB can be retrieved even though they can be saved.

Technically you could store the image as a hex, binary, octal string but that would be more complex, less efficient and even more restrictive.. So really you have to store it as a BLOB is not completely factual.

It's also actually pretty useless storing images (or any large files) with SQLite or most structured databases as there is little that you can do with the data query wise.

You actually save a BLOB via SQL using something like :-

INSERT INTO your_table VALUES(x'00FF01EF');

i.e. 00 (0) is the first byte, FF (255) the 2nd, 01 (01) the 3rd .........
note the above would only work for a table with a single column

However, the SQLiteDatabase insert convenience method does the conversion from the byte[] to the correct SQL (hex string) on your behalf.

So you'd typically use :-

ContentValues cv = new ContentValues();
cv.put("your_column",your_image_as_a_byte_array);
your_sqlitedatabase_object.insert("your_table",null,cv);

You retrieve the byte array from a Cursor (result of a query) using something along the lines of

your_retrieved_image_byte_array = your_cursor.getBlob(your_column_offset_as_an_int); //<<<<<<<<< CursorWindow full error if the row cannot fit into the CursorWindow which has 2MB limit

You may wish to have a look at How can I insert image in a sqlite database, as this goes into more detail and has code that stores both images and paths (either one is stored based upon the image size, less then 100k, then the image is stored, else the path is stored) and additionally retrieves the images into a ListView. Additionally, although not recommended this is a way of getting around the 2Mb limit How to use images in Android SQLite that are larger than the limitations of a CursorWindow?

How to insert and retrieve image from Sqlite database in Android Studio

The messsage indicates that bytes is null. The documentation for getBlob states :-

The result and whether this method throws an exception when the column
value is null or the column type is not a blob type is
implementation-defined.

As such I believe that getBlob is returning a null and therefore that the likliehood is that nulls are getting inserted.

Consider the following based upon your DatabaseHelper :-

    mDB = new DatabaseHelper(this);
    mDB.addData("Test001", "Test001", "email", "password", "xxc", null);
    mDB.addData("Test002", "Test002", "email", "password", "xxc", new byte[]{0});
    Cursor csr = mDB.getData();
    DatabaseUtils.dumpCursor(csr);

    while (csr.moveToNext()) {
        bytes = csr.getBlob(6);
        if (bytes == null) {
            Log.d("OUCH", "Row " + String.valueOf(csr.getPosition()) + " is null");
        } else {
            Log.d("OK", "Row " + String.valueOf(csr.getPosition()) + " has byte array of length " + bytes.length);
        }
        Log.d("REPLICATE"," byte array length is " + bytes.length);
    }

This adds two rows the first with null as the byte[] (image), the second has a valid albeit it short byte[].

The rows are inserted without issue.

The Data is extract without issue.

However the log will contain the following :-

2019-01-09 14:15:31.622 2783-2783/ptfc.populatetablefromcursor D/mydb: adding : Test001 TO mytable
2019-01-09 14:15:31.623 2783-2783/ptfc.populatetablefromcursor D/mydb: adding : Test002 TO mytable
2019-01-09 14:15:31.624 2783-2783/ptfc.populatetablefromcursor I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@453edcd
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: 0 {
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out:    ID=1
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out:    fn=Test001
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out:    ln=Test001
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out:    e_m=email
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out:    pass=password
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out:    mobnum=xxc
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out:    image=null
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: }
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: 1 {
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out:    ID=2
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out:    fn=Test002
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out:    ln=Test002
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out:    e_m=email
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out:    pass=password
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out:    mobnum=xxc
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out:    image=<unprintable>
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: }
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out: 2 {
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out:    ID=3
2019-01-09 14:15:31.626 2783-2783/ptfc.populatetablefromcursor I/System.out:    fn=Test001
2019-01-09 14:15:31.627 2783-2783/ptfc.populatetablefromcursor I/System.out:    ln=Test001
2019-01-09 14:15:31.627 2783-2783/ptfc.populatetablefromcursor I/System.out:    e_m=email
2019-01-09 14:15:31.627 2783-2783/ptfc.populatetablefromcursor I/System.out:    pass=password
2019-01-09 14:15:31.627 2783-2783/ptfc.populatetablefromcursor I/System.out:    mobnum=xxc
2019-01-09 14:15:31.627 2783-2783/ptfc.populatetablefromcursor I/System.out:    image=null
2019-01-09 14:15:31.627 2783-2783/ptfc.populatetablefromcursor I/System.out: }
2019-01-09 14:15:31.629 2783-2783/ptfc.populatetablefromcursor I/System.out: <<<<<
2019-01-09 14:15:31.629 2783-2783/ptfc.populatetablefromcursor D/OUCH: Row 0 is null
2019-01-09 14:15:31.630 2783-2783/ptfc.populatetablefromcursor D/AndroidRuntime: Shutting down VM
2019-01-09 14:15:31.632 2783-2783/ptfc.populatetablefromcursor E/AndroidRuntime: FATAL EXCEPTION: main
    Process: ptfc.populatetablefromcursor, PID: 2783
    java.lang.RuntimeException: Unable to start activity ComponentInfo{ptfc.populatetablefromcursor/ptfc.populatetablefromcursor.MainActivity}: java.lang.NullPointerException: Attempt to get length of null array
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2914)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3049)
        at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:78)
        at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:108)
        at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:68)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1809)
        at android.os.Handler.dispatchMessage(Handler.java:106)
        at android.os.Looper.loop(Looper.java:193)
        at android.app.ActivityThread.main(ActivityThread.java:6680)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)
     Caused by: java.lang.NullPointerException: Attempt to get length of null array
        at ptfc.populatetablefromcursor.MainActivity.onCreate(MainActivity.java:40)
        at android.app.Activity.performCreate(Activity.java:7136)
        at android.app.Activity.performCreate(Activity.java:7127)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1271)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2894)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3049) 
        at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:78) 
        at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:108) 
        at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:68) 
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1809) 
        at android.os.Handler.dispatchMessage(Handler.java:106) 
        at android.os.Looper.loop(Looper.java:193) 
        at android.app.ActivityThread.main(ActivityThread.java:6680) 
        at java.lang.reflect.Method.invoke(Native Method) 
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493) 
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)

The stackTrace is similar. showing that bytes.length results in the failure if a null is inserted.

There are many ways this could be fixed for example you could set the image column to have a DEFAULT value along with skipping the cv.put if the ProfileImage passed to the addData method is null e.g.

in the Database helper change + image + " BLOB ')"; to + image + " BLOB DEFAULT X'00')";

along with the following change in the addData method :-

    if (Profileimg != null) {
        cv.put(image, Profileimg);
    }

I can't recall how this will affect the image in the listview, although I think it handles it.

However, the root cause will be that the picture taking will be returning a null.

Additionally you will likely encounter other issues if the images
themselves are large as there are limitations (1M more recently 2M)
with the size of data that a CursorWindow (used by a Cursor) can
handle exceed or get close to 2M with 1 image and an exception is
guaranteed. With 1M images a CursorWindow will hold 1 at the most if
at all so you'd expect display issues.

If images average around 100k then they can be stored in the DB and a
search could reveal the reasoning behind how SQlite can be more
efficient than a file system.

How to store(bitmap image) and retrieve image from sqlite database in android?

Setting Up the database

public class DatabaseHelper extends SQLiteOpenHelper {
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "database_name";

    // Table Names
    private static final String DB_TABLE = "table_image";

    // column names
    private static final String KEY_NAME = "image_name";
    private static final String KEY_IMAGE = "image_data";

    // Table create statement
    private static final String CREATE_TABLE_IMAGE = "CREATE TABLE " + DB_TABLE + "("+ 
                       KEY_NAME + " TEXT," + 
                       KEY_IMAGE + " BLOB);";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        // creating table
        db.execSQL(CREATE_TABLE_IMAGE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // on upgrade drop older tables
        db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE);

        // create new table
        onCreate(db);
    }
}

Insert in the Database:

public void addEntry( String name, byte[] image) throws SQLiteException{
    SQLiteDatabase database = this.getWritableDatabase();
    ContentValues cv = new  ContentValues();
    cv.put(KEY_NAME,    name);
    cv.put(KEY_IMAGE,   image);
    database.insert( DB_TABLE, null, cv );
}

Retrieving data:

 byte[] image = cursor.getBlob(1);

Note:

Before inserting into database, you need to convert your Bitmap image into byte array first then apply it using database query.
When retrieving from database, you certainly have a byte array of image, what you need to do is to convert byte array back to original image. So, you have to make use of BitmapFactory to decode.

Below is an Utility class which I hope could help you:

public class DbBitmapUtility {

    // convert from bitmap to byte array
    public static byte[] getBytes(Bitmap bitmap) {
        ByteArrayOutputStream stream = new ByteArrayOutputStream();
        bitmap.compress(CompressFormat.PNG, 0, stream);
        return stream.toByteArray();
    }

    // convert from byte array to bitmap
    public static Bitmap getImage(byte[] image) {
        return BitmapFactory.decodeByteArray(image, 0, image.length);
    }
}

Further reading

If you are not familiar how to insert and retrieve into a database, go through this tutorial.