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

[Jakes72]

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:

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]

 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:

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]

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.

 

[Erel]

Watch the SQL video tutorial and switch to ExecNonQuery2.

https://www.b4x.com/etp.html

 

[Jakes72]

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]

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 )

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

 

[Imad71]

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:

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

Hello
Could please inform how you did the setup of remote MS SQL server Connectivity?

 

[Jakes72]

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:

#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:

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:

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:

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.

 

[Imad71]

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:

#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:

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:

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:

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.

Many thanks Jakes,
I was stacked with "strConnString"..
highly appreciated....