Android Question MSSQL-JDBC Minimalistic Example

Discussion in 'Android Questions' started by Charles Biba, Mar 12, 2018.

  1. Charles Biba

    Charles Biba Member Licensed User

    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
     
  2. Erel

    Erel Administrator Staff Member Licensed User

  3. Charles Biba

    Charles Biba Member Licensed User

    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.



    Code:
    #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
     
  4. Erel

    Erel Administrator Staff Member Licensed User

    You have set the event name to sqlserver. This means that the code should be:
    Code:
    Wait For SqlServer_Ready (Success As Boolean)
     
  5. Charles Biba

    Charles Biba Member Licensed User

    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
     
  6. Charles Biba

    Charles Biba Member Licensed User

    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");
     
  7. Charles Biba

    Charles Biba Member Licensed User

    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

    Code:
    #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
     
    Last edited: Mar 14, 2018
  8. Erel

    Erel Administrator Staff Member Licensed User

    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.
     
  9. Charles Biba

    Charles Biba Member Licensed User

    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.
     
  10. OliverA

    OliverA Expert Licensed User

    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).

    Code:
    Dim mySQL As JdbcSQL
    mySQL.Initialize(
    "net.sourceforge.jtds.jdbc.Driver""jdbc:jtds:sqlserver://xxx.xxx.xxx.xxx;databaseName=someDb;user=someUser;password=somePassword;"
     
    Erel and Peter Simpson like this.
  11. Charles Biba

    Charles Biba Member Licensed User

    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.
     
  12. MrKim

    MrKim Active Member Licensed User

    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:
    Code:
    #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","",""NullTrue)
        
    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 address:port 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.

    upload_2018-5-25_16-15-11.png

    I have used this to connect to SQL Server 2008/2012/2014
    hope this helps
     
    Last edited: May 26, 2018
    Tony Bulo, victormedranop and Erel like this.
  13. 3394509365

    3394509365 Active Member Licensed User

    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.
     
  14. OliverA

    OliverA Expert Licensed User

    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.
    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).
     
  15. MrKim

    MrKim Active Member Licensed User

    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.
     
    OliverA likes this.
  16. Jaymer

    Jaymer New Member Licensed User

    @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...
     
  17. Jaymer

    Jaymer New Member Licensed User

    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]
     
  18. OliverA

    OliverA Expert Licensed User

    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.
     
  19. OliverA

    OliverA Expert Licensed User

    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).
     
  20. Jaymer

    Jaymer New Member Licensed User

    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.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice