Android Question Cursor size too big

manuel_g

Member
Licensed User
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!
 

Sandman

Expert
Licensed User
Longtime User
Is there a way to know the whole cursor size?
Tip: you should use ResultSet instead of Cursor.

Check out #21:

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

manuel_g

Member
Licensed User
Hi everybody! THANKS for replaying.

Check out #21:

(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!
 

Attachments

  • BigSql.zip
    9.7 KB · Views: 162
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Upvote 0

manuel_g

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

OliverA

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

manuel_g

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

OliverA

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

manuel_g

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

OliverA

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

manuel_g

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

Mahares

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