B4J Question JDBC MySQL detect if lost connection

[aaronk]

Hi,

I am connecting to a remote SQL database, and I followed the tutorial: https://www.b4x.com/android/forum/threads/sql-tutorial.35185/#content

I have everything working, but is there a way to detect if the connection drops from the remote database?

I will be reading and writing to the database all the time, and want to detect if the connection drops so I can reconnect to it before sending the SQL query to database.

I understand that there is now jRDC2 but I now have everything working as per the above link, and won't want to change much code but will look at jRDC2 at a later time. Just want to know if there is a way to detect if the connection has dropped or not when using JBDC.

 

[Erel]

Best option is to let ConnectionPool take care of it: https://www.b4x.com/android/forum/threads/107776/#content

 

[aaronk]

Best option is to let ConnectionPool take care of it

I just tried this, and have a question in regards to it.

Using the following code it connects to the MySQL database:

Dim sql1 As SQL
sql1.InitializeAsync("sql1", "com.mysql.jdbc.Driver","jdbc:mysql://IP_address_here/database?characterEncoding=utf8","username","password")
   
    wait for sql1_ConnectionReady (Success As Boolean)
   
    If Success = False Then
            Log(LastException)
        Else
            Log("ready = OK")
    End If

The above code works without the connectionPool.

I then used the code below to connect with the ConnectionPool like:
(Hopefully I have done this correct)

Dim sql1 As SQL
    Dim pool As ConnectionPool
    
    pool.Initialize("com.mysql.jdbc.Driver","jdbc:mysql://IP_address_here/database?characterEncoding=utf8","username","password")
    sql1 = pool.GetConnection
    
    pool.GetConnectionAsync("IsConnected")
    
Private Sub IsConnected_ConnectionReady (Success As Boolean, SQL As SQL)
    Log("SQL Connect = " & Success)
End Sub

This allows me to connect to the MySQL database using the ConnectionPool.

Few questions to ask:
1. When the B4J app loses connection to the MySQL, does the ConnectionReady event get triggered or do I need to run the code:

pool.GetConnectionAsync("IsConnected")

and then check the ConnectionReady event for the status? I am guessing I would need to use a Timer and run this code every minute to check the status.

2. Does the ConnectionPool (Pool) lose connection to the database, or is it the SQL (sql1) the one that loses connection when connecting to the remote database? I am guessing it's the pool that loses connection.

 

[Erel]

The code is wrong.
Correct way to work with connection pool: https://www.b4x.com/android/forum/t...abase-connection-is-broken.113110/post-705801

 

[aaronk]

Just so I understand, every time I want to send a SQL query to the database I need to re-dim the SQL, run the query and then close the SQL each time?

So, I would run this code once:

Sub Process_Globals
    Dim pool As ConnectionPool
End Sub

Sub RunOnce
   Dim pool As ConnectionPool
   pool.Initialize("com.mysql.jdbc.Driver","jdbc:mysql://IP_address_here/database?characterEncoding=utf8","username","password")
End Sub

Then create a function, and run the following code each time I want to send a query to the MySQL database:

Sub RunQuery
   Dim sql As SQL = pool.GetConnection
   Try
      'work with sql
      sql.ExecNonQuery("CREATE TABLE table1 (col1 TEXT , col2 INTEGER, col3 INTEGER)")
   Catch
       'handle failure
   End Try
   sql.Close
End Sub

Would the above be correct, or am I still looking at this wrong ?

 

[Erel]

This code is correct. The way it works the connection pool is responsible for giving you a working connection each time.