Android Question Row too big to fit into CursorWindow

janitra

Member
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
 

jahswant

Well-Known Member
Licensed User
Longtime User
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?

 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
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.
 
Upvote 0

janitra

Member
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.
 
Upvote 0

janitra

Member
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.
 
Upvote 0

janitra

Member
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) = ?
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
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) = ?
 
Upvote 0

toby

Well-Known Member
Licensed User
Longtime User
A simple project with sample data should be provided in order for other people to better help you.
 
Upvote 0

janitra

Member
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.
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
"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.
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
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
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
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)
 
Upvote 0
Top