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

Jakes72

Active 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.
 
Upvote 0

Jakes72

Active 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.
 
Upvote 0

Jakes72

Active 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.
 
Upvote 0

Jakes72

Active 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))
 
Upvote 0
Top