Android Question Row too big to fit into CursorWindow

janitra · Jan 18, 2022

Hello!

I'm getting this error when I do a SQL Select from in SQLite.
The data is not big, the query only selecting 3 columns and getting 8 rows of data.

Error Log:

android.database.sqlite.SQLiteBlobTooBigException: Row too big to fit into CursorWindow requiredPos=7, totalRows=8
    at android.database.sqlite.SQLiteConnection.nativeExecuteForCursorWindow(Native Method)
    at android.database.sqlite.SQLiteConnection.executeForCursorWindow(SQLiteConnection.java:859)
    at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:836)
    at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)
    at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:159)
    at android.database.sqlite.SQLiteCursor.onMove(SQLiteCursor.java:128)
    at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:237)
    at anywheresoftware.b4a.sql.SQL$CursorWrapper.setPosition(SQL.java:322)
    at anywheresoftware.b4a.sql.SQL$ResultSetWrapper.NextRow(SQL.java:458)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:732)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:348)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:193)
    at anywheresoftware.b4a.shell.DebugResumableSub$RemoteResumableSub.resume(DebugResumableSub.java:22)
    at anywheresoftware.b4a.BA.checkAndRunWaitForEvent(BA.java:267)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:137)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:193)
    at anywheresoftware.b4a.keywords.Common$1.onClick(Common.java:490)
    at com.android.internal.app.AlertController$ButtonHandler.handleMessage(AlertController.java:172)
    at android.os.Handler.dispatchMessage(Handler.java:106)
    at android.os.Looper.loop(Looper.java:201)
    at android.app.ActivityThread.main(ActivityThread.java:6831)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:547)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:927)

I using a Function that do the query and then return a List to be processed in Main Form.

Code:

Public Sub SelectArray(query As String, param() As Object) As List
    Dim result As List
    result.Initialize
   
    Dim resultSet As ResultSet = sqlLite.ExecQuery2(query, param)
   
    Dim columnCount As Int = resultSet.ColumnCount
    Dim rowCount As Int = 0
   
    Do While resultSet.NextRow
        Dim value(columnCount) As Object
       
        For j = 0 To columnCount - 1
            resultSet.Position = rowCount
            value(j) = resultSet.GetString(resultSet.GetColumnName(j))
        Next
       
        result.Add(value)
        rowCount = rowCount + 1
    Loop
   
    Return result
End Sub

I try to debug but find nothing wrong with the code.
Other weird thing is, the Function is not always error.
Is there anything wrong with my code?

Thanks

Erel · Jan 18, 2022

First step is to close resultSet when done with it. I don't know whether it is related to this error or not.

jahswant · Jan 18, 2022

It appears that Cursors in android can only hold up to 1 MB of data.

What would be the most efficient way to pull the maximum number of rows from a table in a SQLite database that stays under the 1 MB limit?

Android Cursor maximum size

It appears that Cursors in android can only hold up to 1 MB of data. What would be the most efficient way to pull the maximum number of rows from a table in a SQLite database that stays under th...

stackoverflow.com

aeric · Jan 18, 2022

janitra said:
resultSet.Position = rowCount

Something looks unusual here.

DonManfred · Jan 18, 2022

janitra said:
Is there anything wrong with my code?

what is the amount of data being returned by the query? Only a few varchar fields or are there blobs you read from the database?

You told only 3 columns. What data are in these three columns? How big is the data from these columsns?

Based on the error the amount of data are to big to the resultset. SQLite has a 1-2mb limit you can fetch from the Database at once.

janitra · Jan 18, 2022

DonManfred said:
what is the amount of data being returned by the query? Only a few varchar fields or are there blobs you read from the database?

You told only 3 columns. What data are in these three columns? How big is the data from these columsns?

Based on the error the amount of data are to big to the resultset. SQLite has a 1-2mb limit you can fetch from the Database at once.

Hi Don,

The amount of data is 8 row.

The field type is Text, no blob.

The data is text only, not more than 50 characters each column.

janitra · Jan 18, 2022

aeric said:
Something looks unusual here.

Hi Aeric,

It looks like I have to set the Result Set row Position.
If I delete that, I got another error.
Will post it in another thread.

99.99% the Function works fine.
Even that 8 rows returned, when I debug it, it return 7 row then error at the 8.

aeric · Jan 18, 2022

Can you post a small example with the db?

janitra · Jan 19, 2022

aeric said:
Can you post a small example with the db?

Hi Aeric,

This is the table

Table:

sqlLite.ExecNonQuery("CREATE TABLE IF NOT EXISTS SIV (SIVCode TEXT, " _
    & "CompanyNo TEXT, " _
    & "StoreCode TEXT, " _
    & "SIVDate DATE, " _
    & "Description TEXT, " _
    & "IsSync TEXT)")

sqlLite.ExecNonQuery("CREATE TABLE IF NOT EXISTS SIVDetails (SIVCode TEXT, " _
    & "CompanyNo TEXT, " _
    & "StockSeq TEXT, " _
    & "StockItemCode TEXT, " _
    & "Quantity TEXT, " _
    & "Price TEXT, " _
    & "Amount TEXT, " _
    & "Description TEXT, " _
    & "ItemType TEXT, " _
    & "IsSync TEXT)")

This is the query

Query:

SELECT DISTINCT SIV.SIVCode AS SIVCode, SIV.SIVDate, SIV.CompanyNo
FROM SIV LEFT JOIN SIVDetails SIVD ON SIV.SIVCode = SIVD.SIVCode AND SIV.CompanyNo = SIVD.CompanyNo
WHERE (SIV.IsSync = '0' OR SIVD.IsSync = '0') AND
CAST(strftime('%Y', SUBSTR(SIV.SIVDate, 7, 4) || '-' || SUBSTR(SIV.SIVDate, 1, 2) || '-' ||
SUBSTR(siv.SIVDate, 4, 2)) As INTEGER) = ?
AND CAST(strftime('%m', SUBSTR(SIV.SIVDate, 7, 4) || '-' || SUBSTR(SIV.SIVDate, 1, 2) || '-' ||
SUBSTR(SIV.SIVDate, 4, 2)) AS INTEGER) = ?

aeric · Jan 19, 2022

janitra said:
resultSet.Position = rowCount

There is no method call Position in Resultset. It only existed in Cursor.

You didn't provide sample data. I add some dummy data myself and run with no problem.

B4X:

Public Sub SelectArray(query As String, param() As Object) As List
    Dim result As List
    result.Initialize
  
    Dim resultSet As ResultSet = DB.ExecQuery2(query, param)
  
    Dim columnCount As Int = resultSet.ColumnCount
    'Dim rowCount As Int = 0
  
    Do While resultSet.NextRow
        Dim value(columnCount) As Object
      
        For j = 0 To columnCount - 1
            'resultSet.Position = rowCount
            'value(j) = resultSet.GetString(resultSet.GetColumnName(j))
            value(j) = resultSet.GetString2(j)
        Next
      
        result.Add(value)
        'rowCount = rowCount + 1
    Loop
    resultSet.Close
    Return result
End Sub

If your SIVDate is in "dd-MM-YYYY" format then the query should be:

SQL:

    SELECT DISTINCT
    SIV.SIVCode AS SIVCode, SIV.SIVDate, SIV.CompanyNo
    FROM SIV
    LEFT JOIN SIVDetails SIVD
    ON SIV.SIVCode = SIVD.SIVCode
    AND SIV.CompanyNo = SIVD.CompanyNo
    WHERE (SIV.IsSync = '0' OR SIVD.IsSync = '0')
    AND CAST(strftime('%Y', SUBSTR(SIV.SIVDate, 7, 4) || '-' || SUBSTR(SIV.SIVDate, 4, 2) || '-' ||
    SUBSTR(siv.SIVDate, 1, 2)) As INTEGER) = ?
    AND CAST(strftime('%m', SUBSTR(SIV.SIVDate, 7, 4) || '-' || SUBSTR(SIV.SIVDate, 4, 2) || '-' ||
    SUBSTR(SIV.SIVDate, 1, 2)) AS INTEGER) = ?

toby · Jan 19, 2022

A simple project with sample data should be provided in order for other people to better help you.

janitra · Jan 19, 2022

aeric said:

There is no method call Position in Resultset. It only existed in Cursor.

You didn't provide sample data. I add some dummy data myself and run with no problem.

B4X:

Public Sub SelectArray(query As String, param() As Object) As List
    Dim result As List
    result.Initialize
 
    Dim resultSet As ResultSet = DB.ExecQuery2(query, param)
 
    Dim columnCount As Int = resultSet.ColumnCount
    'Dim rowCount As Int = 0
 
    Do While resultSet.NextRow
        Dim value(columnCount) As Object
    
        For j = 0 To columnCount - 1
            'resultSet.Position = rowCount
            'value(j) = resultSet.GetString(resultSet.GetColumnName(j))
            value(j) = resultSet.GetString2(j)
        Next
    
        result.Add(value)
        'rowCount = rowCount + 1
    Loop
    resultSet.Close
    Return result
End Sub

If your SIVDate is in "dd-MM-YYYY" format then the query should be:

SQL:

    SELECT DISTINCT
    SIV.SIVCode AS SIVCode, SIV.SIVDate, SIV.CompanyNo
    FROM SIV
    LEFT JOIN SIVDetails SIVD
    ON SIV.SIVCode = SIVD.SIVCode
    AND SIV.CompanyNo = SIVD.CompanyNo
    WHERE (SIV.IsSync = '0' OR SIVD.IsSync = '0')
    AND CAST(strftime('%Y', SUBSTR(SIV.SIVDate, 7, 4) || '-' || SUBSTR(SIV.SIVDate, 4, 2) || '-' ||
    SUBSTR(siv.SIVDate, 1, 2)) As INTEGER) = ?
    AND CAST(strftime('%m', SUBSTR(SIV.SIVDate, 7, 4) || '-' || SUBSTR(SIV.SIVDate, 4, 2) || '-' ||
    SUBSTR(SIV.SIVDate, 1, 2)) AS INTEGER) = ?

Hi Aeric,

I try your solution but it still error.

The sample data I get from the query is

SIVCode : SIV202211918655
This value is based on this Code

Code:

"SIV" & DateTime.GetYear(DateTime.Now) & "" & DateTime.GetMonth(DateTime.Now) _
        & "" & DateTime.GetDayOfMonth(DateTime.Now) & "" & DateTime.GetHour(DateTime.Now) _
        & "" & DateTime.GetMinute(DateTime.Now) & "" & DateTime.GetSecond(DateTime.Now)

SIVDate : 2022-01-19 (Normal date format)
CompanyNo : S01M

Is there a max length for a column?
I try to get SIVCode only from the query, it give me same error.
But if I ditch SIVCode, it work just fine.

aeric · Jan 19, 2022

janitra said:
"SIV" & DateTime.GetYear(DateTime.Now) & "" & DateTime.GetMonth(DateTime.Now) _ & "" & DateTime.GetDayOfMonth(DateTime.Now) & "" & DateTime.GetHour(DateTime.Now) _ & "" & DateTime.GetMinute(DateTime.Now) & "" & DateTime.GetSecond(DateTime.Now)

Since we don't have your actual data, it is difficult to keep continue make guesses.

The code you use to generate the SIVCode may generate inconsistent length because numbers like months could be 1 or 2 digit e.g. Jan=1 (1 digit) and Dec=12 (2 digit). This is same with day, hour, minute and seconds.

aeric · Jan 19, 2022

Try this Sub to generate the SIVCode:

B4X:

Sub GenerateSIVCode As String
    DateTime.DateFormat = "yyyyMMdd"
    DateTime.TimeFormat = "HHmmss"
    Dim CurrentTime As String = DateTime.Date(DateTime.Now) & DateTime.Time(DateTime.Now)
    'Log(CurrentTime)
    Return "SIV" & CurrentTime
End Sub

Mahares · Jan 19, 2022

janitra said:
This value is based on this Code

@aeric and @toby have been asking you to upload a simple project, so you get help, but you keep ignoring their call. Looking at your queries and code, you are complicating things unnecessarily. Upload a project and you will get help. SQLite is a very popular subject and many members are so good at it and dying to help you.
Here is another simple form for aeric's function: This or @aeric' s function are a lot easier than what you are trying to do:

B4X:

Sub GenerateSIVCode As String
    DateTime.DateFormat = "yyyyMMddHHmmss"
    Return "SIV" & DateTime.Date(DateTime.Now)
End Sub

B4X:

Log(GenerateSIVCode)

Android Question Row too big to fit into CursorWindow

janitra

Member

Erel

B4X founder

jahswant

Well-Known Member

Android Cursor maximum size

aeric

Expert

DonManfred

Expert

janitra

Member

janitra

Member

aeric

Expert

janitra

Member

aeric

Expert

toby

Well-Known Member

janitra

Member

aeric

Expert

aeric

Expert

Mahares

Expert

Similar Threads