Android Question Save Image in local 'DirAssets' to Remote MS SQL Database using JdbcSQL

Jakes72

Member
Licensed User
Hi Guys,

Please could someone help me out. I am trying to save a local image into my remote SQL Server database.
I want to save the image to a record that already exists to a column of type varbinary(MAX). Not sure if this is the right type?

Also I give the sub the Tablename, the ColumnName where I want to save the image to, the Record ID of the record I want to use, and the Record ID's column name. First I convert the image to a byte array, but I do not know how to construct the SQL.

I get an error on the MsSQL_.ExecNonQuery line, it says:
'Object converted to string. This is probably programming mistake'. How can I fix this?

Thank you in advance.

Here is my code so far:

B4X:
Public Sub ExecSaveFileAsBinary(strTableName As String, strColumnName As String, lngRecID As Long, strRecIDColumnName As String, strFilePathName As String)
   
    'convert the image file to a bytes array
    Dim InputStream1 As InputStream
    InputStream1 = File.OpenInput(File.DirAssets, strFilePathName)
 
    Dim OutputStream1 As OutputStream
    OutputStream1.InitializeToBytesArray(1000)
 
    File.Copy2(InputStream1, OutputStream1)
    Dim buffer() As Byte 'declares an empty array
    buffer = OutputStream1.ToBytesArray
 

    MsSQL_.ExecNonQuery("UPDATE " & strTableName & " SET " & strColumnName & " = " & Array As Object(Buffer) & " WHERE " & strRecIDColumnName & " = " & lngRecID)

 
End Sub
 

Erel

Administrator
Staff member
Licensed User
B4X:
 Dim InputStream1 As InputStream
    InputStream1 = File.OpenInput(File.DirAssets, strFilePathName)
 
    Dim OutputStream1 As OutputStream
    OutputStream1.InitializeToBytesArray(1000)
 
    File.Copy2(InputStream1, OutputStream1)
    Dim buffer() As Byte 'declares an empty array
    buffer = OutputStream1.ToBytesArray
Equivalent to:
B4X:
Dim buffer() As Byte = File.ReadBytes(File.DirAssets, strFilePathName)
What is MsSQL? You should use JdbcSQL library for direct connections. Make sure not to use synchronous methods with remote databases.
 

Jakes72

Member
Licensed User
Hi Erel thanks for the reply.
MS SQL is the database type I am using (Microsoft SQL Server).
I am working with the JdbcSQL library.

I mainly need help with my SQL I do not know how to write that, see my above question.

Thank you any help would be greatly appreciated.
 

Jakes72

Member
Licensed User
Hi Erel, sorry maybe I cam not explaining myself clearly, let me try again.
All I really need help with is too modify the SQL statement so that I can UPDATE (not INSERT) an existing record.

Pseudo code of what I am trying to do:

UPDATE MyTableName SET MyColumnName = ImageByteArrayData WHERE RecordID = xxx

The only example I have is this:
SQL1.ExecNonQuery2("INSERT INTO table1 VALUES (?, ?, 0)", Array As Object("some text", 2))

How can I modify this SQL statement to achieve my pseudo code above?

Hope this makes sense.
Thank you for your time.
 

Jakes72

Member
Licensed User
EUREKA!!!!!
Never mind Sir I figured it out!
Thanks for the help.

This is what I was after (I didn't understand how your question marks were working at first as parameters :) )

B4X:
MsSQL_.ExecNonQuery2("UPDATE " & strTableName & " SET " & strColumnName & " = ? WHERE " & strRecIDColumnName & " = " & lngRecID, Array As Object(buffer))
 
Top