B4J Question ResultSet Freeze application with MySQL and MSSQL

Swissmade

Well-Known Member
Licensed User
Longtime User
Hi,
I have a app where I have a connection to a Server.
This application is making a Backup of the data in real-time.

Now the problem.
When I lose the connection to the server and the Application is busy with Backup the Resultset of the Server-connection is freeze the application.
Is there a option to fetch this as a error. This way I can handle this.
Try Catch Block is not helping the application is frozen.

I hope somebody has any idea for this.
 

keirS

Well-Known Member
Licensed User
Longtime User
A couple of subs which may help you.

B4X:
Sub IsValidConnection(SQLConnection As SQL) As Boolean
Dim JO As JavaObject
Dim TimeoutSecs As Int = 1
JO = SQLConnection
JO = JO.GetFieldJO("connection")
Return JO.RunMethod("isValid",Array As Object (TimeoutSecs))
End Sub

Sub IsConnectionClosed(SQLConnection As SQL) As Boolean
    Dim JO As JavaObject
    JO = SQLConnection
    JO = JO.GetFieldJO("connection")
    Return JO.RunMethod("isClosed",Null)
  

End Sub

IsValid will return false if there is an issue with the connection.
IsConnectionClosed may return True if there have been exceptions.

This sub may get the network connection time out in milliseconds. It depends on your driver. The MySQL Driver does not implement it and it always returns zero.

B4X:
Sub GetConnectionNetWorkTimeout(SQLConnection As SQL) As Int
    Dim JO As JavaObject
    JO = SQLConnection
    JO = JO.GetFieldJO("connection")
    Return JO.RunMethod("getNetworkTimeout",Null)
End Sub
 
Upvote 0

Swissmade

Well-Known Member
Licensed User
Longtime User
Many thanks Keirs,
Will try.;);)
 
Upvote 0

keirS

Well-Known Member
Licensed User
Longtime User
No problem. Thinking about it here are a couple more you could try.

B4X:
Sub GetConnectionWarnings(SQLConnection As SQL)
    Dim JO As JavaObject
    JO = SQLConnection
    JO = JO.GetFieldJO("connection")
    JO = JO.RunMethodJO("getWarnings",Null)
    Do While JO.IsInitialized
        Log(JO.RunMethod("toString",Null))
        JO = JO.RunMethodJO("getNextWarning",Null)
    Loop

End Sub

Sub GetResultSetWarnings(R As ResultSet)
    Dim JO As JavaObject
    JO = R
    JO = JO.RunMethodJO("getWarnings",Null)
Do While JO.IsInitialized
    Log(JO.RunMethod("toString",Null))
    JO = JO.RunMethodJO("getNextWarning",Null)
Loop

End Sub

These should output any warnings from the Connection and ResultSet objects to the log. Again it's dependent on the driver if any warnings are generated.
 
Upvote 0

Swissmade

Well-Known Member
Licensed User
Longtime User
I will try tomorrow
The problem is that the Resultset has info and the connection get broken.
like
B4X:
str = RS.getstring2(Index)
This happening in Debug and Release mode.
Thanks for help.
 
Upvote 0

Swissmade

Well-Known Member
Licensed User
Longtime User
Are you using the asynchronous methods?

[B4X] SQL with Wait For

Note that you can use ConnectionPool from jServer library to connect to remote databases. It will take care of managing the connections.
Hi Erel,
Yes it is asynchronous
Most of the time it is happening when I do something with the Resultset.
I also have a Ping to check if the Connection is still here but you never know when the Network connection get lost and where the program is at that moment.
 
Last edited:
Upvote 0

Swissmade

Well-Known Member
Licensed User
Longtime User
I have two connection The Server and the BackupServer.
The task is running one table at the time. Time between for the next table to process is about 1 Minute.
In this Minute checks will be done like have connection and some other thinks.
Before the next table is processed a Ping is done to the Network.
If we have connect it will process otherwise it stops and some other process will go on to check if we have the connection back and we start over again.
Wait For is not helping in this
I try to check the Resultset for changes with.

B4X:
Sub IsValidConnection(RS As ResultSet) As Boolean
Try 
    Dim JO As JavaObject
    Dim TimeoutSecs As Int = 1
    JO = RS
'    JO = JO.GetFieldJO("connection")
'    JO = JO.GetFieldJO("")
Return JO.RunMethod("isClosed", Null) 'Array As Object (TimeoutSecs))
Catch
    Log("Error in Is ValidConnection")
    End Try
End Sub
Somehow something has to change in this resultset if the Connection is lost.
 
Upvote 0

Swissmade

Well-Known Member
Licensed User
Longtime User
I get the idea, if the Connection is lost, the Resultset is still busy to empty itself and, is it empty, then try to get the next rows it stops.
I search for some timeout
 
Upvote 0

Swissmade

Well-Known Member
Licensed User
Longtime User
Some more to explain,
It happens in the part where I compare the Records.
Many logs in this part to find the problem.
This is testing all rows to see if we have changes.
Also I use a loop for this because every table is different.
Even a RS.Close can freeze the application.
B4X:
    If RStmp.NextRow = True Then
        Dim tmpColCount As Int = RS.ColumnCount
        For RSCounter = 1 To tmpColCount-1
            If Main.ConnectionLost = True Then
                RStmp.Close
                Exit
            End If
            HaveChange = False
'            Log("Have Change")
Try         
            Data1 = RStmp.GetString2(RSCounter-1)
            Log("Get Data1")
            Log("Test field " & IsValidConnection(RS, "isClosed"))
            Log("ColomName " & RS.GetColumnName(RSCounter))
             Log("Columncount Resultsset " & RS.ColumnCount)
             Log("RSCounter " & RSCounter)
            Dim tmpstr As String = RS.GetString2(RSCounter) 'Fout here it stops
            Log("TmpStr " & tmpstr)
            Data2 = tmpstr
            Log("Get Data2")
Catch
        Log("We have a error here")
        Return
End Try         
#IF DEBUG          
            Log("Data1 Col Name " & RStmp.GetColumnName(RSCounter-1) & " Data2 Col Name " & RS.GetColumnName(RSCounter))
#END IF         
            If Data1 = "null" Or Data1.Length = 0 Then
                Data1 = ""
            End If
            If Data2 = "null" Or Data2.Length = 0 Then
                Data2 = ""
            End If

            If Data1 <> Data2 Then
                HaveChange = True
                Exit
            End If
            Functions.WaitFor(1)
            Log("Create Query Counter = " & RSCounter)
        Next
 
Upvote 0

Swissmade

Well-Known Member
Licensed User
Longtime User
The Wait For I use but have not yet try ConnectionPool.
I don't know how exactly I have to use this yet.
I have solve this temporarily with a ping on a place where I don't like to have.
Many thanks for helping me out here.
 
Upvote 0

Swissmade

Well-Known Member
Licensed User
Longtime User
In the meantime I have found the exception created.
com.microsoft.sqlserver.jdbc.SQLServerException: Software caused connection abort: recv failed
I get this when I recover the application and set some breakpoins.
Somehow this is not handled.
I think MySQL will be the same Error.
 
Upvote 0

OliverA

Expert
Licensed User
Looking around (Google) I found four cases with freezing while reading DB's with getstring. One dealt with MSSQL, one with an unkown DB accessed via the JDBC/ODBC bridge (that must be fun) and two dealing with MySQL (here and here). The two MySQL had a solution and it dealt with the database connector used. Therefore, what #AdditionalJar are you using for your DB(s) and have you tried a different one?
 
Upvote 0

keirS

Well-Known Member
Licensed User
Longtime User
Strange. I would expect this sub to return false in that instance.

B4X:
Sub IsValidConnection(SQLConnection As SQL) As Boolean
[INDENT]Dim JO AsJavaObject
Dim TimeoutSecs As Int = 1
JO = SQLConnection
JO = JO.GetFieldJO("connection")
Return JO.RunMethod("isValid",ArrayAs Object (TimeoutSecs))[/INDENT]
End Sub

You can set the time out to zero if you like as the the test is usually "SELECT -1" which should be pretty instantaneous.
 
Upvote 0

OliverA

Expert
Licensed User
com.microsoft.sqlserver.jdbc.SQLServerException: Software caused connection abort: recv failed
An old post suggests that this may be caused by
  1. Faulty networking - be it faulty network hardware (network cards, switches, etc.) or software (drivers)
  2. JVM issues, which may relate to my previous post. The JDBC driver may not match the Java version used (thinking out loud)
  3. Connection dropped due to excessive connections (and here they connection pooling suggested by @Erel would help)
 
Upvote 0

OliverA

Expert
Licensed User
This application is making a Backup of the data in real-time
Another 5 cents: Have you ever thought that this may stress out your main server? I'm gonna guess (probably should not), that the RS is a result set for the main server and the RStmp is a result set for your backup server. Right now you seem to indicate that the error always happens on RS. Maybe you're just asking too much of your SQL server, especially if this continually runs during production and the server handles lots of requests for normal duties (besides this synchronization).
 
Upvote 0

Swissmade

Well-Known Member
Licensed User
Longtime User
Have you ever thought that this may stress out your main server
Hi OliverA
No this is not stressing out the Server there is time en of between processes.
On the Main Server is a Access Control application (Handling Doors with RFID) running what is handling two locations.
The second location connected with VPN can sometimes lose the connection.
Therefore I have build this Backup software.
If the connection is lost the software controls that the Access Control application is starting up local So door can still go open after a small delay.
If the connection is back the local open application is closed and the backup process is start up again.

I go to try the Connection-pool.

Just see how this is working.
Thanks guys for you help.
 
Upvote 0
Top