Android Question MSSQL BLOB 2 Imageview

[thegremlin]

Hy,
I try to read a Picture from MSSQL Database and Display it to a Imageview.
This code crash the app.

If Bildcode.Text.Length > 0 Then
Dim RS As SD_ResultSet = MSSQL.ExecQuery("SELECT bBild FROM dbo.tBild WHERE kBild='" & Bildcode.Text & "'")
Dim Buffer() As Byte =RS.GetBytes(0)
Dim InputStream1 As InputStream
Do While RS.NextRow
InputStream1.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
Dim Bitmap1 As B4XBitmap = Artikelbild.bitmap
Dim Bitmap1 As Bitmap
Bitmap1.Initialize2(InputStream1)
InputStream1.Close
Artikelbild.SetBackgroundImage(Bitmap1)
Loop
End If

But I also found a Thread with Base64 encode, but it produces the same error ?

'Convert without Dir / File as request #8
Dim bitmap1 As B4XBitmap = ImageView1.Bitmap
Dim base As String = Base64EncodeDecodeImage.Base64ImageToString2(bitmap1)
ImageView2.Bitmap = Base64EncodeDecodeImage.Ba

Error occured on line: 103 (SD-ResultSet)

it looks like in the library, so a error from me...

Anyone can help me ?

Thanks

Steven

 

[Erel]

Please use [code]code here...[/code] tags when posting code.

 

[ilan]

an example project would help a lot to understand your issue.
what is "SD_ResultSet" ?
is this a third party library?

 

[thegremlin]

Hy,
I use the Lib [B4X] [B4XLib] SD_SQL (direct access to MySQL, MariaDB, MS SQL, FireBird)

I try to read a Picture from MSSQL Database and Display it to a Imageview.
But this code crash the app.

If Bildcode.Text.Length > 0 Then
Dim RS As SD_ResultSet = MSSQL.ExecQuery("SELECT bBild FROM dbo.tBild WHERE kBild='" & Bildcode.Text & "'")
Dim Buffer() As Byte =RS.GetBytes(0)
Dim InputStream1 As InputStream
Do While RS.NextRow
InputStream1.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
Dim Bitmap1 As B4XBitmap = Artikelbild.bitmap
Dim Bitmap1 As Bitmap
Bitmap1.Initialize2(InputStream1)
InputStream1.Close
Artikelbild.SetBackgroundImage(Bitmap1)
Loop
End If

I had seen at other Thread, that a Blob can´t be directly set to Imagecontainer.
These should work with a base64decode, so I tried this...
[B4X] Library Base64 encode/decode Image and File library

'Convert without Dir / File as request #8
Dim bitmap1 As B4XBitmap = ImageView1.Bitmap
Dim base As String = Base64EncodeDecodeImage.Base64ImageToString2(bitmap1)
Artikelbild.Bitmap = Base64EncodeDecodeImage.Base64StringToImage(base)

The code I use instead of the Line

Artikelbild.SetBackgroundImage(Bitmap1)

I get a error message all the time, Maybe anyone can help me, how to read a BLOB to a Imagecontainer.

Thanks

Steven

 

[ilan]

from your code Bitmap1 is set twice.

Dim Bitmap1 As B4XBitmap = Artikelbild.bitmap
Dim Bitmap1 As Bitmap

this could be the problem

 

[thegremlin]

Hy,
thanks. My error, I had tried different options. It must be:

If Bildcode.Text.Length > 0 Then
Dim RS As SD_ResultSet = MSSQL.ExecQuery("SELECT bBild FROM dbo.tBild WHERE kBild='" & Bildcode.Text & "'")
Dim Buffer() As Byte =RS.GetBytes(0)
Dim InputStream1 As InputStream
Do While RS.NextRow
InputStream1.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
'Dim Bitmap1 As B4XBitmap = Artikelbild.bitmap
Dim Bitmap1 As Bitmap
Bitmap1.Initialize2(InputStream1)
InputStream1.Close
Artikelbild.SetBackgroundImage(Bitmap1)
Loop
End If

But error still there...

 

[zed]

the column name is invalid.
Column name 0

Do you have a column named 0 in the db?

 

[thegremlin]

Hy,
thanks. I had also tried :

Dim Buffer() As Byte = RS.GetBytes("kBild")

kBild are the Column name
but same error.

 

[ilan]

Upload an example project where the error can be reproduced

 

[ilan]

Try also to use sql library without sd lib to see if you get the same error

 

[thegremlin]

Voila, I used a sample project to test the functions.

If Bildcode.Text.Length > 0 Then
Dim RS As SD_ResultSet = MSSQL.ExecQuery("SELECT bBild FROM dbo.tBild WHERE kBild='" & Bildcode.Text & "'")
Dim Buffer() As Byte =RS.GetBytes(0)   <---- ERROR ARE HERE
Dim InputStream1 As InputStream
Do While RS.NextRow
InputStream1.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
'Dim Bitmap1 As B4XBitmap = Artikelbild.bitmap
Dim Bitmap1 As Bitmap
Bitmap1.Initialize2(InputStream1)
InputStream1.Close
Artikelbild.SetBackgroundImage(Bitmap1)
Loop
End If

My full code as attachment. But I think you can reproduce without database connection...
All other SQL querys are working. Only the blob field generate the error message.

 

[thegremlin]

When I save the BLOB, it looks like this

 

[zed]

Here is an example that works with sqlite.

Private Sub insertDB
    Dim q As String
    Dim InputStream1 As InputStream
    InputStream1 = File.OpenInput(File.DirAssets, "as.png")
    Dim OutputStream1 As OutputStream
    OutputStream1.InitializeToBytesArray(1000)
    File.Copy2(InputStream1, OutputStream1)
    Dim Buffer() As Byte =    OutputStream1.ToBytesArray
  
    Try
        q="INSERT INTO test VALUES(?)"
        SQL1.ExecNonQuery2(q, Array As Object(Buffer))
    Catch
        Log(LastException)
    End Try 
      
    loadImg 
 
End Sub

Private Sub loadImg
  
    Dim RS As ResultSet = SQL1.ExecQuery("SELECT * FROM test")
    RS.NextRow

    Dim Buffer() As Byte  = RS.GetBlob("myblob")
    Dim InputStream1 As InputStream
    InputStream1.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
    Dim Bmp As Bitmap
    Bmp.Initialize2(InputStream1)
    InputStream1.Close
    ImageView1.Bitmap = Bmp
End Sub

How was the Blob inserted into the database.
The problem may come from there.

If the blob is badly saved, when you want to recover it, the resultset will be 0

 

[thegremlin]

Hello,
the problem seems to be related to just this one line:

Dim Buffer() As Byte =RS.GetBytes(0)

The Database are MSSQL 2019 which is generated from JTL (merchandise management system).

I had found there at the forum these PHP code to show a Image from the database (blob) output:

echo '<img src="data:image/jpg;base64,'.base64_encode($row['bVorschauBild']).'" title="" alt="" />';

So maybe I need to encode the output, but my problem are before this step.

I can`t read the value out of the database...