Android Question [Solved] JdbcSQL and SQL Server

[makis_best]

Hi

I read allot inside the forum about how to connect jdbcSQL with Sql server but I can't understand it.
All info are split in several post confusing very much specially newbies like me.

There are no complete tutorial - example saying from start to end how to do it?

Thank you.

 

[DonManfred]

https://www.b4x.com/android/forum/threads/jdbcsql-directly-connect-to-remote-databases.84016/
1. You just need to adapt the connectionline and driver

    Private driver As String = "com.mysql.jdbc.Driver"
    Private jdbcUrl As String = "jdbc:mysql://192.168.0.6/test"

2. The SQL Server jdbc driver is linked.
3. Google can help you finding the right syntax for the jdbcurl line.
4. The forumsearch IS working: https://www.b4x.com/android/forum/threads/how-to-connect-to-mssql-with-jdbcsql.95389/

I don´t see you did some tests by yourself.

 

[MarkusR]

i believe this is more for intranet, local network

JdbcSQL - Directly connect to remote databases
https://www.b4x.com/android/forum/threads/jdbcsql-directly-connect-to-remote-databases.84016/

-------------

this if u will access a database from phone via internet

[B4X] jRDC2 - B4J implementation of RDC (Remote Database Connector)
https://www.b4x.com/android/forum/t...ation-of-rdc-remote-database-connector.61801/

 

[DonManfred]

i believe this is more for intranet, local network

It does work with an Online DB too. I am using it with the MySQL-Database hosted at my provider...

 

[MarkusR]

It does work with an Online DB too. I am using it with the MySQL-Database hosted at my provider...

so JdbcSQL can use a least a secure connection?

 

[DonManfred]

so JdbcSQL can use a secure connection?

i dont know. I just know that it works with the MySQL Database Hosted at all-inkl
I don´t know if it is secured. I am using it in my Apps which are not at Playstore (only Company internaal)

 

[makis_best]

I made lot of tries to connect local to my sql server but i haven't success yet.
The reason I ask this question for first place

 

[BillMeyer]

I use them both in one app. Sounds strange but I'm converting all my apps to jRDC2 and still currently look at the old database with JdbcSQL. Sort of in a transition phase.

They both work equally well on the DB hosted on my Server at my Provider, a lot safer than the php route and I make sure that I do not send any passwords and usernames but rather use Tokens for verification on JdbcSQL and I think @DonManfred will agree - it's quick !!

After all considered, the safest way (in my opinion) is jRDC2 and if you can - the best you can use, but if you do not have the luxury of setting up the jRDC2 server then I would JdbcSQL is the way to go.

Here is the tutroial for jRDC2 https://www.b4x.com/android/forum/t...ation-of-rdc-remote-database-connector.61801/ (Watch the Video)
and for JdbcSQL https://www.b4x.com/android/forum/threads/jdbcsql-directly-connect-to-remote-databases.84016/ (download the example and work through it)

 

[BillMeyer]

@makis_best - Don't give up - work through these tutorials and then give us more information.

Tell us:
1. Where is your server hosted ?
2. Is it accessible via a public IP ?
3. Use code tags and post some code of how you are trying (just change your server connection items to keep it safe)

If we have more information we can help you.

 

[makis_best]

@BillMeyer - I don't give up.

I try hard to pass the problem for many days.
1. My server is hosted local.
2. Maybe in the future and remote. "If I succeed"

3. That is what i did until now.

#Region  Service Attributes
    #StartAtBoot: False
    #ExcludeFromLibrary: True
#End Region

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Public RemoteSQL As JdbcSQL
    Private driver As String = "net.sourceforge.jtds.jdbc.Driver"
    Private jdbcUrl As String = "jdbc:jtds:sqlserver://192.168.1.2;database=xxxxx"
    Private Username As String = "User1"
    Private Password As String = "Paswword1"
End Sub

Sub Service_Create
    'This is the program entry point.
    'This is a good place to load resources that are not specific to a single activity.
    DisableStrictMode
End Sub

Sub Service_Start (StartingIntent As Intent)
    

End Sub

Sub DisableStrictMode
    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
End Sub

Sub Connect As ResumableSub
    RemoteSQL.InitializeAsync("RemoteSQL", driver, jdbcUrl, Username, Password)
    Wait For RemoteSQL_Ready (Success As Boolean)
    If Success = False Then
        Log("Check unfiltered logs for JDBC errors.")
    End If
    Return Success
End Sub

Sub CloseConnection
    RemoteSQL.Close
End Sub

Sub ListAnimals As ResumableSub
    Wait For (Connect) Complete (Success As Boolean)
    If Success Then
        Try
            Dim sf As Object = RemoteSQL.ExecQueryAsync("RemoteSQL", "SELECT TOP 100 Code FROM ESFIItem", Array(300))
            Wait For (sf) RemoteSQL_QueryComplete (Success As Boolean, Crsr As JdbcResultSet)
            If Success Then
                Do While Crsr.NextRow
                    Log($"Id: ${Crsr.GetInt("id")}, Name: ${Crsr.GetString("name")}"$)
                Loop
                Crsr.Close
            End If
        Catch
            Success = False
            Log(LastException)
        End Try
        CloseConnection
    End If
    Return Success
End Sub

Sub Service_TaskRemoved
    'This event will be raised when the user removes the app from the recent apps list.
End Sub

'Return true to allow the OS default exceptions handler to handle the uncaught exception.
Sub Application_Error (Error As Exception, StackTrace As String) As Boolean
    Return True
End Sub

Sub Service_Destroy

End Sub

 

[OliverA]

1) What are your error messages?

1. My server is hosted local.
2. Maybe in the future and remote. "If I succeed"

But your server is "remote". It is remote to the Android application (since it is not running on the Android device).

 

[MarkusR]

you spoke about my sql but here u use other jtds

jTDS is an open source 100% pure Java (type 4) JDBC 3.0 driver for Microsoft SQL Server

I made lot of tries to connect local to my sql server but i haven't success yet.

its my sql or ms sql or my ms sql? (most people using my sql because its free.)

Private driver As String = "net.sourceforge.jtds.jdbc.Driver"
Private jdbcUrl As String = "jdbc:jtds:sqlserver://192.168.1.2;database=xxxxx"

 

[makis_best]

@MarkusR - It is one MS SQL Server running on a local network.
I am not sure what to use on driver string and jdbUrl string so I can
connect to the mssql

All the post I fund about it, leave many gaps of information

 

[MarkusR]

ok, see also question at #11

 

[makis_best]

@OliverA - The database is remote only.... over LAN.... My mistake

 

[OliverA]

Here is a working connection sample: https://www.b4x.com/android/forum/threads/mssql-jdbc-minimalistic-example.90548/#post-573443
Same thread has another one: https://www.b4x.com/android/forum/threads/mssql-jdbc-minimalistic-example.90548/#post-591195
Another one: https://www.b4x.com/android/forum/threads/mssql-server-with-b4j.77711/#post-492573
Some URL's may require an instance name. Some a port number. That all depends on your SQL configuration. Could a firewall setting on the machine hosting your SQL server be an issue? We just don't know, since you are not posting any error messages. Let's say it does not connect, then simplify your code to just test the connection. You have a lot of code there, with wait for and everything that just needs to be simplified to just test connecting.

public sub testSql()
   RemoteSQL.Initialize2(driver, jdbcUrl, Username, Password)
end sub

Try calling that first. Use Debug mode and see what errors this spits out, if any.

 

[npsonic]

Here's what I use and why thing you should check.

sql.Initialize2("net.sourceforge.jtds.jdbc.Driver","jdbc:jtds:sqlserver://192.168.1.2:1433/mydatabase","User1","Password1")

Port is set to 1433, but you should go to check what is your dynamic port that you should use.
Go "SQL Server Configuration Manager" > "SQL Server Network Configuration" > "TCP/IP" > "IP Addresses" > "IPAll" scroll to the bottom.
I have port 49787 and yours should look somewhat same.

Also don't know if you actually have been testing that your username and password for SQL Server really works.
If your user have rights to the database and you can't login with that user. Use basic Windows Authentication to login, then right click over SQL Server (Ex. 192.168.1.2\MIG2014 (SQL Server 12.0.4100 \User)) in the Object Explorer. Select "Properties" > "Security" > "Server authentication" and make sure that there is selection on "SQL Server and Windows Authentication mode"

 

[makis_best]

@npsonic

I check my IPall port and it says 1433. I need to change it?
I also check the username and password and it is working.

The error log gives me -->

Logger connected to:  LGE LG-V490
--------- beginning of main
--------- beginning of system
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
Check unfiltered logs for JDBC errors.
Completed. Success: false
java.sql.SQLException: Unknown server host name 'Host is unresolved: SERVER'.

 

[OliverA]

Unknown server host name 'Host is unresolved: SERVER'.

That means you have SERVER as your hostname instead of an IP address in your JDBC URL.

 

[makis_best]

@OliverA I make it work.... Finaly...

Thank you all for your help...

Highly appreciate

The code work for me -->

#AdditionalJar: jtds-1.3.1.jar

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Public RemoteSQL As JdbcSQL
    Private driver As String = "net.sourceforge.jtds.jdbc.Driver"
    Private jdbcUrl As String = "jdbc:jtds:sqlserver://192.168.1.2;integratedSecurity=false;databaseName=DbName1;"
    Private Username As String = "User1"
    Private Password As String = "Password1"
End Sub