How to break cursor processing while cursor object hold first row and move to fetch second row?

Question

As I'm fetching only one column from SqLite database but getting more than 1MB of data in my cursor object and I can't split my database query. Is it possible to break cursor processing as soon as cursor fetches first row and at that particular time I want to hold this cursor object values in another object. After that cursor clear this value and move to next for fetching second row in database, this continues till the end of records ?

What kind of data are you fetching from the column that is so big? Is it a blob? — Benoît Bouré, Oct 09 '12 at 11:24
As I understand you are looking for traditional behavior of cursor (PL/SQL cursor "one way ticket", `int sqlite3_step()`). Right? Look, for current devices 10-20M for cached cursor data is not a problem. Are you ok to use 3party libs to avoid a such memory "abuse"? — ra., Oct 09 '12 at 18:30

score 1 · Answer 1 · answered Oct 09 '12 at 19:17

If you are using Cursor(SQLiteCursor) - there is no way to prevent cursor from "eating memory"(break processing as you says) after you fetched first row.

android.database.sqlite is a java wrapper for sqlite3 library which is written on C.
The fact is that sqlite3 has no function to count how much records statement will produce, so you have to scan whole resultset with help of sqlite3_step function until it returns SQLITE3_DONE.
SQLiteCursor is derived from CursorWindow. CursorWindow (has some native methods) at the moment Cursors getCount() method is called first time - it does two things : calculates count of row and caches these rows.

There is custom port(russian) of sqlite3 for android with functionality you need.
If you can not read russian:
java code
native code
native sources

score 1 · Accepted Answer · answered Oct 10 '12 at 07:52

1

What if you do the following? (This is just an idea) Fetch all rows you need with the id column only (fetch the id instead of the blob column). Iterate throw that cursor and for each line fetch only one row for the given id with your blob. Then you close that Cursor and you open a new one for the next id row:

//just fetch the ids of the wanted rows
Cursor idCursor = db.query("TABLE_NAME",new String[]{"_id"}, null, null, null,null,null);
Cursor blobCursor;

    //for each row (id)
    while(idCursor.moveToNext())
    {

        //fetch one row with the blob of the given id
        blobCursor = db.query("TABLE_NAME",new String[]{"image"}, "_id = ?", new String[] {new Long(idCursor.getLong(0)).toString()}, null,null,null);

        if(blobCursor.moveToFirst())
        {

            //get the blob and store it
            blobCursor.getBlob(0); 

        }

        blobCursor.close(); //close the cursor (and release resources)

    }
idCursor.close();

answered Oct 10 '12 at 07:52

Benoît Bouré

1,017
9
18

>>As I'm fetching **only one** column from SqLite database but getting more than 1MB of data<< ... – ra. Oct 10 '12 at 09:03
Do you mean that your table only has one column? What I meant is to fetch all rows without the blob (to have a smaller Cursor with just primitives) and then fetch all the blobs but one by one (releasing resources each time)... – Benoît Bouré Oct 10 '12 at 09:09
@ra, sorry I thought that you were Manish. Let's see what he says – Benoît Bouré Oct 10 '12 at 09:19
resultset and table sometimes not the same – ra. Oct 10 '12 at 09:19
@Manish, so what do you think of my idea? fetch only ids first then id by id, fetch the associated blob and release the cursor. – Benoît Bouré Oct 10 '12 at 10:38
Benoît Bouré idea is for you. – ra. Oct 10 '12 at 10:39
I have 1 columns and in cursor I retrieve more than 1MB of data and it is blob – Manish Dubey Oct 11 '12 at 11:09

How to break cursor processing while cursor object hold first row and move to fetch second row?

2 Answers2