B4J Question [SOLVED] How to keep MySQL connection alive?

[jroriz]

If I disable Timer2, WriteData_tick fails and returns the following error:

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
The last packet successfully received from the server was 119.831 milliseconds ago.  The last packet sent successfully to the server was 3 milliseconds ago.
bla bla bla...

My WebHost is Hostgator, I did not find any config in phpMyAdmin related to connection timeout or something like that.

Sub Process_Globals
    Private fx As JFX
    Dim Hostgator As SQL
    Dim Timer1, Timer2 As Timer
End Sub

Sub AppStart (Form1 As Form, Args() As String)
   
    Timer1.Initialize("WriteData", 15*60*1000)    ' 15 minutes
    Timer2.Initialize("KeepDBAlive", 30*1000)    ' 30 seconds

    Try
        Hostgator.Initialize("com.mysql.cj.jdbc.Driver", "jdbc:mysql://myserver.com/mydatabase?user=username&password=pwd")
    Catch
        Log(LastException.Message)
        ExitApplication
    End Try

    Timer1.Enabled = True
    Timer2.Enabled = True    ' disabel timer2 and WriteData fails
End Sub

Sub KeepDBAlive_tick

    If Hostgator.IsInitialized Then
        Try
            Dim Cursor As ResultSet
            Cursor = Hostgator.ExecQuery("SELECT 1")
            Cursor.Close
        Catch
            Log(LastException)
        End Try
    End If

End Sub

Sub WriteData_tick
    ' write some stuff to database (INSERT INTO)
End Sub

 

[Peter Simpson]

There's no reason to keep it alive. Just check if it's open, if it's not open you open a connection and run your query, if it's already open just run the query.

 

[jroriz]

There's no reason to keep it alive. Just check if it's open, if it's not open you open a connection and run your query, if it's already open just run the query.

Thank you for the answer.
But as I said, if timer2 is disabled, and a few minutes go by without the program making any access to the database, the error listed above occurs, and the attempt to write the record fails.
In addition, Hostgator.IsInitialized returns true before the error.

 

[Erel]

There's no reason to keep it alive.

+1

Use ConnectionPool. This is the best option when connecting to remote databases.