B4J Question jServer and MySQL

Declan · Jul 17, 2023

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 · Jul 17, 2023

Declan said:
Many, Many Thanks.
Works GREAT

You may mark as answer.

aeric · Jul 18, 2023

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 · Jul 18, 2023

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

Declan · Jul 18, 2023

aeric said:
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 · Jul 18, 2023

Here is a new update.

Declan · Jul 18, 2023

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

EnriqueGonzalez · Jul 19, 2023

aeric said:
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.

Declan said:
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)

B4X:

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 · Jul 19, 2023

aeric said:
Here is a new update.

Thanks, This is running great

Declan · Jul 19, 2023

EnriqueGonzalez said:
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 · Jul 19, 2023

EnriqueGonzalez said:
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)

B4X:

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 · Jul 19, 2023

aeric said:
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 · Jul 19, 2023

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.

B4X:

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 · Jul 21, 2023

Great, thank you

B4J Question jServer and MySQL

Declan

Well-Known Member

aeric

aeric

Expert

aeric

Expert

Declan

Well-Known Member

aeric

Expert

Attachments

Declan

Well-Known Member

EnriqueGonzalez

Well-Known Member

Declan

Well-Known Member

Declan

Well-Known Member

aeric

Expert

Declan

Well-Known Member

alwaysbusy

Expert

Attachments

Declan

Well-Known Member

Similar Threads

B4J Question jServer and MySQL

Well-Known Member

Expert

Expert

Well-Known Member

Expert

Attachments

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Expert

Attachments

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency