B4J Question B4J: JPG as BLOB from\to SQLite

[peacemaker]

Hi, All

Is it real to store 2 JPG images (rather big) in SQLite table ? At PC B4J only, RAM is not a problem.

Any code example to read\save ?

 

[Magma]

Hi, All

Is it real to store 2 JPG images (rather big) in SQLite table ? At PC B4J only, RAM is not a problem.

Any code example to read\save ?

Hi there....

well...
example updating a record blob:

Dim theimagefile As String = ChooseFile("Select Image...",File.DirApp,"Images/Pictures Format", _
                                               Array As String("*.jpg","*.png","*.bmp"))
               
Log(theimagefile)
Dim mybitmap As Image=xui.LoadBitmap(File.GetFileParent(theimagefile), File.GetName(theimagefile))
Dim mybitmap2() As Byte
mybitmap2=XUIImageToJPEGByteArray(mybitmap,300,0,80)
datadb.ExecNonQuery2("UPDATE " & SQLtable & " SET picture=?",Array(mybitmap2))


Sub XUIImageToJPEGByteArray(aImage As Object, width As Int, height As Int, quality As Int) As Byte()  'this is by OliverA
    'Sanity check quality
    If (quality < -1 And quality > 100) Then
        Log("Parameter quality not in range (-1..100)")
        Return Array As Byte ()
    End If
    If quality = -1 Then quality = 75

    'Sanity check incoming image
    Try
        Dim image As B4XBitmap = aImage
    Catch
        Log(LastException)
        Return Array As Byte ()
    End Try
 
    'Sanity check incoming image dimensions
    Dim oldWidth As Int = image.Width
    Dim oldHeight As Int = image.Height
    If (oldWidth = 0 And oldHeight = 0 ) Then
        Log("Source image with incorrect dimensions")
        Return Array As Byte ()
    End If

    'See if were resizing the image
    Dim  resize As Boolean = True
    If ((width = 0 And height = 0) Or (width = -100 Or height = -100)) Then resize = False
 
    'Resize if necessary
    If (resize) Then
        Dim newWidth As Int = width
        Dim newHeight As Int = height
        'Calculate new dimensions
        If (newWidth < 0) Then newWidth = -1 * oldWidth * newWidth / 100
        If (newHeight < 0) Then newHeight = -1 * oldHeight * newHeight / 100
        If (newWidth = 0) Then newWidth = oldWidth * newHeight / oldHeight
        If (newHeight = 0 ) Then newHeight = oldHeight * newWidth / oldWidth
        image = image.Resize(newWidth, newHeight, False)
    End If
 
    'Convert image to JPEG byte array
    Dim out As OutputStream
    out.InitializeToBytesArray(0)
    image.WriteToStream(out, quality, "PNG")
    out.Close
 
    'Done
    Return out.ToBytesArray
End Sub

example loading a blob:

                Dim Buffer() As Byte = rs.GetBlob("picture")
                ImageView1.SetImage(BytesToImage(Buffer))

Public Sub BytesToImage(bytes() As Byte) As B4XBitmap
    Dim In As InputStream
    In.InitializeFromBytesArray(bytes, 0, bytes.Length)
    Dim bmp As Image
    bmp.Initialize2(In)
    Return bmp
End Sub

But have in mind that is not the best "option" to save blobs into DATABASES.... at least big ones... better save the path or a part of path....

 

[peacemaker]

I have prepared this code for B4J (not for B4A), but not tested yet:

Public Sub ImageToBytes2(Image As B4XBitmap, Quality As Int) As Byte()
    Dim out As OutputStream
    out.InitializeToBytesArray(0)
    Image.WriteToStream(out, Quality, "JPEG")
    out.Close
    Return out.ToBytesArray
End Sub

Public Sub BytesToImage(bytes() As Byte) As B4XBitmap
    Dim In As InputStream
    In.InitializeFromBytesArray(bytes, 0, bytes.Length)
#if B4A or B4i
   Dim bmp As Bitmap
   bmp.Initialize2(In)
#else
    Dim bmp As Image
    bmp.Initialize2(In)
#end if
    Return bmp
End Sub

Sub LoadImageFromBlob(xSQL1 As SQL, xTable As String, xField As String, xID As Long) As B4XBitmap
    If xID <= 0 Or xTable.Trim = "" Or xField.Trim = "" Then Return Null
    Dim Bmp As B4XBitmap = Null
    Dim blobSize As Long = SQL.ExecQuerySingleResult2("SELECT IFNULL(LENGTH(" & xField & "), 0) FROM " & xTable & " WHERE id = ?", Array As String(xID))
    If blobSize > 0 Then
        Dim rs As ResultSet = xSQL1.ExecQuery2("SELECT " & xField & " FROM " & xTable & " WHERE id = ? LIMIT 1", Array As String(xID))
        If rs.NextRow Then
            Dim Buffer() As Byte = rs.GetBlob(xField)
            Bmp = BytesToImage(Buffer)
        End If
        rs.Close
    End If
    Return Bmp
End Sub

Sub SaveImageAsBLOB(bmp As Image, xSQL1 As SQL, xTable As String, xField As String, xID As Int) As Boolean
    Dim b() As Byte = ImageToBytes2(bmp, 80)
    Dim i As Int = xSQL1.ExecQuerySingleResult("SELECT count(*) FROM " & xTable & " WHERE id = '" & xID & "'")
    If i = 0 Then
        Dim s As String = "INSERT INTO " & xTable & " (" & xField & ") VALUES (?)"
        xSQL1.ExecNonQuery2(s, Array (b))
    Else
        Dim s As String = "UPDATE " & xTable & " SET " & xField & " = ? WHERE id = ?"
        xSQL1.ExecNonQuery2(s, Array (b, xID))
    End If
    Return True
End Sub

TESTED

 

[Magma]

at load/read... have "try catch" because sometimes is empty... the field with blob...

* The reason used OliverA sub, is because Oliver added the resizing option and is very helpful!