Android Question [RESOLVED] Download image BLOB and insert into SQLite DB

[mfstuart]

Hi all,

I have a web service that delivers, in JSON format, records that contain integer and text values as well as a BLOB field from a SQL Server db. The SQL field data type is varbinary(MAX).
The BLOB contains a photo image, in the original JPEG format.

I've been reading the forum for this topic, but not finding exactly what I need to do, which is 2 things...
1) Web Service Call:
From the B4A app, call a web service that delivers back the JSON formatted data, parse the data, and write each record to a sqlite database. This all works well, but I'm not sure how to define the image variable (Picture) in the parsing code.
The Picture variable is defined as: Dim Picture as String.
The code updates the variable: Picture = colitems.Get("Picture").
Then a write to the database: SQL.ExecNonQuery(strSQL, Array as Object(ID, Picture))
This code works fine.
Using a SQLite Browser app on the tablet, I see the Picture field data without any seeible issue - a bunch of characters.

2) Set the layout ImageView object with the Picture field from SQLite DB:
I have a code module that I call from the Activity module to get explicitly the BLOB value from the record as:

'Activity call to get the image:
ivPicture.Bitmap = DBCalls.GetAnimalProfileImage

'the DBCalls code module handler to return the image:
Sub GetAnimalProfileImage As Bitmap
    Dim Buffer() As Byte
    Dim strSQL As String = "SELECT Picture FROM AnimalProfile WHERE AnimalID=" & Starter.AnimalID
    Dim cr As Cursor
 
    cr = SQL.ExecQuery(strSQL)
    cr.Position = 0
    If cr.RowCount > 0 Then
        Buffer = cr.GetBlob("Picture")
        Dim ipstrm As InputStream
        ipstrm.InitializeFromBytesArray(Buffer,0,Buffer.Length)
        Dim bmp As Bitmap
        bmp.Initialize2(ipstrm)      'fails here with: java.lang.RuntimeException: Error loading bitmap.
        ipstrm.Close
    End If
    cr.Close
    Return bmp
End Sub

But as you can see, the bmp.Initialize2(ipstrm) fails.

What is the best way to do all this?
BTW: we do not have WI-FI as an option during the web service call.

ADDED: What I mean is, the downloading (PostString) of the data happens when the tablet is connected to the network via a mini USB and RJ45 cable. The user will then enter data on the tablet, which is stored in the local SQLite database. When the user is finished, they reconnect the tablet and the upload transfers the new data to the SQL Server db.

Thanx,
Mark Stuart

 

[MarkusR]

set a break point (left beside the row numbers) and be sure the byte array contains the image file data.
data from a file will stay original in a byte array.
u can save the web api data to a file and see if u can open it at windows pc. or compare the checksum.
if u load this file data into a bitmap class its no longer a jpg or png.

 

[mfstuart]

Hi Markus,
I'm not sure how to save a file to the file system on a tablet, if that's what you mean.
That's definitely a good way to check if the download for the image is correct or not.
Also, I don't know how to do a checksum. I'm real new to this web service stuff. I've mostly been developing client/server applications, and now I find myself developing mobile apps.

"if u load this file data into a bitmap class its no longer a jpg or png."
Once the image is on the tablet, it is a read-only situation, the user will not change the original image. They can take a another picture of the animal and replace the existing one. Then the newly replaced photo image needs to be uploaded to the SQL Server db.

 

[MarkusR]

at a break point u can also see if the Buffer variable contains bytes.
b4a-bridge have a ftp checkbox, u can open the file explorer at pc from within ide,menu tools/b4a bridge/file explorer

    Dim o As OutputStream
Log(File.DirInternal)
    o = File.OpenOutput(File.DirInternal,"Test.dat",False)
    o.WriteBytes(....
    o.Close

 

[mfstuart]

Thank you Markus, I will give that a try.

 

[MarkusR]

Dim Picture as String.

thats wrong, binary data is a byte array u used here

Dim Buffer() As Byte

about json, i think the binary data is there in a string as base64 content. then u must change base 64 string into byte array.
https://www.b4x.com/android/help/stringutils.html

 

[mfstuart]

So that would be...

DecodeBase64 (Data As String) As Byte()
Decodes data from Base64 notation.

 

[mfstuart]

In the JobDone handler, the JSON result is passed to a code module handler that uses the JSONParser to read each element.
In debug mode, I see the Picture variable (type String) content and it looks like Base64 encoded (?), using the Picture = colitems.Get("Picture")
That content is then written to the SQLite db as is. It doesn't error out.

My whole issue is, is this the normal way to handle images coming from a web service?
Once I get this working, the next step is to upload a SQLite BLOB value (a new picture taken).
Another unknown frontier for me, wanting to know the correct way to do all this.

Thanx,
Mark Stuart

 

[MarkusR]

i read json did not support binary data so the workaround was to bring the binary into a string with base64 encode.
i would decode it and use a blob field in the sqllite database.
or u need to change your web service that it return a image stream as bytes. (i can post a example tomorrow from c# web api controller)

 

[mfstuart]

No need to do the example in C# Markus, as we use a completely different development tool to create our web services - Alpha 5.
It is very fast and easy to create a web service in Alpha 5 and in runtime, it delivers the JSON data to the tablet very fast.

I will continue trying to find out how all this works downloading BLOB data and rendering it on the tablet - and also the uploading of the BLOB data in JSON format.
Maybe I need to save each image to the tablet file system and then do an FTP upload? I don't know.

 

[Mahares]

That content is then written to the SQLite db as is. It doesn't error out.

Try to have a look at @petersimpson library with the link below. It may help solve your issue:
https://www.b4x.com/android/forum/threads/base64-encode-decode-image-library.31031/#content
You can encode and decode images. Perhaps you can save the text and then you can decode it to a bitmap that you display when you are viewing the SQLite table data.

 

[mfstuart]

Thank you Mahares. I will check it out.

 

[mfstuart]

I've finally gotten things working, for those who read this thread in the future.

This is what is being done: Download data as JSON from a web service.

From the tablet with a network connection, call a web service that delivers JSON data.
In this data is a Base64Encoded image from a SQL Server varbinary(MAX) field.
The tablet code uses the JSONParser to read the "text" data from the JSON result and updates the SQLite database with that data.
The SQLite field type is BLOB.

Libraries required: JSON, OkHttpUtils2, SQL, StringUtils

JSON data:
{
    "items": [
        {
            "AnimalName": "Aries",
            "Picture": "/9j/4AAQSkZJRgABAgAAAQABA continuation of image data... Fw/wCIlJpwxrU2+KmK4bHUpg0Ot4jdm+jzK9t6iiur4n//2Q==",
        }
    ]
}

'from an Activity module that calls the web service:
Sub btnDownload_Click
   ExecuteWS("AnimalProfile", URL)   'you will have to preset the URL value for your situation
End Sub

Sub ExecuteWS(QueryName as String, URL as String)
    ProgressDialogShow("Starting download...")
    Dim Job As HttpJob
    Job.Initialize(QueryName,Me)
    Job.PostString(URL,"")
End Sub

Sub JobDone(Job as HTTPJOB)
    If Job.Success Then
        Dim res as String = Job.GetString
        Select Job.Name
            Case "AnimalProfile"
                WriteAnimalProfile(res)

        End Select
    End If
    Job.Release
    ProgressDialogHide
End Sub

Sub WriteAnimalProfile(res as String)
   Dim strSQL as String = "INSERT INTO AnimalProfile (AnimalName, Picture) VALUES(?,?)"
   Dim AnimalName as String
   Dim Picture as String
   Dim parser as JSONParser
   parser.Initialize(res)
   Dim root as Map
   Dim items as List

   root.Initialize
   items.Intialize

   root = parser.NetxObject
   items = root.Get("items")

   SQL.BeginTransaction
   For Each colitems as Map in items
      AnimalName = colitems.Get("AnimalName")
      Picture = colitems.Get("Picture")

      SQL.ExecNonQuery2(strSQL, Array As Object(AnimalName, Picture))
   Next
   SQL.TransactionSuccessful
   SQL.EndTransaction
End Sub

'from an Activity module that will display the image BLOB from the SQLite database
Sub Globals
   Private lblAnimalName as Label
   Private ImageView1 as ImageView
End Sub

'you will need two controls/views on the layout: Label and ImageView
Sub Activity_Create(FirstTime As Boolean)
    Activity.LoadLayout("animal_profile_detail")  
End Sub

Sub Activity_Resume
   Dim AP() as String = GetAnimalProfile(1)
   Dim Picture as String
   Dim bmp As Bitmap
   lblAnimalName.Text = AP(0)
   Picture = AP(1)

   If Picture.Length > 4 Then      'check that we do really have an image
      bmp = GetAnimalProfileImage(Picture)

      If bmp <> Null Then
         ImageView1.SetBackgroundImage(bmp)
      End If
    
   End If
End Sub

Sub GetAnimalProfile(AnimalID as Int) as String()
   Dim sProfile() as String
   Dim cr as Cursor
   Dim strSQL as String = "SELECT AnimalName, Picture FROM AnimalProfile WHERE AnimalID=" & AnimalID

   Dim AnimalName as String
   Dim Picture as String

   cr = SQL.ExecQuery(strSQL)
   cr.Position = 0
   If cr.RowCount > 0 then
      AnimalName = cr.GetString("AnimalName")
      Picture = cr.GetString("Picture")
     
      sProfile = Array As String(AnimalName, Picture)
   Else
      sProfile = Null
   End If
   Return sProfile
End Sub

Sub GetAnimalProfileImage(s As String) As Bitmap
    Dim su As StringUtils
    Dim bytes() As Byte = su.DecodeBase64(s)
    Dim in As InputStream
    in.InitializeFromBytesArray(bytes, 0, bytes.Length)
    Dim bmp As Bitmap
    bmp.Initialize2(in)
    in.Close
    Return bmp
End Sub

I hope this helps someone that cannot find explicit examples of using SQL Server, web service, JSON, and BLOB image data.
Mark Stuart

 

[MarkusR]

u have everything, why do you not put the image file as it is (the byte array) into the sqlite blob field instead of the base64 string?

u can get the bytes from query by .GetBlob("PictureFieldName") then. instead of loading a image from filesystem load from inputstream

'convert a file content to a bitmap
Sub BytesToBitmap(Data() As Byte) As Bitmap
    
    Dim InputStream As InputStream
    InputStream.InitializeFromBytesArray(Data,0,Data.Length)
    
    Dim NewBitmap As Bitmap
    NewBitmap.Initialize2(InputStream)
    
    InputStream.Close
    
    Return NewBitmap
    
End Sub

'make a jpg as bytes
Public Sub BitmapToBytes(Bmp As Bitmap) As Byte()
    Dim Out As OutputStream
    Out.InitializeToBytesArray(0)
    Bmp.WriteToStream(Out, 100, "JPEG")
    Out.Close
    Return Out.ToBytesArray
End Sub

 

[mfstuart]

Hi Markus,
The image data was already Base64 encoded, so I had to decode it to make it appear in the ImageView.
I had already tried similar code as what you have referenced above, so that's what started my quest to make it work for me and try other ways.