Android Sqlite - exception: Index -1 requested, with a size of 32 --- due to large size?

Question

In my app I use a standard way of accessing my Sqlite database files. Everything is working fine for months, but for one specific row in the database there is a problem. The data in the column is 7.265.161 bytes long.

For reading this row I get I get the following error:

Index -1 requested, with a size of 32

The standard code I use is:

List<CText> cTexts = new ArrayList<>();
try {
    Cursor cursor;
    String[] tableColumns = new String[]{"book", "chapter", "fromvorm", "tovorm", "data"};
    String[] whereArgs = new String[]{"" + bookNumber, "" + chapterNumber};
    cursor = db.query(TABLE_CTEXT, tableColumns, "book = ? and chapter = ?", whereArgs, null, null, "fromvorm");
    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
        cTexts.add(new CText(cursor.getInt(0),
                cursor.getInt(1), cursor.getInt(2),
                cursor.getInt(3), cursor.getString(4)));
        cursor.moveToNext();
    }
    cursor.close();
} catch (Exception e) {
    Logger.e("Get ctext exception: " + e.getMessage(), e);
    return null;
}

Can you help solving it?

UPDATE: thanks to @ekimro we have a solution!

Upon further investigation I see that splitting the 7Mb file into portions of 1MB results in a response. It takes about 30s to get that response.

Answer 1

Problem you explain is bigger than 4Mb for CursorWindow.

Solve is redesign schema (storing data as blocks small to not bigger than 4Mb (like 256k)) or getting data in chunk, small like 256k.

Doing last like SQL

WITH RECURSIVE control(startchar,endchar,limiter,book,chapter,seq) AS (
    SELECT 1,256 * 1024, (SELECT max(length(data)) FROM ctext WHERE book = 'B2' AND chapter = '1'),'B2','1',0
    UNION ALL SELECT endchar + 1,endchar + (256 * 1024), limiter,book,chapter,seq +1 FROM control WHERE endchar < limiter LIMIT 100
)
SELECT 
    seq AS sequence,
    book AS book,
    chapter AS chapter,
    (SELECT fromvorm FROM CTEXT WHERE ctext.book = control.book AND ctext.chapter = control.chapter) AS fromvorm,
    (SELECT tovorm FROM CTEXT WHERE ctext.book = control.book AND ctext.chapter = control.chapter) AS tovorm,
    (SELECT substr(ctext.data,control.startchar,control.endchar) FROM CTEXT  WHERE ctext.book = control.book AND ctext.chapter = control.chapter ) AS chunk
FROM control  ORDER BY sequence;

getting like with test data (close 7Mb)

Android like (this only 1 book/chapter needing loop book and needing loop chapter if more). Not tested so need test.

    List<CText> cTexts = new ArrayList<>();
    Cursor cursor;
    StringBuilder data = new StringBuilder();
    String[] whereArgs = new String[]{"" + bookNumber, "" + chapterNumber, "" + bookNumber, "" + chapterNumber};
    cursor = db.rawQuery(
            "WITH RECURSIVE control(startchar,endchar,limiter,book,chapter,seq) AS " +
                    "(" +
                    "SELECT 1,256 * 1024, " +

                    "(SELECT max(length(data)) " +
                    "FROM ctext " +
                    "WHERE book = ? " +
                    "AND chapter = ?)," +

                    "?," +
                    "?," +
                    "0 " +
                    "UNION ALL SELECT " +
                    "endchar + 1," +
                    "endchar + (256 * 1024), " +
                    "limiter," +
                    "book," +
                    "chapter," +
                    "seq +1 " +
                    "FROM control " +
                    "WHERE endchar < limiter LIMIT 100" +
                    ") SELECT " +
                    "   seq AS sequence," +
                    "   book AS book," +
                    "   chapter AS chapter," +
                    "   (SELECT fromvorm " +
                    "       FROM CTEXT" +
                    "        WHERE ctext.book = control.book " +
                    "       AND ctext.chapter = control.chapter" +
                    "   ) AS fromvorm," +

                    "   (SELECT tovorm " +
                    "       FROM CTEXT " +
                    "       WHERE ctext.book = control.book " +
                    "       AND ctext.chapter = control.chapter" +
                    "   ) AS tovorm,    " +

                    "   (SELECT substr(ctext.data,control.startchar,control.endchar) " +
                    "       FROM CTEXT " +
                    "       WHERE ctext.book = control.book " +
                    "       AND ctext.chapter = control.chapter " +
                    "   ) AS chunk " +

                    "FROM control;"
            , whereArgs
    );
    while (cursor.moveToNext()) {
        data.append(cursor.getString(cursor.getColumnIndex("chunk")));
    }
    if (cursor.moveToFirst()) {
        cTexts.add(new CText(cursor.getInt(0),
                cursor.getInt(1), cursor.getInt(2),
                cursor.getInt(3), data.toString()));
    }