I am doing it like the following:
Sub Process_Globals
Dim pool As ConnectionPool
Dim con As Int = 0
End Sub
Sub GetDeviceStatus(Account As String, device As String) As ResumableSub
Dim returnValue As String
pool.GetConnectionAsync("SQL1")
con = con + 1
wait for SQL1_ConnectionReady (Success As Boolean, sql As SQL)
If Success Then
Try
'work with sql
Dim sf As Object = sql.ExecQueryAsync("SQL", "SELECT Value FROM `StatusV2` WHERE `Location` = ?", Array As Object (Account & "_" & device))
Wait For (sf) SQL_QueryComplete (Success As Boolean, rs As ResultSet)
If Success Then
Log("ExecQueryAsync Success = " & Success)
If rs.NextRow Then
returnValue = rs.GetString("Value")
End If
End If
Catch
Log(LastException.Message)
End Try
End If
sql.Close
rs.Close
con = con - 1
Log("con = " & con)
' will return value
Return returnValue
End Sub
I am logging how many connections are open (well I think that is one way in detecting how many connections are open) based on the value of con.
When the connection is being made it adds one to the con int value, and once it closes the connection it will minus one from the con int value.
This keeps increasing and does't seem to ever decrease.
It was locking up and showing an error. The value of 'con' was 15:
java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:690)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
at anywheresoftware.b4j.object.ConnectionPool.GetConnection(ConnectionPool.java:45)
at anywheresoftware.b4j.object.ConnectionPool$1.call(ConnectionPool.java:57)
at anywheresoftware.b4j.object.ConnectionPool$1.call(ConnectionPool.java:1)
at anywheresoftware.b4a.BA$4.run(BA.java:272)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@721e0f4f -- timeout at awaitAvailable()
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1467)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685)
... 10 more
java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:690)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
at anywheresoftware.b4j.object.ConnectionPool.GetConnection(ConnectionPool.java:45)
at anywheresoftware.b4j.object.ConnectionPool$1.call(ConnectionPool.java:57)
at anywheresoftware.b4j.object.ConnectionPool$1.call(ConnectionPool.java:1)
at anywheresoftware.b4a.BA$4.run(BA.java:272)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@721e0f4f -- timeout at awaitAvailable()
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1467)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685)
... 10 more
I then increased the max pool size after I initialise the the pool like:
pool.Initialize("com.mysql.jdbc.Driver","jdbc:mysql://mysql_ip_address_here/cloudserver?characterEncoding=utf8","username","password")
Dim jo As JavaObject = pool
jo.RunMethod("setMaxPoolSize", Array(5000))
Be increasing the connection pool size, it seems to fix the issue where it locked up after it got to 15, but it keeps increasing, and I assume it will crash again once it gets to 5000.
I tried in debug mode and release mode, but both has the same result.
I am now doing this correct or am I still doing it wrong ?