Android Question B4XTable DataUpdated Sub with a Blob Column from a SQLite Table

Mahares

Well Known Member
Licensed User
The below code in B4XTable1_DataUpdated sub works, but it seems very inefficient as the query to display the imageview with the blob has to be run for every record on the visible ids. Is there a better way to get the blobs, convert to bmp and display onto the imageviews.

B4X:
Sub B4XTable1_DataUpdated
    For i = 0 To B4XTable1.VisibleRowIds.Size - 1
        Dim RowId As Long = B4XTable1.VisibleRowIds.Get(i)
        Dim pnl As B4XView = FlagsColumn.CellsLayouts.Get(i + 1)
        Dim iv As B4XView = pnl.GetView(1)
        If RowId > 0 Then      
            Dim row As Map = B4XTable1.GetRow(RowId)
            Dim cursor1 As Cursor
            cursor1=Starter.SQL1.ExecQuery("SELECT FLAG FROM " &   Starter.DBTableName & " WHERE ID =" & row.Get("ID"))
            cursor1.Position=0
            Dim buffer() As Byte =cursor1.GetBlob("FLAG")
            iv.SetBitmap(BytesToImage(buffer))
            cursor1.Close       
        Else
            iv.SetBitmap(Null)
        End If
    Next  
End Sub
Table structure: ID INTEGER AUTOINCREMENT, COUNTRY TEXT , FLAG BLOB
Thank you
 

Erel

Administrator
Staff member
Licensed User
but it seems very inefficient
First step is to measure it.
B4X:
Dim n As Long = DateTime.Now
 For i = 0 To B4XTable1.VisibleRowIds.Size - 1
 
 Next
Log($"Time: ${DateTime.Now - n}ms"$)
Run it in release mode. What is the output?

If it is too slow then you should comment the BytesToImage line and check the timing again. This will help you understand where is the bottleneck.
 

Mahares

Well Known Member
Licensed User
First step is to measure it
It is not slow even in debug because we are only talking about a table with a total of 225 flags. But I am questioning this query to be done for every visible ID:
cursor1=Starter.SQL1.ExecQuery("SELECT FLAG FROM " & Starter.DBTableName & " WHERE ID =" & row.Get("ID"))
Is there a way to store the ID and Blob in a map when first run for all items, then use the map to extract the blob ( as a value) using the ID as the key or a list or something. This way I can just retrieve the bytes of the blob (map value) based on the ID ( map key)
in the B4XTable1_DataUpdated sub. If you think what I currently have is OK, I can live with that. It just does not look kosher.
 
Top