Is it a good idea to store bitmap as BLOB in Sqlite?

Question

If I store too many bitmaps (as BLOB) in my sqlite database, while retrieving them back I get strange exception (which I can't catch even as Throwable, but I can see it in LogCat every time):

Failed to read row 2, column 0 from a CursorWindow which has 2 rows, 8 columns

When I don't store BLOBs, I don't get such exceptions. Maybe I should store images at phone memory and keep at database uris to this images?

What is the problem? Could anyone tell me?

score 3 · Accepted Answer · edited May 23 '17 at 12:30

Usually not a good idea to store bitmaps/images in general on your database, it's not efficient at all.

You should save the bitmaps as images and store the paths on the database.

This was discussed on this question.

EDIT:

But... If you're really into storing that for whatever reason, you could also try to encode the image as a Base64 String, and store it on your DB.

Android has the Base64 class for this. Try to use the following snippet to encode:

ByteArrayOutputStream baos = new ByteArrayOutputStream();  
bitmap.compress(Bitmap.CompressFormat.JPEG, 100, baos); // Could be Bitmap.CompressFormat.PNG or Bitmap.CompressFormat.WEBP
byte[] bai = baos.toByteArray();

String base64Image = Base64.encodeToString(bai, Base64.DEFAULT);

// Call your method to save this string on the DB here.

And you'll have to decode it try the following:

byte[] data = Base64.decode(base64Image, Base64.DEFAULT);
Bitmap bm;
BitmapFactory.Options opt = new BitmapFactory.Options();
opt.inMutable = true;
bm = BitmapFactory.decodeByteArray(data, 0, data.length, opt);

// Now do whatever you want with the Bitmap.

You can see the docs for the Bitmap class here.

You should prefer BLOB over base64 because http://stackoverflow.com/a/9723588/4754141 and http://stackoverflow.com/a/8236317/4754141 — shanraisshan, Sep 07 '16 at 11:24

score 1 · Answer 2 · answered Nov 28 '16 at 06:42

  profile_images.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View v) {


    Intent photoPickerIntent = new Intent(Intent.ACTION_PICK);
                photoPickerIntent.setType("image/*");
                startActivityForResult(photoPickerIntent, SELECT_PHOTO);

            }
        });


  protected void onActivityResult(int requestCode, int resultCode, Intent imageReturnedIntent) {
        super.onActivityResult(requestCode, resultCode, imageReturnedIntent);

        switch(requestCode) {
            case SELECT_PHOTO:
                if(resultCode == RESULT_OK){
                    Uri selectedImage = imageReturnedIntent.getData();
                    try {
                        Bitmap bmp = decodeUri(selectedImage);
                        profile_images.setImageBitmap(bmp);
                        ByteArrayOutputStream stream = new ByteArrayOutputStream();
                        bmp.compress(Bitmap.CompressFormat.JPEG, 100, stream);
                        byte[] byteArray = stream.toByteArray();

                        insertUser(byteArray);

                    }catch (Exception e){
                        e.printStackTrace();
                    }
                }
        }
    }
public void insertUser(byte[] logoImage ){
    SQLiteDatabase db               =   dbs.getWritableDatabase();

    String delSql                       =   "DELETE FROM Image";
    SQLiteStatement delStmt         =   db.compileStatement(delSql);
    delStmt.execute();

    String sql                      =   "INSERT INTO Image (CODE,Img) VALUES(?,?)";
    SQLiteStatement insertStmt      =   db.compileStatement(sql);
    insertStmt.clearBindings();
    insertStmt.bindLong(1, 1);
    insertStmt.bindBlob(2,logoImage);
    //   insertStmt.bindBlob(3, this.accImage);
    insertStmt.executeInsert();
    db.close();
}



  public Bitmap getCurrentBitmap() {
        SQLiteDatabase db       =   dbs.getWritableDatabase();
        String sql              =   "SELECT * FROM Image";
        Cursor cursor           =   db.rawQuery(sql, new String[]{});
        Bitmap bmp=null;
        if(cursor.moveToFirst()){
            //this.accId             = cursor.getInt(0);
            //  this.accName           = cursor.getString(1);
            byte[] logoImage       = cursor.getBlob(1);

            bmp = BitmapFactory.decodeByteArray(logoImage, 0, logoImage.length);
        }
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
        }
        db.close();
        if(cursor.getCount() == 0){
            return null;
        } else {
            return bmp;
        }
    }


    private Bitmap decodeUri(Uri selectedImage) throws FileNotFoundException {

        // Decode image size
        BitmapFactory.Options o = new BitmapFactory.Options();
        o.inJustDecodeBounds = true;
        BitmapFactory.decodeStream(getContentResolver().openInputStream(selectedImage), null, o);

        // The new size we want to scale to
        final int REQUIRED_SIZE = 140;
    // Find the correct scale value. It should be the power of 2.
    int width_tmp = o.outWidth, height_tmp = o.outHeight;
    int scale = 1;
    while (true) {
        if (width_tmp / 2 < REQUIRED_SIZE
                || height_tmp / 2 < REQUIRED_SIZE) {
            break;
        }
        width_tmp /= 2;
        height_tmp /= 2;
        scale *= 2;
    }

    // Decode with inSampleSize
    BitmapFactory.Options o2 = new BitmapFactory.Options();
    o2.inSampleSize = scale;
    return BitmapFactory.decodeStream(getContentResolver().openInputStream(selectedImage), null, o2);

}

you can do this as above given methods.

Is it a good idea to store bitmap as BLOB in Sqlite?

2 Answers2

Linked