Android Question How to use JDTS or JRDC to get successful connections to SQL Server 2019 DB?

[Tamer El-Shimy]

I've tried the following with a simple DB which has only windows authentication for experimental purposes :

#AdditionalJar : mssql-jdbc-10.2.0.jre17.jar
    #AdditionalJar : JDBCSQL.jar
    ''#AdditionalRes : sqljdbc_xa.dll
    #AdditionalJar :jtds-1.3.1.jar
 
    Public Sub KPI_Ready(Success As Boolean)
    If Success = False Then
    Log ("Failed to connect")
    Else if Success=True Then
        Log ("Succesful Connection")
    End If 
End Sub
Private Sub ButtonAdd_Click
    ''Private connectionString As String="jdbc:sqlserver://localhost;Port=80;databaseName=KPI"
    '' Private connectionString As String="jdbc:sqlserver://{SERVER}/{INSTANCE};Port={PORT};databaseName={DATABASE}"
    Private connectionStringTrusted As String="jdbc:jtds:SQLServer://Localhost:17178;databaseName='KPI';integratedSecurity=false;"
    AnySQL.InitializeAsync("KPI","net.sourceforge.jtds.jdbc.driver",connectionStringTrusted,"","")
    ''AnySQL.InitializeAsync("KPI","com.microsoft.sqlserver.jdbc.SQLServerDriver" , connectionStringTrusted,"","")
    If AnySQL.IsInitialized Then
   AnyResultSet= AnySQL.ExecQuery("Select * from Department")
    Log(AnyResultSet.GetString("Department_Name"))
    End If
    B4XPages.ShowPageAndRemovePreviousPages("Item")
End Sub

And I get the message "Failed to connect". What could be the problem?

 

[Erel]

1. Localhost should probably be localhost.
2. However localhost will only work if the SQL Server runs on the Android device itself, and it doesn't.

 

[aeric]

So you need to put the IP address of the DB server.

[B4X] jRDC2 - B4J implementation of RDC (Remote Database Connector)

RDC is a middleware server that makes it simple to safely connect clients and remote SQL database servers. jRDC2 is the latest version. All new projects should use this version. jRDC2 is made of two components: - B4J server. The server receives the requests from the clients, issues the SQL...

www.b4x.com

 

[Tamer El-Shimy]

Ok , Now I'm using InitializeAsync and ExecQueryAsync/ExecQuery with the following examples :

Private connectionStringTrusted As String="jdbc:jtds:SQLServer://192.168.1.106:1433;databaseName=KPI;integratedSecurity=false;"
AnySQL.InitializeAsync("KPI","net.sourceforge.jtds.jdbc.Driver",connectionStringTrusted,"","")
Dim MyList As List
MyList.Initialize
Dim AnyObject As Object= AnySQL.ExecQueryAsync("KPI","Select * from Employees",MyList)

Just because ExecQueryAsync returns an object. But if I want to return JDBCResultSet by ExecQuery the event returns Success : False , RS not initialized

AnyResultSet=AnySQL.ExecQuery("Select * from Department")
Public Sub KPI_Ready(Success As Boolean)
    Log ("Connection :"  & Success)
    If Success = False Then
    Log ("Failed to connect")
    Else
        Log ("Succesful Connection")
    End If
End Sub
Private Sub KPI_QueryComplete(Success As Boolean , RS As JdbcResultSet)
Log("Query :"&Success)
Log (RS)
End Sub

 

[Erel]

Have you opened port 1433 in Windows firewall?

 

[Chris2]

...which has only windows authentication...

I might be getting the wrong end of the stick here, but....

1. You have

integratedSecurity=false;

in your jdbcurl.
I think in under Windows it needs to be

integratedSecurity=true;

to use Windows Authentication.
Is that the same for android?

2. Can you use Windows Authentication from an Android Device?
3. Does the JTDS driver support integrated security?
I see no mention if it here - http://jtds.sourceforge.net/faq.html#urlFormat

 

[Tamer El-Shimy]

Ok , What should I do to the DB to get a simple Select statement from it after:
1- Open firewall port 1433 on windows 10.
2- Enable TCP/IP connection on SQL Server 2019.
3- Put integrated security = true.
4- Modify the SQL Server Authentication to Mixed mode ( Windows and SQL Server 2019).
5- Create new role with a username and password then Pass them to InitializeAsync.
?
After all these procedures still Query : False , Resultset not initialized.
I've excluded network and security possibilities , replaced the Internal IP to real IP to avoid that the Mobile Android Device is on a mobile data while Laptop is on Work Network.

 

[Tamer El-Shimy]

This is the Logging result of AnyObject resulted from ExecQueryAsync "anywheresoftware.b4j.objects.SQL@4dee7d2"

 

[Erel]

I recommend you to start with B4J and run it on the same computer where the db is running. This will eliminate several possible failure points.
In the long run, running jRDC2 is much better then making direct connections from the mobile devices.

 

[Chris2]

1- Open firewall port 1433 on windows 10.
2- Enable TCP/IP connection on SQL Server 2019.
3- Put integrated security = true.
4- Modify the SQL Server Authentication to Mixed mode ( Windows and SQL Server 2019).
5- Create new role with a username and password then Pass them to InitializeAsync.

You seem to be mixing authentication methods.

As I understand it;
a) if you have 'integratedSecurity=true', then you are using Windows Authentication and should not provide a username or password (Note that I don't think you can use Windows Authentication when connecting from an Android device).
b) if you have 'integratedSecurity=false', then you are using SQL Server Authentication and should supply the user name and password.

 

[Tamer El-Shimy]

Thank you so much for your help trials , I used AnySQL.Initialize2 and It initialized correctly and I got data by ExecuteQuery , but InitializeAsync failed for unknown reason.

 

[Wien.Mart]

I use this AdditionalJar:mssql-jdbc-9.4.1.jre11.jar from microsoft ...

Download - JDBC Driver for SQL Server

Download the Microsoft JDBC Driver for SQL Server to develop Java applications that connect to SQL Server and Azure SQL Database.

docs.microsoft.com

Connection string is:
Sql1.Initialize("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://192.168.1.1:1433;user=sa;password=mypassword;databaseName=mydatabase;")

I hope this helps ...

 

[Chris2]

...but InitializeAsync failed for unknown reason.

If you're still using the code from your first post, then you are querying the database before the connection is ready.
See - https://www.b4x.com/android/forum/threads/jsql-v1-20-asynchronous-initialization.42251/

It should be something like:

Private Sub ButtonAdd_Click

     Private connectionStringTrusted As String="jdbc:jtds:SQLServer://localhost:17178;databaseName='KPI';integratedSecurity=false;"
    AnySQL.InitializeAsync("KPI","net.sourceforge.jtds.jdbc.driver",connectionStringTrusted, user, password)

End Sub

Sub KPI_Ready(Success As Boolean)

    If Success = False Then
        Log ("Failed to connect")
    Else if Success=True Then
        Log ("Succesful Connection")
        dim AnyResultSet as ResultSet = AnySQL.ExecQuery("Select * from Department")
        Do While rs.NextRow
             Log(AnyResultSet.GetString("Department_Name"))
        Loop
        rs.Close
    End If

End Sub

(I've not checked the connection string or anything else here, just meant to point out that the query should be sent after the successful connection is established)

 

[Tamer El-Shimy]

If you're still using the code from your first post, then you are querying the database before the connection is ready.
See - https://www.b4x.com/android/forum/threads/jsql-v1-20-asynchronous-initialization.42251/

It should be something like:

Private Sub ButtonAdd_Click

     Private connectionStringTrusted As String="jdbc:jtds:SQLServer://localhost:17178;databaseName='KPI';integratedSecurity=false;"
    AnySQL.InitializeAsync("KPI","net.sourceforge.jtds.jdbc.driver",connectionStringTrusted, user, password)

End Sub

Sub KPI_Ready(Success As Boolean)

    If Success = False Then
        Log ("Failed to connect")
    Else if Success=True Then
        Log ("Succesful Connection")
        dim AnyResultSet as ResultSet = AnySQL.ExecQuery("Select * from Department")
        Do While rs.NextRow
             Log(AnyResultSet.GetString("Department_Name"))
        Loop
        rs.Close
    End If

End Sub

(I've not checked the connection string or anything else here, just meant to point out that the query should be sent after the successful connection is established)

Thank you so much for this tip , InitializeAsync , The query should be sent after the successful connection is established.

 

[Tamer El-Shimy]

So you need to put the IP address of the DB server.

[B4X] jRDC2 - B4J implementation of RDC (Remote Database Connector)

RDC is a middleware server that makes it simple to safely connect clients and remote SQL database servers. jRDC2 is the latest version. All new projects should use this version. jRDC2 is made of two components: - B4J server. The server receives the requests from the clients, issues the SQL...

www.b4x.com

Thank you so much . This point is so important to put the static IP address of the server instead of Localhost , because the android device isn't your DB Server.

 

[Bladimir Silva Toro]

Hello @Tamer El-Shimy

I recommend you read my tutorial in the following link: https://www.b4x.com/android/forum/t...-android-to-ms-sql-server-using-jrdc2.114856/

There are the steps how to connect to SQL Server using jRDC

Let me know if you have any questions.

 

[Albert Kallal]

I use this AdditionalJar:mssql-jdbc-9.4.1.jre11.jar from microsoft ...

Download - JDBC Driver for SQL Server

Download the Microsoft JDBC Driver for SQL Server to develop Java applications that connect to SQL Server and Azure SQL Database.

docs.microsoft.com

Connection string is:
Sql1.Initialize("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://192.168.1.1:1433;user=sa;password=mypassword;databaseName=mydatabase;")

I hope this helps ...

Gee, I never been able to get the ms driver to work. I can perhaps give the MS driver another go, and it been about 2 years now. We talking about a direct connection here - no "server" process running.

but, to the original poster?

But, as a few pointed out:
You have to use IP address - you don't' have "wins" (computer name) resolution.
You have to turn on for sql express the tcp/ip settings - by default sql server express has those turned off.
You have to create a sql logon, and THEN make sure the given database ALSO has a "user" (you need both).
You thus have to ensure integrated logons are allowed - you can't use windows auth.

So, to connect, I use this :

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

And my driver, jdbUrl, UserName, and Password are this:

    Private driver As String = "net.sourceforge.jtds.jdbc.Driver"
    Private jdbcUrl As String = "jdbc:jtds:sqlserver://192.168.0.251;DatabaseName=TEST3;instance=SQLEXPRESS"

    Private Username As String = "Albert"
    Private Password As String = "password"

Note VERY careful in above. You MUST specify the sql instance. In most cases, that is SQLEXPRESS. You leave out the "instance", and your connection will not work. So, BOTH database name and "instance" are required. Now on other platforms, and in 99% of cases, you can specify server (ip address) and sql "instance" in on go, say like this: 192.168.0.251/SQLEXPRESS.

Edit: also, now since about sql 2017 and later? You MUST start the sql browser service. Again in relative recent past, that was not required, now it is, so this one:

And of course make sure these are turned on:

however, using the jdbc driver? NOPE!!! - you can't do that. (ie: you can't just add on /SQLEXPRESS, you specify SQL instance in the string as seperate as per above).
(but if you could and did get the Microsoft jdbc driver working, then how you connect is VERY different - you include the instance right after the server name - not so with jtds driver.

So, with this setup? Yes, you WILL have to setup a logon, and passwords, and you WILL ALSO have to setup a "user" for that one give database.

So, you need a sql logon here, here:

And once you setup that user, then using desktop sql tools - and connect using that user - VERIFY that it works.

And during testing, turn off your windows fire walls - just to be sure until you get this working, you can then tighten up that later.

Regards,
Albert D. Kallal
Edmonton, Alberta, Canada

 

[Dave G]

I've been developing mobile applications that access data on a server for over 20 years. None of them directly accessed a database on a server! I had to convert one that a customer did that way (15,000 lines of code) because it was a huge failure. My approach has always to decouple from the server using transaction requests to the server that then returned that data from the database as an ASCII formatted message. Using that approach I have been able to replace the server without any changes to the mobile client and vice versa. This approach uses basic TCP but can also utilize HTTP REST architecture. I posted source code for a B4A app (BluetoothPages-forum) that uses TCP to validate data scanned by a Bluetooth scanner or entered on keyboard on a server.

 

[Albert Kallal]

Sure, without question, the phone part should interact with a web service. - little debate.

However, there are a number of scenarios in which a direct hit to the database can work - and work rather well. For my case, I have a working sqlite database running on the phone. You can add records, update - do whatever in that app. However, I ALSO wanted to sync that data with sql server. (bi-directional). So any edits on phone, including additions? And that often going to be records in child tables.

And any edits server side - including additions? They also sync to the phone.

So, most of the time, you running the local data base on the phone. but, by even wi-fi, when I am on the same network location as sql server?
I can tap a button - and sync my changes.

The advantages were:

No web server required. ONLY sql server had/has to be running.

So, eliminations of having to setup and have some web service, or in fact having to setup, run, maintain a web server was 100% eliminated from this equation.

However, hands down, a "constant" interaction against the live database with a direct connection? No, that is a bad idea.

By eliminations of the web server, then a BOATLOAD of efforts, setup, time etc. was saved. However, as I stated, on a daily bases, while the database on the phone (sqlite) has quite a few records, the number of updates required during a "sync" process is VERY small. So say out of 10,000 records, we might wind up sync of data of about 10 records. and when I hit my "sync" button, that occurs in less then 1 second.

So, all in all? this was as fantastic choice, since the only infrastructure required on site was that of running sql server - which of course was ALREADY running.

But, a live applcation that constant updates and uses a direct connection to sql server? No, it is a bad idea, and not at all reliable.

And for say an applcation that is to connect this way - say by VPN and using the phones data plan? No, this is not a great idea, and there not much of a "recovery" model in place. And with a web site/service exposed, then no VPN to the company network would be required. We did not need nor require off-site operation of this applcation - only on site and only that wi-fi connect to sync once a day - or ever every other day is and was fine.

So, like always, this is not 0 or 1, or all or nothing. There are some really good use cases for this setup - but to be fair, they are few.

However, the end result is I did wind up building a VERY nice "sync" set of routines. I was going to consider buying and using a commercial product - based on the .net sync framework - and in fact looked into writing a .net sync client for android. (sadly, the .net sync framework is depreciated, but still a great choice - even now).

However, the set of routines I built turned out to work very well. And despite the multiple tables using auto number PK values, and FK's for the child tables on sql server?
The result works well, and I even managed to quite much "transparent" deal with the PK, FK issues during the sync process. Been busy, but I will when I find some time, I do plan to post the sync code I used - as has many more uses beyond my applcation requirements. And the code could be converted to use say "rest" api's for a given web site, and thus no more "direct" connection to the database would be required.

Little arguments here - direct connect is a LIMITED use case - but they do exist.
Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

 

[Dave G]

Sure, without question, the phone part should interact with a web service. - little debate.

However, there are a number of scenarios in which a direct hit to the database can work - and work rather well. For my case, I have a working sqlite database running on the phone. You can add records, update - do whatever in that app. However, I ALSO wanted to sync that data with sql server. (bi-directional). So any edits on phone, including additions? And that often going to be records in child tables.

And any edits server side - including additions? They also sync to the phone.

So, most of the time, you running the local data base on the phone. but, by even wi-fi, when I am on the same network location as sql server?
I can tap a button - and sync my changes.

The advantages were:

No web server required. ONLY sql server had/has to be running.

So, eliminations of having to setup and have some web service, or in fact having to setup, run, maintain a web server was 100% eliminated from this equation.

However, hands down, a "constant" interaction against the live database with a direct connection? No, that is a bad idea.

By eliminations of the web server, then a BOATLOAD of efforts, setup, time etc. was saved. However, as I stated, on a daily bases, while the database on the phone (sqlite) has quite a few records, the number of updates required during a "sync" process is VERY small. So say out of 10,000 records, we might wind up sync of data of about 10 records. and when I hit my "sync" button, that occurs in less then 1 second.

So, all in all? this was as fantastic choice, since the only infrastructure required on site was that of running sql server - which of course was ALREADY running.

But, a live applcation that constant updates and uses a direct connection to sql server? No, it is a bad idea, and not at all reliable.

And for say an applcation that is to connect this way - say by VPN and using the phones data plan? No, this is not a great idea, and there not much of a "recovery" model in place. And with a web site/service exposed, then no VPN to the company network would be required. We did not need nor require off-site operation of this applcation - only on site and only that wi-fi connect to sync once a day - or ever every other day is and was fine.

So, like always, this is not 0 or 1, or all or nothing. There are some really good use cases for this setup - but to be fair, they are few.

However, the end result is I did wind up building a VERY nice "sync" set of routines. I was going to consider buying and using a commercial product - based on the .net sync framework - and in fact looked into writing a .net sync client for android. (sadly, the .net sync framework is depreciated, but still a great choice - even now).

However, the set of routines I built turned out to work very well. And despite the multiple tables using auto number PK values, and FK's for the child tables on sql server?
The result works well, and I even managed to quite much "transparent" deal with the PK, FK issues during the sync process. Been busy, but I will when I find some time, I do plan to post the sync code I used - as has many more uses beyond my applcation requirements. And the code could be converted to use say "rest" api's for a given web site, and thus no more "direct" connection to the database would be required.

Little arguments here - direct connect is a LIMITED use case - but they do exist.
Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

I've created several mobile/server applications using variations of a Windows .NET TCP (VB.NET!) server app I originally created back in the 90's. I even had one that used Windows Mobile devices written in VB.NET that utilized a SQL Server Compact Edition database so I could operate without WiFi and sync when needed using USB connection. The one app that a client had implemented that directly accessed SQL Server had been written in VB.NET on Windows Mobile. I rewrote it using Xamarin and C# on the mobile unit and VB.NET as a web service using IIS. Needed multi-threaded, robust service as it was running a fairly large warehouse. Converted over 15,000 lines of code and once we did the integration testing only one minor bug was found in the subsequent years. BTW, the one thing I made a lot of use of was .NET DataTables. They allow for the creation of a database-like environment on a mobile device. Was able to export the contents of DataTable to .xml with one statement such that the mobile unit could recover from a complete reboot and pick up where it left off! Anyway, thanks for the overview of your app.