B4J Question jServer and MySQL

[Declan]

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?

 

[aeric]

I have tried separate the class but in my local server it hits the error "Too many connections" on connection #53.
I check my MySQL server on Laragon. The default max connections is 151.

 

[aeric]

I tried to move the object to Main. It seems solve the issue. I will upload my project later.

 

[Declan]

I tried to move the object to Main. It seems solve the issue. I will upload my project later.

Guys, thanks for all the support

 

[aeric]

Here is a new update.

 

[Declan]

Many thanks,
I will try it in the morning.
I don't have access to my pc at the moment.

 

[EnriqueGonzalez]

I usually set x to 100

I understand that this value is only the MAX value, usually the pool will start at 5 connections and increase when necessary.

Too Many Connections

But that value has nothing to do with this error. this error comes from the DB so you have to look for the properties of the DB to increase this value.

BUT BUT before doing that, it is very rare that you will need the 100 connections. You may not be closing the connection
You have to close it like this when you stop using it (ensure that when the server responses you already closed the connection)

sql.close

to allow the server to recycle that connection. Believe me, a pool with a limit of 5 connections will allow you to serve hundred of customers. this is because the pool is very fast on making the connection when needed.

Also, because the error is DB related and not from the pool, take into consideration that more than one app (for example, you on your dev pc, and then production server) are requesting the connections. so even if you have one single connection to the DB on the production server, you may not be able to connect if for example you are connecting from your pc with a debugging app.[/code]

 

[Declan]

Here is a new update.

Thanks, This is running great

 

[Declan]

But that value has nothing to do with this error. this error comes from the DB so you have to look for the properties of the DB to increase this value.

BUT BUT before doing that, it is very rare that you will need the 100 connections. You may not be closing the connection
You have to close it like this when you stop using it (ensure that when the server responses you already closed the connection)

Okay - where to from here...
I will have about 500 remote devices connecting periodically.

 

[aeric]

I understand that this value is only the MAX value, usually the pool will start at 5 connections and increase when necessary.


But that value has nothing to do with this error. this error comes from the DB so you have to look for the properties of the DB to increase this value.

BUT BUT before doing that, it is very rare that you will need the 100 connections. You may not be closing the connection
You have to close it like this when you stop using it (ensure that when the server responses you already closed the connection)

sql.close

to allow the server to recycle that connection. Believe me, a pool with a limit of 5 connections will allow you to serve hundred of customers. this is because the pool is very fast on making the connection when needed.

Also, because the error is DB related and not from the pool, take into consideration that more than one app (for example, you on your dev pc, and then production server) are requesting the connections. so even if you have one single connection to the DB on the production server, you may not be able to connect if for example you are connecting from your pc with a debugging app.[/code]

The issue with my previous code snippet is, it was creating a new connection with every request to the handler. When I move the object of DataConnector class to Main, only one object of the Pool is created when the server starts as Public variable and it only stays as a single connection to the database (I guess). I made a test client to send request to the server with every 100 milliseconds using a timer and it run passing 200 times. Previously it stopped at 50 times and then timed out.

 

[Declan]

The issue with my previous code snippet is, it was creating a new connection with every request to the handler. When I move the object of DataConnector class to Main, only one object of the Pool is created when the server starts as Public variable and it only stays as a single connection to the database (I guess). I made a test client to send request to the server with every 100 milliseconds using a timer and it run passing 200 times. Previously it stopped at 50 times and then timed out.

Awesome,
My remote devices send a request to the server every ~1 minute.
I am currently only running 1 remote device and it is running great.
I will add more remote devices and see how the server performs.
Once again, many thanks

 

[alwaysbusy]

Here is an alternative example using my wrap around the HikariCP pool (usage is the same, but it is many times faster, has many tuning options and it can return some interesting statistics of the usage of the pool). We use this in our production apps and it helps trying to find out if we don't have some leak (e.g. because of a connection we didn't close).

The HikariCP library will also report if a leak is detected.

With the settings in the demo, many thousands of handheld scanners periodically connect to our server without problems. A 100 connections is even to high, it could easily handle them with a lot less.

Example of such a statistic (runs every 5 seconds in a BackgroundWorker: the class InfoConnections). In our case we log it in a database so we can track it later if something starts to go wrong.

2023-07-19T08:44:27
Memory Used: 41.829MB
Allocated Free Memory: 982.171MB
Active Connections: 2
Threads Waiting For Connection: 0
Currently Reserved connections: 100
Total Available Connections: 98
----------------------------------------------

Unzip and copy all the files in the ABHikariCPLibrary.zip file to your B4J Additional Libraries folder.

You will have to make some changes in the demo to match your database settings (url, login, password).
After running the demo, open browser and navigate to: 127.0.0.1:51042/demo?device_id=10

Alwaysbusy

 

[Declan]

Great, thank you