Android Question MSSQL-JDBC Minimalistic Example

[Charles Biba]

Ok... I am trying to get B4A to connect to an in-house MSSQL server (2014) using a direct JDBC connection. However, things are a bit confusing for an outsider new to B4A and I keep getting "unknown error (14) Can't open database" in the log. This server is running other in-house databases so accepts connections, etc. If I poison the initialize string, I get a massive error, so I am guessing I am on the right track (hopefully). Is there something blatantly wrong with my test? Also, all the examples I found do not have a Boolean parameter in the sql initialize... am I mixing up libraries here?

Thanks!


I have an additional jar in project attributes and have added a reference to SQL in the libraries manager.

#AdditionalJar: mssql-jdbc-6.2.2.jre7.jar

I declared an sql variable in process globals:

Private sql1 As SQL

In the designer I created a button and added a click event. In there i am trying to execute:

sql1.Initialize("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://172.21.1.100:1433/TEST_CB;user=android;password=andy",False)


Ultimately I would like to execute something like:

sql1.Initialize("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://172.21.1.100:1433;integratedSecurity=false;user=android;password=andy;databaseName=TEST_CB;", True)

Cursor = sql1.ExecQuery("SELECT item1, item2 FROM testtable")

For i = 0 To Cursor.RowCount - 1
Cursor.Position = i
Log(Cursor.GetString("item1"))
Log(Cursor.GetString("item2"))
'Next

 

[Erel]

Please use [code]code here...[/code] tags when posting code.

See the JdbcSQL example: https://www.b4x.com/android/forum/threads/jdbcsql-directly-connect-to-remote-databases.84016/
The object type is JdbcSQL not SQL.

 

[Charles Biba]

Ok, got it to compile and run without blatant errors, but some MSSQL-ifcation is still problematic.

Below is my conversion to SQL server. What is the correct first parameter for the InitializeAsync when targeting MSSQL server? Also, should I be specifying MS 6.2.2 library, or the earlier ones (like sqljdbc41.jar)?

Thanks.

#AdditionalJar: mssql-jdbc-6.2.2.jre7

Sub Process_Globals
 Public sql_connection As JdbcSQL
 Private driver As String = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
 Private jdbcUrl As String = "jdbc:sqlserver://172.21.1.100/TEST_CB"
 Private Username As String = "android"
 Private Password As String = "andy"
End Sub


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

 

[Erel]

You have set the event name to sqlserver. This means that the code should be:

Wait For SqlServer_Ready (Success As Boolean)

 

[Charles Biba]

No hair left... lol ....but that's why we try to learn new things.

Still no connection, and in unfiltered logs I am getting:

java.lang.RuntimeException: Class not found: com.microsoft.sqlserver.jdbc.SQLServerDriver
Are you missing an #AdditionalJar attribute setting?


In Main I have the additional jar directive,

#AdditionalJar: mssql-jdbc-6.2.2.jre7

and in my paths configuration I have C:\B4A-Projects\ExtraLibraries that contains

JdbcSQL.jar
JdbcSQL.xml
mssql-jdbc-6.2.2.jre7.jar

 

[Charles Biba]

Ok... doing some research, I discovered that the classnames have apparently changed in different versions. How would I go about determining the correct version? Can the jar be examined to derive the correct name or does that depend entirely on external documentation?


From www.stackoverflow.com:

You are looking at sqljdbc4.2 version like :

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

but, for sqljdbc4 version statement should be:

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

 

[Charles Biba]

What a mess. The only way I could move forward is to use below. The newer version (mssql-jdbc-6.2.2.jre7) kept failing with class not found because the name is probably different. However in this configuration, I am getting an SSL error. I definitely don't know enough about this to properly troubleshoot. It seems that SSL cannot be disabled in the jdbc41 version. Is there a way to get around this? The SQL server has Force Encryction set to NO.


com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Socket closed". ClientConnectionId:0be76013-7613-4400-acd7-189a23c1250c

#AdditionalJar: sqljdbc41.jar

Sub Process_Globals
 Public sql_connection As JdbcSQL
 Private driver As String = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
 Private jdbcUrl As String = "jdbc:sqlserver://172.21.1.100"
 Private Username As String = "android"
 Private Password As String = "andy"
End Sub

 

[Erel]

It might be easier to start with a B4J project. The SQL library is exactly the same and you should connect to 127.0.0.1 assuming that the server runs on the same computer.

 

[Charles Biba]

I was thinking the same thing. Too bad that no one else seems to want to connect like this. Very disappointing at the level of difficulty for something that should be fairly basic and relatively poorly documented; not everyone wants to make something cloud based with middleware when operating on a closed LAN. If anyone can contribute a working example with specific versions of Jars, that would be awesome, and would probably benefit a whole lot of people.

 

[OliverA]

Very disappointing at the level of difficulty for something that should be fairly basic and relatively poorly documented; not everyone wants to make something cloud based with middleware when operating on a closed LAN.

Mini-rant: Yeah, but then you are doing something non-standard: using an Android device to connect directly to Microsoft's SQL Server. Don't blame the forum members, nor the B4a environment, for not having an easy 1-2-3 step setup for you.

Issue: It looks like Microsoft is enforcing SSL with their JDBC drivers (see https://blogs.msdn.microsoft.com/jd...by-using-secure-sockets-layer-ssl-encryption/) and something in Android (unrelated to B4a) is causing issues with this. Please remember, this is a "desktop" driver, not really geared towards Android.

Solution (source: https://stackoverflow.com/a/18628848): Use jTDS (http://jtds.sourceforge.net/index.html). I've tried both 1.2.8 and 1.3.1 and was able to connect to both SQL Server 2008 and 2016. I've not gone beyond the initial connection, so your mileage may vary. Please note that this will be a non-secured, non-encrypted connection (using the URL option ssl=request does not work - it produces a java.sql.SQLException - and I'm to dis-interested right now to follow up on that).

Dim mySQL As JdbcSQL
mySQL.Initialize("net.sourceforge.jtds.jdbc.Driver", "jdbc:jtds:sqlserver://xxx.xxx.xxx.xxx;databaseName=someDb;user=someUser;password=somePassword;"

 

[Charles Biba]

Thanks for the reply... This certainly isn't a commentary aimed at the forum. Just generally, libraries change, classnames fluctuate, different jar files break things, errors are non-descriptive.... it's discouraging that the state of APIs and documentation is still so ambiguous. Doing something in a closed LAN might be heresy for the cloud gang, but it doesn't mean that people don't want to do that.

I will do some testing with jtds. I was resigned to using an ASPX service as middleware, but since you were able to connect to 2016, that's worth another shot.

 

[MrKim]

I have been able to get my connection to SQL Server working using the jtds driver only. Never got mssql-jdbc to work.

It REQUIRES Android 7. 6 did not work. Throws an error, don't know why.

I agree 100% there should be a reliable, stable well documented well supported solution to this. Crazy to me that there is not.

Here is what I finally got working:

#Region Project Attributes

    #AdditionalJar: jtds-1.3.1.jar

#End Region

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.

    Dim sql1 As JdbcSQL', Str As StringUtils
End Sub

'I use Callback to tell me what sub to return to if the connection fails and I have to re-establish it.
Public Sub Connect(CallBack As String)

        sql1.Close
        sql1.InitializeAsync("Connect", "net.sourceforge.jtds.jdbc.Driver", "jdbc:jtds:sqlserver://192.168.1.5:65218;databaseName=MyDBName;user=UserName;password=MyPW;appname=SKMJL;wsid=MyWS;loginTimeout=10", "UserName", "MyPW")  'sql 2012


    Catch
        Msgbox2Async("Connection to the network failed: " & CRLF & LastException.Message , "Network Error", "OK","","", Null, True)
    End Try

End Sub

'I have left an example SQL Select statement in here, ExecQueryAsync
Sub Connect_Ready (Success As Boolean)
    ProgressDialogHide
    If Success = False Then
        MsgboxAsync("Failed connect to the server. Server is not available, database is not availble, connect string is wrong, or you are having network/firewall issues. Contact your system administrator.", "Network/SQL Error")
        MsgboxAsync(LastException.Message,"JAva Error:")
        Return
    End If
    Try
        Connecting = False
        ProgressDialogShow("Getting Configuration Data")
        GetCount 'Check user counts
        Dim SenderFilter As Object = sql1.ExecQueryAsync("Switches", "SELECT  SW17, SW34, SW37, SW39 FROM Switches;", Null)
        Wait For (SenderFilter) Switches_QueryComplete (Success2 As Boolean, Crsr As JdbcResultSet)
        If Success2 = False Then
            ProgressDialogHide
            MsgboxAsync("Failed to get the required Configuration (Switches) data - Cannot continue." & CRLF & LastException.Message, "DB Error")
            Screen = "Home"
            Return
        End If
    Catch
        Log(LastException.Message)
        ToastMessageShow("Connect_Ready" & CRLF & LastException.Message, True)
    End Try
 
End Sub

A Google search for jtds-1.3.1.jar will bring up the download.

IMPORTANT NOTE!
I could only get it working with ip addressort number never with the server name.
The IP Address is the Server IP found by running IPCONFIG at a command prompt.

AND THE PORT NUMBER IS NOT THE DEFAULT! DO THIS:
Open SQL Server Management Studio
Go to Management>SQL Server Logs
Open the Current Server Log
Search for 'any'
You want the port on the <ipv4> line 65214 in this case. This port seems to be SQL server version specific and does not seem to change. If you can't find this line it is probably turned off/Disabled and you will have to fix that.
That is the only port I have found that works.

I have used this to connect to SQL Server 2008/2012/2014
hope this helps

 

[3394509365]

I also have the same problem of Charles and I can't in any way connect to my db neither with host nor with IP.
I do not know what to do. Yet with B4A I had always felt good and the problems were solved in some way, but nothing is clear on this topic.

 

[OliverA]

but nothing is clear on this topic.

Did you read my mini rant? You are trying to use an mobile operating system (Android) like a desktop operating system with software (JDBC/Direct SQL Access) designed for desktop operating systems. I don't think Google cares at all if their ever changing landscape breaks desktop environments, nor do I think MS cares about issues with their desktop environment products breaking in a mobile environment.

I also have the same problem of Charles and I can't in any way connect to my db neither with host nor with IP.

If you are having the same problems as Charles, are you also using MS's JDBC driver? If so, you may want to try the jtds drivers, since they seem to work (see my post, see @MrKim's post).

 

[MrKim]

I also have the same problem of Charles and I can't in any way connect to my db neither with host nor with IP.
I do not know what to do. Yet with B4A I had always felt good and the problems were solved in some way, but nothing is clear on this topic.

I can tell you this about my code above. Our app is working successfully at over a dozen different locations. I am NOT a networking expert, I am just a dumb database programmer trying to solve a customer problem. Every time we go in, try to get the app to connect to the customers DB. It fails, they call their IT people. We explain the problem, they do whatever it is they do, and then it works.

OliverA:
You are absolutely right, but the customer wants what the customer wants and if he is willing to pay and we can make it work that is how it is.

 

[Jaymer]

Mini-rant: Yeah, but then you are doing something non-standard: using an Android device to connect directly to Microsoft's SQL Server. Don't blame the forum members, nor the B4a environment, for not having an easy 1-2-3 step setup for you.

@OliverA Please educate me.
I don't understand whats "non-standard" about this.
I need a way for people in my warehouse to walkaround and do inventory scanning & location assignment.

So is the non-standard part the device or the connection method?
THEN, whats the standard way to do it?

I'm very serious. Phone/tablet is a cheap device. So thats the preferred device. And how am i SUPPOSED to connect to the db?
I came here (b4x) searching for a development environment to do this easily/RAD. Lots of other tools out there too.
I appreciate that you used your expertise to find this TDS solution to connect to DB - though its quite old now and MSSQL 2012 was its last posted version, so that has me worried a bit that I go down this path only to find another issue.

thx
jaymer...

 

[Jaymer]

I can tell you this about my code above. Our app is working successfully at over a dozen different locations. I am NOT a networking expert, I am just a dumb database programmer trying to solve a customer problem. Every time we go in, try to get the app to connect to the customers DB. It fails, they call their IT people. We explain the problem, they do whatever it is they do, and then it works.

hi @MrKim
i need to connect to a local MSSQL db in our warehouse.
I am brand new to b4x.
Am I going to have problems?
Is this still working for you?
Can you send me any code or hints? I'm worried about replying on a 6 year old StackOverflow thread and planning my direction on old information about old drivers.
When "they" call the IT people - have you any idea about what the issue is that they seem to fix?

thx VERY much![/QUOTE]

 

[OliverA]

I don't understand whats "non-standard" about this.

It is unsupported. The drivers you need to use are designed for desktop usage. That they work on Android is a side effect (of Android having a Java like base). Also, you will always have to deal with using old drivers (forget about Java 8/11 supported drivers). You have to work harder at it to get it to work and all functionality that works on a desktop is not guaranteed to work on Android (and it is up to you to figure out). Now maybe there are other companies that offered PAID drivers that work with the latest SQL databases using Android. Maybe (I've not looked into this, since all the forum questions here seem to pertain to either Microsoft or jTDS JDBC drivers).

Is there a work-around? Sure
1) Use a PHP backend (lot's of posts about that here)
2) Use jRDC2 (you'll find support for this here in the forums +@Erel if you find any issues with it)
3) Something like the above

In these cases, you would be doing the database access on the backend, therefore allowing the usage of the latest and greatest drivers (and all their functionality, if needed). Also, you would de-couple your "client" software from the database used, allowing (if needed) you to change databases used (via modifying/rewriting the backend).

BTW, the rant (rightfully or wrongfully) is targeted at the attitude that the usage of freely available JDBC drivers should just work effortlessly on Android. It's not going to happen (except for very simplistic requirements). And it's not the fault of the development environment offered here.

 

[OliverA]

Am I going to have problems?

I would say it depends on the features that you use. For standard DDL's, DML's, TCL's and DCL's (https://beginner-sql-tutorial.com/sql-commands.htm), you should be fine (you still need to do testing). Delve into stored procedures with return values, you may start to be on shaky ground. Start using additional features of 2012 and up, you may be totally out of luck. And BTW, all this may be happening unencrypted (I'm not sure that any of the drivers support encrypted connections, and if they do, that they use the latest protocols (not old hack-able ones) - someone else may want to chime in).

 

[Jaymer]

Is there a work-around? Sure
1) Use a PHP backend (lot's of posts about that here)
2) Use jRDC2 (you'll find support for this here in the forums +@Erel if you find any issues with it)

ok, thx for this tip.
1) I'd use my node.js server before I'd use PHP. But I was trying to cut out the middleman. This is an internal app, but learning something new is better, because then I can learn to use it outside the warehouse also.
2) I see the benefit of using jRDC2 - very nice, and esp. since its tightly integrated with b4A, that seems the way to go.