SQLite CursorWindow limit - How to avoid crash

Question

I have to execute a query and store the result in a list, the function that I use is this follow :

List<SpoolInDB> getSpoolInRecords(String label, boolean getLastInserted) {
    List<SpoolInDB> spoolInList = new ArrayList<>();
    try {
        if (mdb == null)
            mdb = mdbHelper.getWritableDatabase();

        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
        qb.setTables(TABLE_SPOOLIN);

        Cursor c = qb.query(mdb, null, " label='" + label + "'", null, null, null, " dateins " + (getLastInserted ? "desc" : "asc"));
        if (c != null) {
            c.moveToFirst();
            if (c.getCount() > 0) {
                int ndxid = c.getColumnIndex("id");
                int ndxserverID = c.getColumnIndex("serverID");
                int ndxlabel = c.getColumnIndex("label");
                int ndxvalue = c.getColumnIndex("value");
                int ndxpriority = c.getColumnIndex("priority");
                int ndxdateins = c.getColumnIndex("dateins");

                do {
                    SpoolInDB spoolIn = new SpoolInDB();
                    spoolIn.setId(c.getString(ndxid));
                    spoolIn.setServerID(c.getString(ndxserverID));
                    spoolIn.setLabel(c.getString(ndxlabel));
                    spoolIn.setValue(c.getString(ndxvalue));
                    spoolIn.setPriority(c.getString(ndxpriority));
                    spoolIn.setDateins(c.getString(ndxdateins));
                    spoolInList.add(spoolIn);

                } while (c.moveToNext());
            }
            c.close();
        }
    } catch (Exception e) {
        wil.WriteFile("4)DbGest - Exception: " + e.toString());
    }
    return spoolInList;
}

In a normal context this function work perfectly, but in some case this function produce an exception:

Window is full: requested allocation 3209815 bytes, free space 2096647 bytes, window size 2097152 bytes

This problem occour because in the "values" field I could store json datas that in some case could be bigger than 2mb, I can't forecast when the data is bigger than 2mb, I need a solution that work always.

How I can solve my problem ?

Ealasaid

Answer 1

CursorWindow size limit is 2MB (as of now). You cannot read a single row whose size exceeds 2MB because it is not possible to put that in a Cursor.

So instead of storing the entire JSON as a single element, you can parse it and store in separate columns or tables in the database.

So that,

1. You can leave the unwanted data in the JSON from saving in the database.
2. You can query a part of the data (few columns) at a time so that the queried data will not cross the 2MB CursorWindow limit.

Or you can try out other Database systems, like Realm (I haven't tried it, so I'm not sure if there is any limit there).

Seeker

Thanks for your answer. I guess this is the way to go with SQLIte and Big JSONs. About Realm: I've been migrating from Realm to SQLite and working with big GeoJson fields (> 5 MB). Realm has limitations for saving big GeoJSONs. SQlite for retreiving. – Zonnya