How to Insert an Image in Sqlite Database(Table)

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

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.

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 insert your own image into a SQLite table?

One of the most important factors to consider is the size of the images. If the size is likely to be in excess of say 200k then there is an increasing risk of there being issues not with storing the image in the database but retrieving the image.

Without writing your own alternative of CursorWindow then a CursorWindow (a buffer to hold a sub-group of the rows in a Cursor) is limited to 2M in size (in later versions at some it I believe it was 1M). If a picture is approaching 2MB there is no way that the picture can be retrieved (even without considering space for other columns).

As such, the generally recommended approach is to not store images but store a means of retrieving the image from a file store elsewhere (you've mentioned the res folder, which could be fine but you may need to consider the size of the APK), you could then store the file path in the database.

There's a more comprehensive answer that covers the above including storing small images in the DB and larger images elsewhere (assets folder) here

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;
}

How to insert an Image to sqlite database

I solved the problem by placing:

Bitmap bmp = MediaStore.Images.Media.getBitmap(this.getContentResolver(), imageUri);

alone in the try-catch clause. I also replaced the:

db.insertImage(cv,editword,editcategory);
Toast.makeText(this, "Image Uploaded", Toast.LENGTH_SHORT).show();

in a new function