Android Question jSQL - (SocketException) java.net.SocketException: Socket closed

[Nokia]

I'm getting the following error when I try to use JSQL on android app: (SocketException) java.net.SocketException: Socket closed

I have added the following permission: AddPermission(android.permission.INTERNET)

I have also added this jar file: #AdditionalJar: mssql-jdbc-6.2.2.jre8.jar

I have no issues using code on B4J..

this is my code:

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    Activity.LoadLayout("frmMain")
    Activity.Title = SOFTWARE_NAME
    
    Dim bm As Bitmap = LoadBitmap(File.DirAssets, "NextTest_Background.png")
    SetTiledBackground(Activity, bm)

    sqlMS.InitializeAsync("sqlLoad", GetDriverClass,mdlShared.GetJDBC_url(workingFolder, strConfigFile, "D"), _
                GetJDBC_url(workingFolder, strConfigFile, "U"),GetJDBC_url(workingFolder, strConfigFile, "P"))
    
End Sub

Sub GetDriverClass() As String
    Dim s As String
    s = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    Return s
End Sub

Sub GetJDBC_url (FileLocation As String, FileName As String, part As String) As String
    
    'part: A = all, D=Database/server, U=user and P=user password

    Dim s As String
    Dim strSQLServer As String = "192.168.1.20"
    Dim strDatabase As String = "DatabaseName"
    Dim strUserName As String = "User"
    Dim strUserPassword As String = "Password"
    
    If part = "A" Then
        s = "jdbc:sqlserver://" & strSQLServer & ":1433;databaseName=" & strDatabase & ";user=" & strUserName & ";password=" & strUserPassword & ";"
    else If part = "D" Then
        s = "jdbc:sqlserver://" & strSQLServer & ":1433;databaseName=" & strDatabase & ";"
    else If part = "U" Then
        s = strUserName
    else If part = "P" Then
        s = strUserPassword
    End If
    
    Return s
    
End Sub

This is a in house database server, never going to need to connect remotely and if It does, it will be used through a vpn app on tablet.

 

[Erel]

1. Big mistake to do it in Activity_Create. Move it to the starter service, or better switch to B4XPages.
2. Better to use JdbcSQL library.
3. Please post the full error message.

 

[Nokia]

1. Big mistake to do it in Activity_Create. Move it to the starter service, or better switch to B4XPages.
2. Better to use JdbcSQL library.
3. Please post the full error message.

this is all that I get:

Logger connected to: samsung SM-T550
--------- beginning of main
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = false **
** Activity (acsetup) Create, isFirst = true **
** Activity (acsetup) Resume **
** Activity (acsetup) Pause, UserClosed = true **
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = false **
** Activity (acsetup) Create, isFirst = false **
** Activity (acsetup) Resume **
** Activity (acsetup) Pause, UserClosed = true **
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = true **
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
(SocketException) java.net.SocketException: Socket closed

I was looking at JdbcSQL library prior to posting, does this work on all new version of Microsoft SQL?

 

[Albert Kallal]

To my knowledge, no one here has been able to get the later msSQL jdbc drivers working. it has some issue with attempting to connect "secure" first, and it then just seems to fail outright.

I have a "ready made" sample test. you just put in the server (IP) address, the instance name of sql server, user and pass word. If it connects, then your software should also. You can find my working sample in this thread:

https://www.b4x.com/android/forum/t...ms-sql-server-2016-in-b4a.114798/#post-717800

Been too busy, but I hope once in a while (like you just did), do try the newer jDBC drivers from Microsoft - but so far, they just don't seem to work, and thus one needs to use the "jts" one. That's the only one I could ever get to work.

Work your way though that above thread. As noted, the working sample I have should work - and it has a screen/form to enter your values.

R
Albert

 

[Nokia]

To my knowledge, no one here has been able to get the later msSQL jdbc drivers working. it has some issue with attempting to connect "secure" first, and it then just seems to fail outright.

I have a "ready made" sample test. you just put in the server (IP) address, the instance name of sql server, user and pass word. If it connects, then your software should also. You can find my working sample in this thread:

https://www.b4x.com/android/forum/t...ms-sql-server-2016-in-b4a.114798/#post-717800

Been too busy, but I hope once in a while (like you just did), do try the newer jDBC drivers from Microsoft - but so far, they just don't seem to work, and thus one needs to use the "jts" one. That's the only one I could ever get to work.

Work your way though that above thread. As noted, the working sample I have should work - and it has a screen/form to enter your values.

R
Albert

thanks.. I'll check it out..
are you able to do updates and inserts as well ?

 

[Albert Kallal]

In that sample? No - not yet! The idea was to build a "screen" in which you type in IP (server), sql instance name, user ID and password.
If you then hit connect - then at least one "knows" that they can connect to their sql server. So the idea here was to quick test.
Often there is a LARGE number of issues that are "wrong" or not setup. No use trying bits of code over and over until such time a simple known connection to the server will work. So, that's the intention.

Been busy, but I did consider (want to) include a network scanner - display a list of servers to connect to. But busy. Still, I think that handy dandy connect test, and the ability to enter + try a few sql queries is a great start, but more important a "quick test" that one can connect.

If one can't connect with that sample, then no need to try hours of different coding! - so that's the idea here.

The sample does need some love and care - but at least we have a quick "test" sample.

A few here have suggested that I make it work say with the b4xtable sample (and that table displayer is VERY nice).
Thus, perhaps I will add a table editor option here.

I do have a set of routines that will take a sql query, fill out a view. And then I have a routine that takes the view, and sends out the controls back to the table. (got VERY tired of writing code to pull data in, and out of views - so I have a general routine that does this for me. And I use near same code when using sqlite on a local database. And I have a table sync routine. (it syncs the local tables to sql server).

My most difficult issue? it was that of dealing with datetime values in sqlite, and then dealing with SQL server date time values. A real whack a mole process - but one I finally think I have a handle on. So, in place of a "edit" ability, I spent my time on building some code to push/pull a row of data in and out of views. Thus now I can just drop controls on a view - (set tag = column name from database), and my "load" and "save" code now is darn near the SAME for any new screen I have to edit data.

So, given time? Yes, I do plan and want to create a much better version of that "tester". There is a sqlite table browser utility floating around on B4a forums - might grab that and make it work with sql server and the jtc drivers.

And I been wanting to research further the use of the Microsoft jdbc drivers. I think they would handle the date/time issues a bit better - but the jts driver works - and that's about our best (only) choice right now if one wants to direct connect to sql server (and I do, and my data sync software works that way).

Regards,
Albert D. Kallal
Edmonton, Alberta Canada

 

[Nokia]

In that sample? No - not yet! The idea was to build a "screen" in which you type in IP (server), sql instance name, user ID and password.
If you then hit connect - then at least one "knows" that they can connect to their sql server. So the idea here was to quick test.
Often there is a LARGE number of issues that are "wrong" or not setup. No use trying bits of code over and over until such time a simple known connection to the server will work. So, that's the intention.

Been busy, but I did consider (want to) include a network scanner - display a list of servers to connect to. But busy. Still, I think that handy dandy connect test, and the ability to enter + try a few sql queries is a great start, but more important a "quick test" that one can connect.

If one can't connect with that sample, then no need to try hours of different coding! - so that's the idea here.

The sample does need some love and care - but at least we have a quick "test" sample.

A few here have suggested that I make it work say with the b4xtable sample (and that table displayer is VERY nice).
Thus, perhaps I will add a table editor option here.

I do have a set of routines that will take a sql query, fill out a view. And then I have a routine that takes the view, and sends out the controls back to the table. (got VERY tired of writing code to pull data in, and out of views - so I have a general routine that does this for me. And I use near same code when using sqlite on a local database. And I have a table sync routine. (it syncs the local tables to sql server).

My most difficult issue? it was that of dealing with datetime values in sqlite, and then dealing with SQL server date time values. A real whack a mole process - but one I finally think I have a handle on. So, in place of a "edit" ability, I spent my time on building some code to push/pull a row of data in and out of views. Thus now I can just drop controls on a view - (set tag = column name from database), and my "load" and "save" code now is darn near the SAME for any new screen I have to edit data.

So, given time? Yes, I do plan and want to create a much better version of that "tester". There is a sqlite table browser utility floating around on B4a forums - might grab that and make it work with sql server and the jtc drivers.

And I been wanting to research further the use of the Microsoft jdbc drivers. I think they would handle the date/time issues a bit better - but the jts driver works - and that's about our best (only) choice right now if one wants to direct connect to sql server (and I do, and my data sync software works that way).

Regards,
Albert D. Kallal
Edmonton, Alberta Canada

I made a sample project and can verify that the update and insert function work the same as JSQL Lib... your project looks good..

 

[Nokia]

I also ran it through vpn tunnel on my phone...

 

[Nokia]

I would say that driver is working out good.. here is pic of the table view from this site and db setup in my app..

thanks Albert..

 

[Albert Kallal]

I would say that driver is working out good.. here is pic of the table view from this site and db setup in my app..

thanks Albert..

gee, I like that setup menu - I started using tabs (across the top, but the side setup - very nice!!
( am/was about to ask what control that is - but I'll download your sample first and take a "lookey" at it).

Busy, but I just finished up a sqlite to sql server set of sync routines. I don't handle deletes just yet , but the sync part works really quite nice.
This does require one to add one extra column for each PK, and one extra column to each table for FK value.

and I did adopt (use) sqlite client side triggers to automatic (always) update a column called LastUpdate (in that column I place the "now" time stamp value. But, my design at least handles the autonumber keys client side (sqlite) and pushing up the data to SQL server, and its own set of PK/FK autonumbers.
(PK = primary key, FK = foreign key).

R
Albert

 

[Nokia]

gee, I like that setup menu - I started using tabs (across the top, but the side setup - very nice!!
( am/was about to ask what control that is - but I'll download your sample first and take a "lookey" at it).

The sample is not the same as the images, I am using two scroll views and adding a views to them, left scroll view has a view with panel with labels, when you click a label, it loads a view to the scroll view to the right..

Sub lbNextGen_Click
    LoadTab(lbNextGen.Text)
End Sub

Sub LoadTab(text As String)
    
    svTabs.Panel.RemoveAllViews
    lbListValue.Text = text
    
    Select text
        Case "General"
            svTabs.Panel.LoadLayout("svGeneral")
            
            If Main.config.DoesConfigKeyExists("EnLog") Then
                If Main.config.getConfigKey("EnLog", "false") = "true" Or Main.config.getConfigKey("EnLog", "false") = True Then
                    chkOptGenEnLogging.Checked = True
                End If
            End If
            
        Case "Practice"
            svTabs.Panel.LoadLayout("svPractice")
            
            If Main.config.DoesConfigKeyExists("CompName") Then  txtOPCompName.Text =  Main.config.getConfigKey("CompName","")
            If Main.config.DoesConfigKeyExists("CompAddress") Then taOPCompAddress.text = Main.config.getConfigKey("CompAddress","")
            If Main.config.DoesConfigKeyExists("CompPhone") Then txtOPCompPhone.Text = Main.config.getConfigKey("CompPhone","")
            If Main.config.DoesConfigKeyExists("CompFax") Then txtOPCompFax.Text =  Main.config.getConfigKey("CompFax","")
            
            
        Case "Login"
            svTabs.Panel.LoadLayout("svLogin")
            
        Case "NextGen"
            svTabs.Panel.LoadLayout("svNextGen")
            svTabs.panel.Height = plNextGen.Height
            
        Case "License"
            svTabs.Panel.LoadLayout("svLicense")
            svTabs.panel.Height = plLicense.Height
        
        Case "About"
            svTabs.Panel.LoadLayout("svAbout")
    End Select
    
    btOptApply.Enabled = False
    
End Sub

 

[Albert Kallal]

As a follow up and to save some pain here?
The Microsoft driver as I stated does not work for me. It "might" work if your SQL server is 2008, but for versions beyond, the jdbc driver from Microsoft skimpy cannot be used.
You get this error:

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

So for SQL server that are newer versions? You BACK to having to use the jts drivers. So, my original point of not being able to use the Microsoft drivers holds true here. Its possible that one might get this to work if the server and network has "some" additional TLS 1.0 tweaks and settings - but for most part users will find that with SQL server beyond 2008, (2012 or 2016), then as noted, the Microsoft drivers will fail with above. ONLY practical work around I know of is to fall back and use the jts drivers.

R
Albert

 

[Nokia]

As a follow up and to save some pain here?
The Microsoft driver as I stated does not work for me. It "might" work if your SQL server is 2008, but for versions beyond, the jdbc driver from Microsoft skimpy cannot be used.
You get this error:

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

So for SQL server that are newer versions? You BACK to having to use the jts drivers. So, my original point of not being able to use the Microsoft drivers holds true here. Its possible that one might get this to work if the server and network has "some" additional TLS 1.0 tweaks and settings - but for most part users will find that with SQL server beyond 2008, (2012 or 2016), then as noted, the Microsoft drivers will fail with above. ONLY practical work around I know of is to fall back and use the jts drivers.

R
Albert

The jbdc driver seems to be working.. in the image with the table.. that's the driver I am using.. the one you suggested. I have updated my code and seems to work well.. with Select, insert, update and delete...