Retrieve Large Blob from Android SQLite Database

Retrieve large blob from Android sqlite database

You can read large blobs in pieces. First find out which ones need this treatment:

SELECT id, length(blobcolumn) FROM mytable WHERE length(blobcolumn) > 1000000

and then read chunks with substr:

SELECT substr(blobcolumn,       1, 1000000) FROM mytable WHERE id = 123
SELECT substr(blobcolumn, 1000001, 1000000) FROM mytable WHERE id = 123
...

You could also compile your own copy of SQLite and access either the BLOB stream I/O functions or the normal query functions of the C API with the NDK, but that would be too complex in this case.

Android sqlite how to retrieve blob 1 mb in chunks correctly

You want to read CHUNK_SIZE bytes at a time.
While the offset is correct, the length given to substr() increases with each chunk:

        int ceiling =  (i+1)*CHUNK_SIZE > blobLength ? blobLength : (i+1) *CHUNK_SIZE;
        Cursor readBlobChunk =  android.rawQuery("select substr(Picture, " +  i*CHUNK_SIZE + 1 + "," + ceiling + ") from table where id =" + String.valueOf(id), null);

The logic becomes simpler if you track the remaining bytes:

remaining = blobLength;
while (remaining > 0) {
    int chunk_size = remaining > CHUNK_SIZE ? CHUNK_SIZE : remaining;
    query("SELECT substr(Picture, "+ (i*CHUNK_SIZE+1) + ", " + chunk_size + "...");
    ...
    remaining -= chunk_size:
}

How to retrieve a large BLOB from database in android?

You should store the path of the image into the database and the use the path to access/display the image.

An Example

The following is an example where, for convenience, the images (image001.JPG through to image010.JPG) are stored in the assets folder.

The example displays a list (ListView) of the images, along with 1 image and clicking on an item in the list changes the display to show the respective image via the stored path.

First the layout activity_main.xml (note package name would have to be changed accordingly) :-

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:orientation="vertical"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context="mjt.sqliteexamples.MainActivity">
    <ListView
        android:id="@+id/imagelist"
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
    </ListView>
    <ScrollView
        android:layout_width="match_parent"
        android:layout_height="match_parent">
        <LinearLayout
            android:orientation="vertical"
            android:layout_width="match_parent"
            android:layout_height="match_parent">
            <ImageView
                android:id="@+id/myimage"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:contentDescription="myImage"/>
        </LinearLayout>
    </ScrollView>
</LinearLayout>

The DatabaseHelper ImgDBHelper.java

public class ImgDBHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "myimagestore";
    public static final String TBLNAME = "images";
    public static final String ID_COL = "_id";
    public static final String DSCR_COL = "description";
    public static final String PATH_COL = "path"; //<<<<<<<<

    SQLiteDatabase db;

    ImgDBHelper(Context context) {
        super(context,DBNAME,null,1);
        db = this.getWritableDatabase();
    }
    @Override
    public void onCreate(SQLiteDatabase db) {    
        String crtsql = "CREATE TABLE IF NOT EXISTS " + TBLNAME + "(" +
                ID_COL + " INTEGER PRIMARY KEY, " +
                DSCR_COL + " TEXT," +
                PATH_COL + " TEXT" +
                ")";
        db.execSQL(crtsql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }

    public void addImageRow(String path, String description) {
        Cursor csr = db.query(TBLNAME,null,PATH_COL + "=?",new String[]{path},null,null,null);

        // DO NOT ADD DUPLICATE IMAGES (according to path)
        if (csr.getCount() > 0) {
            csr.close();
            return;
        }
        csr.close();

        ContentValues cv = new ContentValues();
        cv.put(DSCR_COL,description);
        cv.put(PATH_COL,path);

        db.insert(TBLNAME,null,cv);
    }

    public Cursor getAllImages() {
        return db.query(TBLNAME,null,null,null,null,null,null);
    }

    public String getImagePathFromID(long id) {
        String rv = "";
        Cursor csr = db.query(TBLNAME,
                null,
                "_id=?",
                new String[]{Long.toString(id)},
                null,null,null
        );
        if (csr.moveToFirst()) {
            rv = csr.getString(csr.getColumnIndex(PATH_COL));
        }
        csr.close();
        return rv;
    }

    public boolean areImagesLoaded() {
        Cursor csr = db.query(TBLNAME,null,null,null,null,null,null);
        boolean rv = (csr.getCount() > 0);
        csr.close();
        return rv;
    }
}

And the Activity MainActivity.java

public class MainActivity extends AppCompatActivity {

    Drawable d;                        // drawable for the image
    String imagepath = "image001.JPG"; // initial image
    ImgDBHelper imgdbhlpr;             // DB Helper
    ListView imagelist;                // ListView
    SimpleCursorAdapter sca;           // ListView's Cursor adapter
    Cursor images;                     // Cursor for the ListView
    ImageView iv;                      // The ImageView to display the image

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        imagelist = (ListView) findViewById(R.id.imagelist);
        iv = (ImageView) findViewById(R.id.myimage);

        imgdbhlpr = new ImgDBHelper(this);

        // Load the table if there are no images
        if (!imgdbhlpr.areImagesLoaded()) {
            for (int i=1;i < 11;i++) {

                String path = "image" +
                        String.format("%03d",i) +
                                ".JPG"
                        ;
                imgdbhlpr.addImageRow(path,"Image " +
                String.format("%03d",i));
            }
        }

        // get a cursor with all of the rows
        images = imgdbhlpr.getAllImages();

        // prepare the listview's adapter
        sca = new SimpleCursorAdapter(this,
                android.R.layout.simple_list_item_1,
                images,
                new String[]{ImgDBHelper.DSCR_COL},
                new int[]{android.R.id.text1},
                0
        );

        // set the Listview's onItemClick listener so that clicking an
        // item displays the respective image
        imagelist.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> adapterView, View view, int i, long l) {
                iv.setImageDrawable(d = getImageFromAssets(
                        imgdbhlpr.getImagePathFromID(l)
                ));
            }
        });
        imagelist.setAdapter(sca);

        // set the initial image (image001.JPG)
        d = getImageFromAssets(imagepath);
        iv.setImageDrawable(d);
    }

    // routine to create a drawable according to the supplied path
    private  Drawable getImageFromAssets(String imagepath) {
        Drawable drawable = null;
        AssetManager am = this.getAssets();
        InputStream is = null;
        try {
            is = am.open(imagepath);
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            drawable = Drawable.createFromStream(is,null);
        } finally {
            try {
                is.close();
                is = null;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return drawable;
    }
}

When initially run :-

Sample Image

And then after clicking on the Listview (image004) :-

Sample Image

Large BLOB getting from SQLiteDatabase in android

You need to check cursor.moveToFirst() then only you access the Cursor Data.like

 Cursor cursor = database.query(Const.TABLE_NAME1, null, null, null, null, null, null);

do{
  if(cursor.moveToFirst()) {

  //Do your job

 }
}while(cursor.moveToNext());

 cursor.close();

Access Large BLOB in Android Sqlite without Cursor

As CL mentioned, using NDK is indeed a way to access Sqlite natively via C language in Java language. However I realized it could get really messy if I wanted to write a custom wrapper myself and try to access the functions in Java.

After searching around, I came across a brilliant open source project called Sqlite4java which is a tight wrapper around Sqlite, compiled to use on various platforms including Android. This library allows you to interact with Sqlite without using Android Cursor which removes the limitations.

I am able to retrieve 20 MB of Blob in 480 milliseconds. This is even faster than reading a small record from Sqlite via Cursors. I believe this can be used to enhance any query to Sqlite by skipping the use of Cursor. Here's the link to this great library: http://code.google.com/p/sqlite4java/

Writing large blob of unknown size into SQLite

The only three ways of modifying blobs are

to use SQL statements, or
to use the sqlite3_blob_*() interface, which cannot resize blobs, or
to modify the database file directly.

Due to SQLite's record format, changing the size of a blob can require rewriting the entire row. Therefore, what you want cannot be done efficiently.

I'd consider writing the stream into a temporary file and handling it afterwards.

sqlite get field with more than 2 MB

It is not advisable to store large BLOBS or similar data in the SQLite database. You should use the file system and only store a reference to the data in you database.
Refer to this answer

Retrieve Large Blob from Android SQLite Database