SQL BLOB field size Limit in Basic4android

[johan vetsuypens]

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

 

[stevel05]

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.

 

[johan vetsuypens]

Thanks. the tip at Stack OverFlow will do the job. Just need to create an intelligent loop that works with all lengths of blob fields.

 

[stevel05]

Great, can you confirm that it does actually get round the problem once you've had a chance to test it.

 

[Wembly]

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:

    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.

 

[Erel]

You can use:

Dim out As OutputStream
out.InitializeToBytesArray(0)

Then write the chunks with out.WriteBytes.

 

[stefanoa]

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).

    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

 

[Erel]

Your code is wrong. You are trying to write more bytes than are available in the buffer.

 

[stefanoa]

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)

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

 

[peacemaker]

Do you have java.lang.OutOfMemoryError ?

I edited the sub:

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.

 

[peacemaker]

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