B4J Question resourcepool.TimeoutException

[uniplan]

I connect to a Firebird database with the following code:

Sub Process_Globals
    Dim pool As ConnectionPool
    Dim str_sql As String
    Dim esito As String
   
   
End Sub

.......

Public Sub inizializza_pool As String
Try
    If pool.IsInitialized = True Then
        Return "OK"
    Else
        Try
            pool.ClosePool
        Catch
            fp.scrivi_log(LastException.Message,"gestione_fdb_pool:inizializza_pool:ClosePool")
        End Try
   
            pool.Initialize("org.firebirdsql.jdbc.FBDriver",strconn_fbd,user_fdb,pwd_fdb)
               
            If pool.IsInitialized=True Then
                Return "OK"
            Else
                Return "KO"
            End If
    End If
Catch
    Return "KO: " & LastException.Message   
End Try

End Sub
...................................................

Private Sub verifica_presenze_pool
    Try

......................
        inizializza_pool

        Dim sq As SQL = pool.GetConnection
   
       
        'estraggo tutte le letture non trasferite con id_dipendente > 0
        str_sql = "select * from ibutton where trasferito = 'N' and id_dipendente > 0 order by id"
        rst = connessione_db.sql_pg.ExecQuery(str_sql)
       
        Do While rst.NextRow=True
       
            id_button = rst.GetInt("id")
            ibutton_rom = rst.GetString("ibutton_rom")
            i_timestamp = rst.GetString("i_timestamp")
            direction = rst.GetString("direction")
            id_dipendente = rst.GetInt("id_dipendente")
            id_azienda = rst.GetInt("id_azienda")
            trasf_timestamp = rst.GetString("trasf_timestamp")

            Dim data_lettura As String 
            Dim ora_lettura As String
       
            data_lettura = i_timestamp.SubString2(0,10)
            ora_lettura = i_timestamp.SubString2(11,16)   




            '----------------------------------
            'inserisco in presenze
            '-----------------------------------
            str_sql = "insert into PRESENZE (ID_AZIENDA,ID_DIP,TIPO,DATA,ORA,IBUTTON,"
            str_sql = str_sql & " TRAS,ID_ORARIO,ID_IBEM,ID_TRASF_IBEM,ID_LETTURA) "
            str_sql = str_sql & " values "
            str_sql = str_sql & "(?,?,?,?,?,?,'S',0,?,?,?) "
            sq.AddNonQueryToBatch(str_sql,Array As Object(id_azienda, _
            id_dipendente,direction,data_lettura,ora_lettura, _
            ibutton_rom,dichiarazioni.ID_IBEM,ID_TRASF_IBEM,id_button))

            '-----------------------------------
            'inserisco in presenza ibem
            '-----------------------------------
            str_sql = "insert into PRESENZE_IBEM (ID_AZIENDA,ID_DIP,TIPO,DATA,ORA,ID_IBEM,"
            str_sql = str_sql & "ID_TRASF_IBEM,ID_LETTURA) "
            str_sql = str_sql & " values "
            str_sql = str_sql & "(?,?,?,?,?,?,?,?) "
            sq.AddNonQueryToBatch(str_sql,Array As Object(id_azienda,id_dipendente,direction, _
            data_lettura,ora_lettura,dichiarazioni.ID_IBEM,ID_TRASF_IBEM,id_button))
       
        Loop
        rst.Close

        sq.ExecNonQueryBatch("inserimento_complessivo_presenze")

   
    Catch
       
        fp.scrivi_log(LastException.Message,"gestione_fdb_pool:verifica_presenze_pool")
       
    End Try
End Sub

Sometimes I get the following error:

com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@15f7cbe -- timeout at awaitAvailable()

What could be the cause?

Thanks.

Regards.

 

[Erel]

Does it happen when you call pool.GetConnection? Does it actually throw an error or only a message in the logs?

 

[uniplan]

Does it happen when you call pool.GetConnection? Does it actually throw an error or only a message in the logs?

I think that happens when I call pool.GetConnection. I can not be sure because it does not always happen. I added a "Try ... Catch" and I find the error in the log.
It is a function that I call periodically with a timer. Once it goes wrong, in all subsequent calls the error remains.

 

[Erel]

Maybe there is a connection problem between the computer and the database server?

 

[uniplan]

Do you think about a temporary problem of connection?
Keep in mind that the error is repeated for each call, but if I restart the webapp all back to work properly.

 

[Erel]

I'm not sure what causes this problem. You can try to close the connection pool when this error happens and initialize it again.

 

[uniplan]

I added the following code in the branch catch:

Catch
      try
          pool.ClosePool
      catch
         log(lastexception.message)
      end try
end try

But now I get the following error:

java.sql.SQLException: com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 20000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> z8kfsx9c1xp34mp11h3c4j|cdc8ea, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> org.firebirdsql.jdbc.FBDriver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> z8kfsx9c1xp34mp11h3c4j|cdc8ea, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:firebirdsql:172.16.1.46/3050:IBEM?encoding=ICODE_FSS, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 150, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, properties -> {user=****, password=****}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> true, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ] has been closed() -- you can no longer use it.

 

[Erel]

I don't recommend you to catch all those exceptions. You might lose important information.

What happens after you close the pool and then open it again? Are you able to get a connection?

 

[uniplan]

What happens after you close the pool and then open it again? Are you able to get a connection?

No I am no longer able to get a connection.
I checked and it faults just when I call:

dim sq as SQL = pool.GetConnection

 

[Erel]

Make sure to use the latest jdbc FireBird driver. It looks like a problem in the driver.

The connection pool can work months without interruption (for example: http://b4x.com:51042).

 

[uniplan]

Make sure to use the latest jdbc FireBird driver. It looks like a problem in the driver.

The connection pool can work months without interruption (for example: http://b4x.com:51042).

I was using 2.2.8 Jaybird Jaybird now I downloaded 2.2.9.
I begin to use it and let you know.
You know, in addition to Jaybird there are other drivers java for Firebird?

 

[Erel]

Never worked with Firebird database.