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

Jakes72 said:

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

Click to expand...

Jakes72 said:

Hi Imad,

Do the following:

1. Host your SQL Server Database on a remote server. If you do not know how to do this, get a company on the internet to help you.
You will need a Remote IP Address, Database name, Port, Username & Password to connect to the database.
Once the database is set up, you can then use Microsoft SQL Server Management Studio installed on your PC to manage it.

NOTE!!!
The following instructions pertain to B4A (Android) using a database driver.
I am not sure if there is an equivalent database driver for B4i, but I created a Web Service instead to access data for B4i projects (different topic).
I could only get this to work in Android Version 5 and above.


2. Copy the database driver file (jtds-1.3.1.jar) I attached here into your 'libraries' subfolder of your B4A install path. Not sure if there is a newer version of this driver, but this is the one I am using and it works fine.

3. Under 'Project Attributes' in project add this line of code:

B4X:

#AdditionalJar: jtds-1.3.1

4. In your Manifest editor you may need these 2 lines if it does not work (I need not need them however):
AddPermission (android.permission.INTERNET)
AddPermission (android.permission.ACCESS_NETWORK_STATE)

5. Under 'Sub Globals' add the following declarations:

B4X:

Private strConnString As String              'Database connection string
Private MsSQL As JdbcSQL                    'Reference to the Database driver object
Private ResultSet As JdbcResultSet        'The result set returned from a query.

6. Under the 'Libraries' tab in your project make sure to tick 'JdbcSQL', 'JavaObject' & 'RuntimePermissions'.

7. In the 'Activity_Create' sub add the following:

B4X:

Dim strPort as string = "Put your Port no here"
Dim strServerIP as string = "Put your Server IP Address here"
Dim strDatabaseName as string = "Put your database catalog name here"
Dim strUserName as string = "Put your username here"
Dim strPassword as string = "Put your password here"

DisableStrictMode

'Prepare the connection string.
strConnString = "jdbc:jtds:sqlserver://" & strServerIP & ":" & strPort & "/" & strDatabaseName

'Connect to the Database.
MsSQL.Initialize2("net.sourceforge.jtds.jdbc.Driver", strConnString, strUserName, strPassword)

If MsSQL.IsInitialized = True Then
     'Yay! I am successfully connected....
Else
     'There is a problem somewhere, I am not connected.
End If

8. Add the following sub to your project:

B4X:

Public Sub DisableStrictMode
 
    Try
 
        Dim jo As JavaObject
        jo.InitializeStatic("android.os.Build.VERSION")
        If jo.GetField("SDK_INT") > 9 Then
            Dim policy As JavaObject
            policy = policy.InitializeNewInstance("android.os.StrictMode.ThreadPolicy.Builder", Null)
            policy = policy.RunMethodJO("permitAll", Null).RunMethodJO("build", Null)
            Dim sm As JavaObject
            sm.InitializeStatic("android.os.StrictMode").RunMethod("setThreadPolicy", Array(policy))
        End If
 
    Catch
        Dim strStatus as string = LastException.Message
    End Try
 
End Sub

Good luck!

Regards
Jacques.

Click to expand...