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 Well-Known Member 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 Well-Known Member 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).
     
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