SQL BLOB field size Limit in Basic4android

J

johan vetsuypens

Member
Licensed User
Longtime User
I'm using an Sqlite DB with a BLOB field. In this field I store large PDF manuals. It seems that a maximum of 2 Mb size PDF's can be retrieved from the DB on my Android device. Using the function blob = Cursor.GetBlob(cFieldName) will result in an error when getting more than 2mb bytes : java.lang.IllegalStateException: Couldn't read row 0, col 0 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it.

Retrieving blob data with size of less than 2 Mb is working fine. Can this be a device memory limit issue ? Or is it possible to increase this size ?

Thanks,
Johan
 
Sort by date Sort by votes
stevel05

stevel05

Expert
Licensed User
Longtime User
Have a look at this on Stack OverFlow, it talks about a limit on the cursor size and how to get around it although it mentions 1MB, which may have changed, or not be applicable at all , but worth a try.
 
Upvote 0
stevel05

stevel05

Expert
Licensed User
Longtime User
Great, can you confirm that it does actually get round the problem once you've had a chance to test it.
 
Upvote 0
W

Wembly

Member
Licensed User
Longtime User
Thanks for this info, but can anyone please advise how to append the chunks of byte data together as the Stack OverFlow method suggests.

Sample of the approach I was trying but got stuck:

B4X: 
    Dim blobSize As Long
    Dim tempBuffer() As Byte
    Dim startblock, endblock As Long
    startblock = 1
  
    blobSize = Main.SQL1.ExecQuerySingleResult2("SELECT IFNULL(LENGTH(field),0) FROM table " & _
                                                "WHERE rowid = ?", Array As String(itemID))
  
    If blobSize > 0 Then
        For i = 1 To blobSize Step 1000000
        endblock = i
        DbCursor = Main.SQL1.ExecQuery2("SELECT SUBSTR(field,?,?) FROM table " & _
                                                "WHERE rowid = ?", Array As String(startblock, endblock, itemID))
                                              
        tempBuffer = DbCursor.GetBlob("field")
      
        buffer = buffer + tempBuffer   ' This is where I'm stuck - I accept this is incorrect.
      
        startblock = i +1
        Next
      
    End If

Thanks in advance.
 
Upvote 0
stefanoa

stefanoa

Active Member
Licensed User
Longtime User
i'm trying with this code but i have an error INDEXOUTOFBOUNDSEXCEPTION on row "out.WriteBytes(tempBuffer, startblock, startblock+lenghtblock-1)"
I don't know well WriteBytes sintax... where is the error???
In this example i have an image of 1380000 bytes and the error has displayed on 1' step (from 1 to 1000000).

B4X: 
    Dim imgCursor As Cursor
    Dim newBuffer() As Byte=Null
    Dim tempBuffer() As Byte=Null
    Dim startblock, lenghtblock, position As Long
    startblock = 1
 
    Dim out As OutputStream
    out.InitializeToBytesArray(0)
    Dim blobSize = SQL1.ExecQuerySingleResult2("SELECT IFNULL(LENGTH(events.image),0) FROM events " & _
                                    "WHERE _ID = ?", Array As String(globalEventsID))
 
    If blobSize > 0 Then
        lenghtblock = 1000000
        Do While True
            If (startblock+lenghtblock) > blobSize Then
                lenghtblock=blobSize
            End If
         
            imgCursor = SQL1.ExecQuery2("SELECT SUBSTR(events.image,?,?) as image FROM events " & _
                                    "WHERE _ID = ?", Array As String(startblock, lenghtblock, globalEventsID))
            imgCursor.position = 0
            tempBuffer = imgCursor.GetBlob("image")
            out.WriteBytes(tempBuffer, startblock,  lenghtblock)   '<<<<< INDEXOUTOFBOUNDSEXCEPTION 
         
            If lenghtblock=blobSize Then
                Exit
            End If
            startblock = startblock+lenghtblock

        Loop
    End If         
    imgCursor.close
 
    Dim dataBuffer() As Byte
    dataBuffer = out.ToBytesArray

Please Help! thanks
 
Upvote 0
stefanoa

stefanoa

Active Member
Licensed User
Longtime User
after many hours of work, this is my solution for load a large BLOB field (ex. image > 1MB) into a BMP, using a temp file, hoping that it will be of help to someone..

Load Blob image in block of 1MB and return a BMP field.
Parameters are: SQL field (ex. Dim SQL1 As SQL), Table name, Field name, record ID field

call this function with:
Dim BMP As Bitmap = loadImageFromBlob(SQL1,"tablename","fieldname",ID)

B4X: 
Sub loadImageFromBlob(xSQL1 As SQL, xTable As String, xField As String, xTableID As Long) As Bitmap
    Dim Bmp As Bitmap
    If xTableID=0 OR xTable.Trim="" OR xField.Trim="" Then Return
   
    Dim imgCursor As Cursor
    Dim tempBuffer() As Byte=Null
    Dim startblock, lenghtblock As Long
    Dim exitLoop As Boolean = False
    startblock = 0
   
    Dim blobSize = SQL1.ExecQuerySingleResult2("SELECT IFNULL(LENGTH(" & xField & "),0) FROM " & xTable &  _
                                    " WHERE _ID = ?", Array As String(xTableID))
   
    If blobSize > 0 Then
        lenghtblock = 1000000
   
        '--- Create SubFold per temp images ----------------------------
        Dim TargetDir As String=File.DirInternal, TargetFolder As String="images"
        If File.ExternalWritable Then TargetDir = File.DirDefaultExternal
        If File.Exists(TargetDir, TargetFolder) = False Then File.MakeDir(TargetDir, TargetFolder)    '<-- create folder if not exists
        Dim out As OutputStream = File.OpenOutput(TargetDir & "/" & TargetFolder, "temp.jpg", False)
        '---------------------------------------------------------------
       
        Do While True
            If (startblock+lenghtblock) > blobSize Then
                lenghtblock=blobSize-startblock+1
                exitLoop=True
            Else
                exitLoop=False
            End If
            imgCursor = SQL1.ExecQuery2("SELECT SUBSTR(" & xField & ",?,?) as image FROM " & xTable  & _
                                    " WHERE _ID = ?", Array As String(startblock, lenghtblock, xTableID))
            imgCursor.position = 0
            tempBuffer = imgCursor.GetBlob("image")
            out.WriteBytes(tempBuffer, 0,  tempBuffer.Length ) 'lenghtblock)   
            If exitLoop = True Then Exit

            startblock = startblock+lenghtblock 
        Loop
        imgCursor.close
        out.Close    
    End If           

    Dim InputStream1 As InputStream
    InputStream1 = File.OpenInput(TargetDir & "/" & TargetFolder, "temp.jpg")       
    Dim OutputStream1 As OutputStream
    OutputStream1.InitializeToBytesArray(0)  '1000)
    File.Copy2(InputStream1, OutputStream1)
    Dim dataBuffer() As Byte 'declares an empty array
    dataBuffer = OutputStream1.ToBytesArray
    OutputStream1.Close
   
    If dataBuffer <> Null Then   
        Dim InputStream1 As InputStream
        InputStream1.InitializeFromBytesArray(dataBuffer, 0, dataBuffer.Length)    '<-- copy outpustream in InputStream
        Bmp.Initialize2(InputStream1)            '<-- trasform in BMP
        InputStream1.Close
    End If
   
    Return Bmp
   
End Sub
 
  • Like
Reactions: JCO
Upvote 0
peacemaker

peacemaker

Expert
Licensed User
Longtime User
Do you have java.lang.OutOfMemoryError ?

I edited the sub:
B4X: 
Sub loadImageFromBlob(xSQL1 As SQL, xTable As String, xField As String, xTableID As Long) As Bitmap
    Dim Bmp As Bitmap
    If xTableID=0 OR xTable.Trim="" OR xField.Trim="" Then Return
 
    Dim imgCursor As Cursor
    Dim tempBuffer() As Byte=Null
    Dim startblock, lenghtblock As Long
    Dim exitLoop As Boolean = False
    startblock = 0
 
    Dim blobSize As Long = others.SQL.ExecQuerySingleResult2("SELECT IFNULL(LENGTH(" & xField & "),0) FROM " & xTable &  _
                                    " WHERE id = ?", Array As String(xTableID))
 
    If blobSize > 0 Then
        lenghtblock = 1000000
 
        '--- Create SubFold per temp images ----------------------------
        Dim TargetDir As String=File.DirInternal, TargetFolder As String="images"
        If File.ExternalWritable Then TargetDir = File.DirDefaultExternal
        If File.Exists(TargetDir, TargetFolder) = False Then File.MakeDir(TargetDir, TargetFolder)    '<-- create folder if not exists
        Dim out As OutputStream = File.OpenOutput(TargetDir & "/" & TargetFolder, "temp.jpg", False)
        '---------------------------------------------------------------
     
        Do While True
            If (startblock+lenghtblock) > blobSize Then
                lenghtblock=blobSize-startblock+1
                exitLoop=True
            Else
                exitLoop=False
            End If
            imgCursor = others.SQL.ExecQuery2("SELECT SUBSTR(" & xField & ",?,?) as image FROM " & xTable  & _
                                    " WHERE id = ?", Array As String(startblock, lenghtblock, xTableID))
            imgCursor.position = 0
            tempBuffer = imgCursor.GetBlob("image")
            out.WriteBytes(tempBuffer, 0,  tempBuffer.Length ) 'lenghtblock) 
            If exitLoop = True Then Exit

            startblock = startblock+lenghtblock
        Loop
        imgCursor.close
        out.Close   
             

        Dim InputStream1 As InputStream
        InputStream1 = File.OpenInput(TargetDir & "/" & TargetFolder, "temp.jpg")     
        Dim OutputStream1 As OutputStream
        OutputStream1.InitializeToBytesArray(0)  '1000)
        File.Copy2(InputStream1, OutputStream1)
        Dim dataBuffer() As Byte 'declares an empty array
        dataBuffer = OutputStream1.ToBytesArray
        OutputStream1.Close
     
        If dataBuffer <> Null Then 
            Dim InputStream1 As InputStream
            InputStream1.InitializeFromBytesArray(dataBuffer, 0, dataBuffer.Length)    '<-- copy outpustream in InputStream
            Bmp.Initialize2(InputStream1)            '<-- trasform in BMP
            InputStream1.Close
            Dim dataBuffer() As Byte = Null    'free buffer
        End If
          File.Delete(TargetDir & "/" & TargetFolder, "temp.jpg")
        Return Bmp
    Else
        Return Null
    End If
 
End Sub

But anyway sometimes Out of memory :-( on 512 MB device.
 
Upvote 0
peacemaker

peacemaker

Expert
Licensed User
Longtime User
:)

B4X: 
Sub loadImageFromBlob(xSQL1 As SQL, xTable As String, xField As String, xTableID As Long) As Bitmap
    Dim Bmp As Bitmap
    If xTableID=0 OR xTable.Trim="" OR xField.Trim="" Then Return
 
    Dim imgCursor As Cursor
    Dim tempBuffer() As Byte=Null
    Dim startblock, lenghtblock As Long
    Dim exitLoop As Boolean = False
    startblock = 0
 
    Dim blobSize As Long = others.SQL.ExecQuerySingleResult2("SELECT IFNULL(LENGTH(" & xField & "),0) FROM " & xTable &  _
                                    " WHERE id = ?", Array As String(xTableID))
 
    If blobSize > 0 Then
        lenghtblock = 1000000
 
        '--- Create SubFold per temp images ----------------------------
        Dim TargetDir As String=File.DirInternal, TargetFolder As String="images"
        If File.ExternalWritable Then TargetDir = others.Folder
        If File.Exists(TargetDir, TargetFolder) = False Then File.MakeDir(TargetDir, TargetFolder)    '<-- create folder if not exists
        Dim out As OutputStream = File.OpenOutput(TargetDir & "/" & TargetFolder, "temp.jpg", False)
        '---------------------------------------------------------------
     
        Do While True
            If (startblock+lenghtblock) > blobSize Then
                lenghtblock=blobSize-startblock+1
                exitLoop=True
            Else
                exitLoop=False
            End If
            imgCursor = others.SQL.ExecQuery2("SELECT SUBSTR(" & xField & ",?,?) as image FROM " & xTable  & _
                                    " WHERE id = ?", Array As String(startblock, lenghtblock, xTableID))
            imgCursor.position = 0
            tempBuffer = imgCursor.GetBlob("image")
            out.WriteBytes(tempBuffer, 0,  tempBuffer.Length ) 'lenghtblock) 
            If exitLoop = True Then Exit

            startblock = startblock+lenghtblock
        Loop
        imgCursor.close
        out.Close   
             
        Bmp = LoadBitmap(TargetDir & "/" & TargetFolder, "temp.jpg")
          File.Delete(TargetDir & "/" & TargetFolder, "temp.jpg")
        Return Bmp
    Else
        Return Null
    End If
 
End Sub
 
  • Like
Reactions: JCO
Upvote 0
You must log in or register to reply here.
Top