Android Question Cursor size too big

[manuel_g]

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]

cursor.rowcount

 

[Addo]

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

 

[Erel]

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

 

[Sandman]

Is there a way to know the whole cursor size?

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]

Hi everybody! THANKS for replaying.

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]

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]

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]

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]

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:

'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:

'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]

Simplified:

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]

Simplified:

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]

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]

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]

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:

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:

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