B4J Question resourcepool.TimeoutException

uniplan

Active Member
Licensed User
Longtime User
I connect to a Firebird database with the following code:

B4X:
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.
 

uniplan

Active Member
Licensed User
Longtime User
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.
 
Upvote 0

uniplan

Active Member
Licensed User
Longtime User
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.
 
Upvote 0

uniplan

Active Member
Licensed User
Longtime User
I added the following code in the branch catch:
B4X:
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.
 
Upvote 0

uniplan

Active Member
Licensed User
Longtime User
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:

B4X:
dim sq as SQL = pool.GetConnection
 
Upvote 0

uniplan

Active Member
Licensed User
Longtime User
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?
 
Upvote 0
Top