Help on saving a blob field into DB appreciated

[albert1963]

I'm developing an App on Android for my comany, only internal use.
The database consists of several BLOB fields. The App saves taken pictures into the DB.

The function to write one of the taken pictures into the database is as follows:

Sub InsertIntake1FotoBlob

Log("> InsertIntake1FotoBlob, " & DateTime.Time(DateTime.Now) & "(" & DateTime.Now & ")")
   'convert the image file to a bytes array
   Dim InputStream1 As InputStream
   InputStream1 = File.OpenInput("/storage/sdcard0/TF_Worklist_Loadcells/", "IntakeFoto1.jpg")
   Dim OutputStream1 As OutputStream
   OutputStream1.InitializeToBytesArray(1000000)
   File.Copy2(InputStream1, OutputStream1)
   Dim Buffer() As Byte 'declares an empty array
   Buffer = OutputStream1.ToBytesArray
   'write the image to the database
   Dim MySqlquery As String
   MySqlquery="UPDATE " & DbMainTableName & _
              " SET " & MainTableColumnNames(48) & " = '?' " & _
            "WHERE " & MainTableColumnNames(0) & " = '" & txtMainTableColumnNames0.Text & _
            "' AND " & MainTableColumnNames(3) & " = '" & TxtProjectReferentie.Text & _
            "' AND " & MainTableColumnNames(7) & " = '" & TxtSerienummer.Text & "'"
   MySql.ExecNonQuery2(MySqlquery, Array As Object(Buffer))
Log("< InsertIntake1FotoBlob, " & DateTime.Time(DateTime.Now) & "(" & DateTime.Now & ")")

End Sub

When i debug this code the MySqlquery variable contains the following string:

"UPDATE DB_WorklistLoadcells SET IntakeControlPhoto1 = '?' WHERE OID = '1' AND ProjectReference = '212999' AND Serial = 'TFL-3175kN-129999'"

This seems pretty nice and correct to me.
When the call ExecNonQuery2 is executed I get the following error:

'LastException java.lang.IllegalArgumentException: Cannot bind argument at index 1 because the index is out of range. The statement has 0 paramters.'

So I'm missing something in MySqlquery variable. I don't see what, any help :sign0085: appreciated!

Best regards,
Albert

 

[albert1963]

Problem solved

:sign0060:

Well, it take some and reading of multiple tutorials, but my problem has been solved in the following manner:

         Dim MyStringUtilities As StringUtils
         Dim PictureBuffer As String = MyStringUtilities.EncodeBase64(Buffer)
         'MySql.ExecNonQuery2(MySqlquery, Array As Object(Buffer))
         MySqlquery = "UPDATE " & DbMainTableName & _
                      " SET " & MainTableColumnNames(FotoIndex) & " = '" & PictureBuffer & "' " & _
                    "WHERE " & MainTableColumnNames(0) & " = '" & txtMainTableColumnNames0.Text & _
                    "' AND " & MainTableColumnNames(3) & " = '" & TxtProjectReferentie.Text & _
                    "' AND " & MainTableColumnNames(7) & " = '" & TxtSerienummer.Text & "'"
         MySql.ExecNonQuery(MySqlquery)

Keep in mind that the BLOB field when read has to be decoded again and read in as string field, not blob field, like:

Dim MyStringUtilities As StringUtils
....
....         
MyPictureArray = MyStringUtilities.DecodeBase64(MyMainTableCursor.GetString(MainTableColumnNames(48)))

I hope anyone encountering the same problem has some benefit from this solution.

Greetings,
Albert

 

[jafuve]

do you know how to insert an image selected from galery to the blob field of a sqlite database??

 

[Erel]

Please start a new thread for this question.