Android Question Cursor size too big

manuel_g · Aug 22, 2021

Hello!

I'm getting this error when I do a SQL Select from a pretty big table in sqlite

android.database.sqlite.SQLiteBlobTooBigException: Row too big to fit into CursorWindow requiredPos=2, totalRows=2

I can use without a problem the row 0 and row 1 but when c.position=2 it crashs

Is there a way to know the whole cursor size?

Thank you!

mcqueccu · Aug 23, 2021

B4X:

cursor.rowcount

Addo · Aug 23, 2021

You can do pagination. The client side will be smooth if you just paginate such big data

Erel · Aug 23, 2021

Please post the code. Tip: you should use ResultSet instead of Cursor.

Sandman · Aug 23, 2021

manuel_g said:
Is there a way to know the whole cursor size?

Erel said:
Tip: you should use ResultSet instead of Cursor.

Check out #21:

[B4X] Features that Erel recommends to avoid

Many things have changed in B4X and also in the underlying platforms. I will try to list here all kinds of (old) features that have better alternatives. B4X is backward compatible so these features still work. The recommendations are more relevant for new projects or when implementing new...

www.b4x.com

(Posted this in case @manuel_g had missed that post.)

manuel_g · Aug 23, 2021

Hi everybody! THANKS for replaying.

Sandman said:
Check out #21:

[B4X] Features that Erel recommends to avoid

Many things have changed in B4X and also in the underlying platforms. I will try to list here all kinds of (old) features that have better alternatives. B4X is backward compatible so these features still work. The recommendations are more relevant for new projects or when implementing new...

www.b4x.com

(Posted this in case @manuel_g had missed that post.)

Thank you for this! I will change several things.

I attached a simple project: create a database and marks the rows that it reads changing the 'unread' to 'read' value
with two buttons
- Wont crush button: fills the database with 3 small string records
- Will crush button: fills the database with a 3 big blob records (1.8Mb eachone)

Please run both buttons and you'll notice than the Will Crush doesn't show the ResultSet ID=2

Please download this image an copy into Assets Files

Than you all!

OliverA · Aug 24, 2021

manuel_g said:
Will crush button: fills the database with a 3 big blob records (1.8Mb eachone)

That should be expected. Android's implementation of SQLite has a cursor size limit. See https://www.b4x.com/android/forum/threads/sql-cursor-memory-size.71871/

manuel_g · Aug 24, 2021

OliverA said:
That should be expected. Android's implementation of SQLite has a cursor size limit. See https://www.b4x.com/android/forum/threads/sql-cursor-memory-size.71871/

I know about that limit and every row is smaller than the limit, but there is something too weird.

Please , on line #99 change For i = 1 To 3 -> For i = 1 To 10, and check the logs and it will read the 1, 3, 7, 9 records on the table... Why the 2, 4, 6, 8 rows are not read?

On the other hand, if you comment the line #54 (the one that change tha validation status on the row) the Will Crush option doesn't crush......

OliverA · Aug 24, 2021

But it also applies to the CursorWindow object that has the same memory limits out of the box. See . (Giving this a cursory glance: https://github.com/AnywhereSoftware..._SQL/src/anywheresoftware/b4a/sql/SQL.java)To get around this limit, B4A's SQLite interface would have to be rewritten to use android.database.sqlite.SQLiteCursor instead of android.database.Cursor, since SQLiteCursor allows for the setting of a larger CursorWindow size. I don't know if it would be worth it for the few edge cases that would benefit from this. Even the medium article suggests using queries with smaller results.

manuel_g · Aug 24, 2021

I found a solution without doing too much

I use another cursor that has only the Id value and I use that cursor to "find" the whole row in the main and big ResultSet. It never crush

Original one:

B4X:

'Read the Sqlite
Private Sub change_validation
    
    Dim SenderFilter As Object = sql.ExecQueryAsync("SQL", "select * from test_table where validation = 'unread'", Null)
    Wait For (SenderFilter) SQL_QueryComplete (Success As Boolean, rs As ResultSet)
    If Success Then
        Do While rs.NextRow
            Log($"ResultSet ID=${rs.GetInt("id")}"$)
            sql.ExecNonQuery2("update test_table set validation=? where id=?",Array As String("read",rs.GetInt("id")))
        Loop
        rs.Close
    End If
    
    ProgressDialogHide
End Sub

New one:

B4X:

'Read the Sqlite
Private Sub change_validation
    
    Dim c As Cursor = sql.ExecQuery("Select id from test_table where validation = 'unread' ")'NEW   
    
    Dim SenderFilter As Object = sql.ExecQueryAsync("SQL", "select * from test_table where validation = 'unread'", Null)
    Wait For (SenderFilter) SQL_QueryComplete (Success As Boolean, rs As ResultSet)
    If Success Then
        For i=0 To c.RowCount-1 'NEW
            c.Position=i 'NEW
            Log($"ResultSet ID=${c.GetInt("id")}"$)
            sql.ExecNonQuery2("update test_table set validation=? where id=?",Array As String("read",c.GetInt("id"))) 'MODIFIED
        Next
        rs.Close
        c.close
    End If
    
    ProgressDialogHide
End Sub

Do you think it is ok?

Thank you

OliverA · Aug 24, 2021

Simplified:

B4X:

Private Sub change_validation
    
    Dim SenderFilter As Object = sql.ExecQueryAsync("SQL", "select id from test_table where validation = 'unread'", Null)
    Wait For (SenderFilter) SQL_QueryComplete (Success As Boolean, rs As ResultSet)
    If Success Then
        Do While rs.NextRow
            Log($"ResultSet ID=${rs.GetInt("id")}"$)
            sql.ExecNonQuery2("update test_table set validation=? where id=?",Array As String("read",rs.GetInt("id")))
        Loop
        rs.Close
    End If
    
    ProgressDialogHide
End Sub

manuel_g · Aug 24, 2021

OliverA said:

Simplified:

B4X:

Private Sub change_validation
   
    Dim SenderFilter As Object = sql.ExecQueryAsync("SQL", "select id from test_table where validation = 'unread'", Null)
    Wait For (SenderFilter) SQL_QueryComplete (Success As Boolean, rs As ResultSet)
    If Success Then
        Do While rs.NextRow
            Log($"ResultSet ID=${rs.GetInt("id")}"$)
            sql.ExecNonQuery2("update test_table set validation=? where id=?",Array As String("read",rs.GetInt("id")))
        Loop
        rs.Close
    End If
   
    ProgressDialogHide
End Sub

No, because I need to work with all the columns (thats why Select *) in the ResultSet

OliverA · Aug 24, 2021

manuel_g said:
No, because I need to work with all the columns (thats why Select *) in the ResultSet

And where are you working with all these columns? Nowhere in your sample code are you working with any of the columns that are returned by "select * from test_table where validation = 'unread'". I'm confused

manuel_g · Aug 24, 2021

OliverA said:
And where are you working with all these columns? Nowhere in your sample code are you working with any of the columns that are returned by "select * from test_table where validation = 'unread'". I'm confused

I do not show that. I use all the columns to send them with a job.PostString(), but it's not relevant with the error, as you see the error shows up before using all the columns.

I've notice that the problem is when I change the 'unred' to 'read' value. If you comment that line it won't crash and will read all the rows, not just the unpair ones.

On the sample code I change the 'unread' to 'read' becouse I do that in the original project and It is the specific problem

Thank you so much

Mahares · Aug 24, 2021

manuel_g said:
Do you think it is ok?

You should not include cursor with resultset like you have. No need to use cursor. Here is your complete sub. It works:

B4X:

Private Sub change_validation  
    Dim rs As ResultSet = sql.ExecQuery2("Select id from test_table where validation = ? ", Array As String("unread"))
    Do While rs.NextRow
        Log($"ResultSet ID=${rs.GetInt("id")}"$)
        sql.ExecNonQuery2("update test_table set validation=? where id=?",Array As String("read",rs.GetInt("id"))) 'MODIFIED
        Log("done")
    Loop
    rs.close
    ProgressDialogHide    
End Sub

Then if you want to work with all the columns, you can do this:

B4X:

Dim rs As ResultSet
    rs=sql.ExecQuery("SELECT * FROM test_table") 'you can use * or validation or other cols
    Do While rs.NextRow
        Log(rs.GetString("validation"))
    Loop
    rs.close

Android Question Cursor size too big

manuel_g

Member

mcqueccu

Well-Known Member

Addo

Well-Known Member

Erel

B4X founder

Sandman

Expert

[B4X] Features that Erel recommends to avoid

manuel_g

Member

[B4X] Features that Erel recommends to avoid

Attachments

OliverA

Expert

manuel_g

Member

OliverA

Expert

manuel_g

Member

OliverA

Expert

manuel_g

Member

OliverA

Expert

manuel_g

Member

Mahares

Expert

Similar Threads

Android Question Cursor size too big

Member

Well-Known Member

Well-Known Member

B4X founder

Expert

Member

Attachments

Expert

Member

Expert

Member

Expert

Member

Expert

Member

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency