After working several weeks on a new Xojo-based web application I regularily stumble over the lack of a connection pool using the built-in database adapters. While the general recommendation here in the forum is “Use one separate connection per session” this still doesn’t solve the issue that web applications can request many different data sources at the same time.
This leads to having a separate connection for every query.
So I’m thinking about building a generic SQL Connection Pool using the Database interface from Xojo. My question is: does anyone already have done something like this?
The module in the project ARGen generates uses connection pooling on Web. It serves one connection per session and has a backup connection for the App (things without a session context will use this).
It is not part of the ActiveRecord project, you have to actually generate something from ARGen to get it.
Hi Tim, thanks for your reply!
My desired solution would be to use a SQLConnectionPool class, pass it the connection credentials and
- MaxConnections
- MinConnections
- IdleTimeout
- WaitTimeout
The ConnectionPool would provide a Database interface so that it can be used like MySQLDatabase etc.
On instantiation the ConnectionPool would initiate “MinConnections” and have them in an “idle array”.
For every new query the ConnectionPool would look for an available (free/unused) connection in the idle array and use that. If none is available, it would create a new connection unless MaxConnections is reached. Then it would wait for “WaitTimeout” seconds for a connection to become available. Finally it would Raise a “ConnectionTimeout” event to indicate that the connection pool is full.
Every connection in the idle array is kept open until IdleTimeout is reached, then it will dispose of the connection until MinConnections is reached.
This is how connection pools work with popular libraries. One advantage is that one can predict the total number of connections used by an application independent of the number of sessions.
I’ll probably take up the challenge during the weekend and share my progress here in the forum.