B4J Question Updating a BLOB with an Image using JBDC and MySql

[codie01]

Hi All,

I have written the following code to insert a record, works well a shown on this site and in my App.

    'Convert Image View to Byte Buffer
    Dim MyBuffer1() As Byte 'declares an empty array
    Dim OutputStream1 As OutputStream
    OutputStream1.InitializeToBytesArray(10000)
    s_image1.GetImage.WriteToStream(OutputStream1)
    MyBuffer1 = OutputStream1.ToBytesArray
    OutputStream1.Close
    ' Set Record to update
    'Dim MySearch As String
    'MySearch = s_sellcode.Text
    'Create Query Statement
    Dim strSQL As String = "INSERT INTO qualas_stock SET s_sellcode = '" & s_sellcode.Text & "',     s_shortdescription = 'AAA', s_longdescription = 'bbb', s_type = 'ccc', s_sunit = '1', s_unitdescription = 'eee', s_image1 = ?;"
    sql1.ExecNonQuery2(strSQL, Array As Object(MyBuffer1))

However I wish to use the UPDATE command with WHERE, any tips please. Also an example of reading the blob back to an imageviewer would be of great assistance.

Regards to Erl and All

 

[Erel]

Why are you adding s_sellcode.Text to the string instead of the array? Replace it with a question mark.

sql.ExecNonQuery2("UPDATE qualas_stock SET s_image=? WHERE s_sellcode = ?", Array (MyBuffer1, s_sellcode.Text))

 

[codie01]

Good Day Erel,

Thank you I can now see the BLOB in the Record. Now I am trying to reloaded from the Table as I change records in the Tableview. I have an imageview "ID"is s_image1 at the side but it is not updating. Can you please review my code thanks.

With this I am going to write a complete MYSQL - JDBC - B4J tutorial and Guide as it has taken a while to drag bits and pieces of information together.

Sub StockTableView_SelectedRowChanged(Index As Int, Row() As Object)
   'Log("TableView reading column 2 for Index:" & Index & CRLF & " Cell value: " & Row(1))
   MyIndex = StockTableView.SelectedRow
   If MyIndex > = 0 Then
      'Log(MyIndex)
    Dim Cursor As ResultSet
    Dim mySearch(24) As Object
    Dim myLookup As String
    mySearch = StockTableView.SelectedRowValues
    myLookup = mySearch(0)
    Log(myLookup)
    Cursor = sql1.ExecQuery("SELECT s_sellcode,  s_shortdescription, s_type, s_sunit, s_image1 FROM qualas_stock WHERE s_sellcode = " & "'" & myLookup & "'")
    Do While Cursor.NextRow
        Dim Buffer() As Byte 'declare an empty byte array
        Dim Row(24) As Object
        s_sellcode.text = Cursor.GetString("s_sellcode")
        s_shortdescription.text = Cursor.GetString("s_shortdescription")
        s_type.value = Cursor.GetString("s_type")
        s_sunit.value = Cursor.GetString("s_sunit")
        ' Read In Image From Blob
        Buffer = Cursor.GetBlob("s_image1")
        'get the Buffer into a Stream
        Dim ImageInStream As InputStream
        ImageInStream.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
        'ImageInStream holds the data.. need to get it into a bitmap
         s_image1.GetImage.Initialize2(ImageInStream)
        'close the stream
        ImageInStream.Close
    Loop
    Else
          MyIndex = StockTableView.SelectedRow
   End If
   stock_comboboxes
End Sub

 

[Erel]

You should never build the query string with concatenation. Use ExecQuery2 and put the parameter in an array.