Android Question Display BLOB from SQLite table

[Declan]

I have an app that reads a table from a remote MySQL database.
The MySQL database table has a BLOB field that has an image.

My app creates a SQLite table:

'Create the "mail" table which will holds mail images.
Dim m As Map
m.Initialize 'clear the map
m.Put("Id", DBUtils.DB_INTEGER)
m.Put("description", DBUtils.DB_TEXT)
m.Put("image", DBUtils.DB_BLOB)
m.Put("read", DBUtils.DB_TEXT)
DBUtils.CreateTable(SQL, "mail", m, "") '"Id" is primary key

I then read the remote table and insert the fields into my local SQLite table:

'Populate local SQLite table "mail"       
        If Job.tag = "mail" Then
            Dim result As DBResult = reqManager.HandleJob(Job)
            ProgressDialogHide
            reqManager.PrintTable(result)
            Dim ListOfMaps As List
                ListOfMaps.Initialize
            For Each row() As Object In result.Rows
                ListOfMaps.Add(CreateMap("description": row(1), "image": row(2), "read": row(3)))
                DBUtils.InsertMaps(SQL, "mail", ListOfMaps)
            Next
            PopulateMailList
        End If

I then populate a ListView with the "description" field:

Sub PopulateMailList
    Dim cur As Cursor
    Dim description As String
    Dim strQuery As String

    strQuery = "SELECT description FROM mail"' WHERE type = 'table'"
    SQL.BeginTransaction
    Try
        cur = SQL.ExecQuery(strQuery)
        For i = 0 To cur.RowCount - 1
            cur.Position = i
            description = cur.GetString("description")
            lstMailMain.AddSingleLine(description)
        Next
        SQL.TransactionSuccessful
    Catch
        Msgbox(LastException.Message, "Fill ListView Error")
    End Try
    SQL.EndTransaction
End Sub

When the user selects the item on the ListView, I load the image from the SQLite table and display within an ImageView:

Sub lstMailMain_ItemClick (Position As Int, Value As Object)
    Dim myImage As String
    myImage = Value
    Msgbox(myImage,"value")
    DisplayBlob(myImage)
End Sub

Sub DisplayBlob(Value As String)
    Dim Cursor1 As Cursor
    'Using ExecQuery2 is safer as it escapes special characters automatically.
    'In this case it doesn't really matter.
    Cursor1 = SQL.ExecQuery2("SELECT image FROM mail WHERE description = ?", Array As String("image"))
    Cursor1.Position = 0
    Dim Buffer() As Byte 'declare an empty byte array
    Buffer = Cursor1.GetBlob("image")
   
    Dim InputStream1 As InputStream
    InputStream1.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
  
    Dim Bitmap1 As Bitmap
    Bitmap1.Initialize2(InputStream1)
    InputStream1.Close
    imgMailBlob.SetBackgroundImage(Bitmap1)
End Sub

But, I get the following error:

HandleJob: 3
Tag: mail, Columns: 4, Rows: 1
id    description    image    read   
1    Special    [B@169ec9f7    null   
InsertMaps (first query out of 1): INSERT INTO [mail] ([description], [image], [read]) VALUES (?, ?, ?)
main_readmailblob (B4A line: 348)
Cursor1 = SQL.ExecQuery2("SELECT image FROM ma
android.database.sqlite.SQLiteException: no such column: Special (code 1): , while compiling: SELECT image FROM mail WHERE description = Special
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:898)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:509)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1346)
    at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1285)
    at anywheresoftware.b4a.sql.SQL.ExecQuery2(SQL.java:167)
    at b4a.example.main._readmailblob(main.java:2001)
    at b4a.example.main._lstmailmain_itemclick(main.java:1850)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:187)
    at anywheresoftware.b4a.BA$2.run(BA.java:299)
    at android.os.Handler.handleCallback(Handler.java:815)
    at android.os.Handler.dispatchMessage(Handler.java:104)
    at android.os.Looper.loop(Looper.java:194)
    at android.app.ActivityThread.main(ActivityThread.java:5624)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:959)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:754)

 

[Erel]

Are you sure that you executed this exact code when you got the error message?

It looks like you executed a different query (description = special).

 

[Declan]

Yes, the field "description" contains the text "special".
After further testing, I have established the following:

This works:

Cursor1 = SQL.ExecQuery("SELECT image FROM mail")

This does not:

Cursor1 = SQL.ExecQuery2("SELECT image FROM mail WHERE description = ?", Array As String("image"))

 

[Erel]

Yes, the field "description" contains the text "special".

You should check it again. The code you posted will not raise this error.

Anyway, you are not using the parameterized query correctly. If you want to get the record where description = "Special" then it should be:

Cursor1 = SQL.ExecQuery2("SELECT image FROM mail WHERE description = ?", Array As String("Special"))

 

[Declan]

Thanks, that works

 

[Declan]

I am now able to load an image, but I can only load one image and not the other.
Both images are GIF files.
The file "Special.GIF" load fine.
The file "Tequila.GIF" generates an error:

HandleJob: 9
Tag: mail, Columns: 4, Rows: 2
id    description    image    read   
4    Special    [B@2fe90c6a    0   
5    Tequila    [B@2bfec55b    null   
InsertMaps (first query out of 1): INSERT INTO [mail] ([description], [image], [read]) VALUES (?, ?, ?)
InsertMaps (first query out of 2): INSERT INTO [mail] ([description], [image], [read]) VALUES (?, ?, ?)
main_displayblob (B4A line: 357)
Bitmap1.Initialize2(InputStream1)
java.lang.RuntimeException: Error loading bitmap.
    at anywheresoftware.b4a.objects.drawable.CanvasWrapper$BitmapWrapper.Initialize2(CanvasWrapper.java:521)
    at b4a.example.main._displayblob(main.java:1409)
    at b4a.example.main._lstmailmain_itemclick(main.java:1905)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:187)
    at anywheresoftware.b4a.BA$2.run(BA.java:299)
    at android.os.Handler.handleCallback(Handler.java:815)
    at android.os.Handler.dispatchMessage(Handler.java:104)
    at android.os.Looper.loop(Looper.java:194)
    at android.app.ActivityThread.main(ActivityThread.java:5624)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:959)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:754)

My Code:

Sub DisplayBlob(Value As String)
    Dim Cursor1 As Cursor
    Cursor1 = SQL.ExecQuery2("SELECT image FROM mail WHERE description = ?", Array As String(Value))
    Cursor1.Position = 0
    Dim Buffer() As Byte 'declare an empty byte array
    Buffer = Cursor1.GetBlob("image")
    Dim InputStream1 As InputStream
    InputStream1.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
    Dim Bitmap1 As Bitmap
    Bitmap1.Initialize2(InputStream1)
    InputStream1.Close
    imgMailBlob.SetBackgroundImage(Bitmap1)
    imgMailBlob.Visible=True
End Sub

I have attached both image files as reference.
I hope it has nothing to do with the "Tequila" .....

 

[Erel]

Your code looks correct (except of missing to close the cursor).

The image itself is probably corrupted.