B4J Question jServer and MySQL

D

Declan

Well-Known Member
Licensed User
Longtime User
I have a jServer running - thanks to Erel for amazing support.
I receive the POST request from my remote device and am able to reply from jServer back to the device.
I must now query the MySQL database table for a value.
I have looked at the jServer example (with MySQL), but cannot get my head around the MySQL connection.

Can anyone point be in the direction of a simple jServer / MySQL query?
 
Sort by date Sort by votes
D

Declan

Well-Known Member
Licensed User
Longtime User
I need to query 1 field of the table as a string.
I then send this string back to the remote device with:
B4X: 
resp.Write("GOTCHA")
Where "GOTCHA" is the result of the query.

In a previous version, not jServer, I used RDC with:
B4X: 
sql.get_tracking=SELECT tracking FROM devices WHERE deviceid=?
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
Here is a minimal example.

Client:
B4X: 
Sub PostData
    Try
        Dim j As HttpJob
        j.Initialize("", Me)
        j.PostString("http://localhost:8080", "device_id=1")
        Wait For (j) JobDone(j As HttpJob)
        If j.Success Then
            Log( j.GetString )
        Else
            Log( j.ErrorMessage )
        End If
    Catch
        Log(LastException.Message)
    End Try
    j.Release
End Sub
 

Attachments

  • jServer.zip
    1.3 KB · Views: 131
Upvote 1
D

Declan

Well-Known Member
Licensed User
Longtime User
I am getting the following error on the server.
It relates to "Too Many Connections":

B4X: 
Tue Jul 18 05:07:28 SAST 2023 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Tue Jul 18 05:07:30 SAST 2023 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Tue Jul 18 05:07:30 SAST 2023 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Tue Jul 18 05:07:30 SAST 2023 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Tue Jul 18 05:07:31 SAST 2023 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
2023-07-18 05:07:31.786:WARN :cmvr.BasicResourcePool:C3P0PooledConnectionPoolManager[identityToken->2rw16qaxrr8zzgcp2p35|5d018107]-HelperThread-#1: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@6eb646cd -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
    at jdk.internal.reflect.GeneratedConstructorAccessor28.newInstance(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1031)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2189)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2220)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2015)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:768)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at jdk.internal.reflect.GeneratedConstructorAccessor19.newInstance(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:385)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:323)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
2023-07-18 05:07:31.786:WARN :cmvr.BasicResourcePool:C3P0PooledConnectionPoolManager[identityToken->2rw16qaxrr8zzgcp2p35|5d018107]-HelperThread-#1: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@7bc10d84 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
2023-07-18 05:07:31.787:WARN :cmvr.BasicResourcePool:C3P0PooledConnectionPoolManager[identityToken->2rw16qaxrr8zzgcp2p35|5d018107]-HelperThread-#2: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@3a1d7f27 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
    at jdk.internal.reflect.GeneratedConstructorAccessor28.newInstance(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1031)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2189)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2220)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2015)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:768)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at jdk.internal.reflect.GeneratedConstructorAccessor19.newInstance(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:385)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:323)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
2023-07-18 05:07:31.787:WARN :cmvr.BasicResourcePool:C3P0PooledConnectionPoolManager[identityToken->2rw16qaxrr8zzgcp2p35|5d018107]-HelperThread-#2: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@7bc10d84 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
2023-07-18 05:07:31.787:WARN :cmvr.BasicResourcePool:C3P0PooledConnectionPoolManager[identityToken->2rw16qaxrr8zzgcp2p35|5d018107]-HelperThread-#0: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@67fb0c0 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
    at jdk.internal.reflect.GeneratedConstructorAccessor28.newInstance(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1031)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2189)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2220)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2015)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:768)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at jdk.internal.reflect.GeneratedConstructorAccessor19.newInstance(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:385)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:323)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
2023-07-18 05:07:31.787:WARN :cmvr.BasicResourcePool:C3P0PooledConnectionPoolManager[identityToken->2rw16qaxrr8zzgcp2p35|5d018107]-HelperThread-#0: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@7bc10d84 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
 
Upvote 0

aeric

Expert
Licensed User
Longtime User

sql.SQLException: "Too many connections" with ConnectionPool library

Hi, Following this thread : http://www.b4x.com/android/forum/threads/sql-error-operation-not-allowed-after-resultset-closed.42516/ I have changed all my SQL queries and SQL connection to the Pool from ConnectionPool method. I now run into this problem: Jul 07, 2014 11:03:19 AM...
www.b4x.com

I usually set x to 100
 
Last edited:
Upvote 0

aeric

Expert
Licensed User
Longtime User
Declan said:
Where in my code do I put:
B4X: 
Dim jo As JavaObject = pool
jo.RunMethod("setMaxPoolSize", Array(100))
Click to expand...
You can put it after initialize the Pool.

B4X: 
Private Sub OpenConnection
    Pool.Initialize("com.mysql.jdbc.Driver", _
    "jdbc:mysql://localhost/test?characterEncoding=utf8", _
    "root", _
    "password")
    Dim jo As JavaObject = Pool
    jo.RunMethod("setMaxPoolSize", Array(100))
End Sub
 
Upvote 0
D

Declan

Well-Known Member
Licensed User
Longtime User
Thanks, that seems to have done the trick.
I will run it for a while and see how it goes.
 
Upvote 0
D

Declan

Well-Known Member
Licensed User
Longtime User
Unfortunately, it runs for a while on the server, then gives me the "Too many connections" error.
I have even increased the "setMaxPoolSize" to 1000.

My Code:
B4X: 
'Handler class
Sub Class_Globals
    Private SQL As SQL
    Private Pool As ConnectionPool
    Private Request As ServletRequest
    Private Response As ServletResponse
End Sub

Public Sub Initialize
    OpenConnection
End Sub

Sub Handle (req As ServletRequest, resp As ServletResponse)
    Request = req
    Response = resp
    
    Dim input() As Byte = Bit.InputStreamToBytes(req.InputStream)
    Dim msg As String = BytesToString(input, 0, input.Length, "utf8")
    
    Log("Got: " & msg)
    
    Dim myDeviceID As String
    
    If msg.IndexOf("TRACK") > -1 Then
        Log("Got Track")
        myDeviceID = msg.SubString(msg.Length - 5)
        Log ("Tracking ID: " & myDeviceID)
    End If
    
    
    Try
        SQL = GetConnection
        Dim device_id As String = myDeviceID
'        Log( device_id )
        Dim strSQL As String = "SELECT tracking FROM devices WHERE deviceid=?"
        Dim Res As ResultSet = SQL.ExecQuery2(strSQL, Array As String(device_id))
        If Res.NextRow Then
            Response.Write( Res.GetString2(0) )
        End If
        Res.Close
    Catch
        Log(LastException)
    End Try
    If SQL <> Null And SQL.IsInitialized Then SQL.Close
End Sub

Private Sub OpenConnection
    Pool.Initialize("com.mysql.jdbc.Driver", _
    "jdbc:mysql://localhost/invictus?characterEncoding=utf8", _
    "root", _
    "Declan2014!@#$%")
    
    Dim jo As JavaObject = Pool
    jo.RunMethod("setMaxPoolSize", Array(1000))
End Sub

Public Sub GetConnection As SQL
    Return Pool.GetConnection
End Sub
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Upvote 0

aeric

Expert
Licensed User
Longtime User
Sorry because I was creating a quick demo.
I am not clear what is the actual situation like how many connections are creating to the server.
Like mentioned by OliverA, study the code for jRDC2.
For my case, I am usually using my own Web API Template.
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
By the way, why are you sending bytes? Why not use PostString?
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Declan said:
still confused
Click to expand...

The super easy way would be to place both OpenConnection and GetConnection into Main. In Main, call OpenConnection once. In your handler code do
B4X: 
SQL = Main.GetConnection
to retrieve a connection from the pool.

Later on, if you want to, you can place the relevant pool code into another class (as jRDC2 does) or code module.
 
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Article
B4J Library jServer3
Replies
0
Views
4K
Erel
  • Article
B4J Library jServer v4.0 - Based on Jetty 11
2 3
Replies
41
Views
16K
aeric
  • Question
B4J Question jServer - jQueryElement??
Replies
2
Views
2K
ilan
  • Question
B4J Question [solved] Jserver+HTML+JS
Replies
11
Views
4K
udg
  • Article
B4J Tutorial [BANano+jSERVER=BANanoServer] Going full circle
Replies
16
Views
9K
LJG
L
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…