CSMLDB connection notes
From: Sebastian Sosnik
In general it is recommended to:
1. Close connection after a "full" database operations.
2. Do not close connection a "single" database operation, e.g. a database query done in a loop.
Creating/closing the connections takes about 0.01 second. This means 100 connections can be reopened in one second. For a "full database operation" the user will not notice the delay, however if he operation will be executed in a loop, then the delay can become a problem.
Why to close connection?
Previously CSMLDB never closed connections. The connections were only closed on application exit. This resulted in keeping many "outdated" connections, which consumes memory and resource. This is not good, e.g. for the CI Online server, each CSMLDB query creates a new connection to the Postgres server. To remove them Tomcat must be restarted!
As before, the connection object is kept in the CSMLDBModel class - fConnection. This connection can be closed by CSMLDBModel.closeConnection(). This function is called after each complex db operation, such as create database, populate database, complex select or insert To avoid problems the CSMLDBModel.getConnection() method was reimplemented: it checks whether the current connection was closed. If it was closed it creates a new connection, otherwise it returns the current one.
All occurrences of direct access to fConnection object were replaced by getConnection().
After this change the developer should use CSMLDBModel as follows:
- Always call getConnection() method instead of direct access to fConnection.
- Do not store the connection object persistently in any other class. It can be closed!
- In case of problems with closed connection call the CSMLDBModel.getConnection() to get a proper connection