Re: Database connection pooling

Richard Edwards wrote:

> Hi,
>
> I have an Enhydra server running an application that talks to a SQL Server
> database running on a seperate machine, using JSQLConnect.  All goes well
> until (for whatever reason) the database server has to restart, or
> connectivity is interrupted for some other reason.
>
> When the database comes back up, no db queries can be made by the
> application until it is restarted; any activity that attempts to query the
> database (eg, a user attempting to log in) results in the following error:
>
> DBComms::transmit exception:java.net.SocketException: Connection reset by
> peer: socket write error
>
> However, once the application (not the multiserver) is restarted, everything
> is fine again.  What could be causing this?  It seems that perhaps the
> multiserver is pooling database connections that have been closed rather
> than opening new connections.
If you use directly DBConnection objects, you must always explicitly 
call the DBConnection.handleException(SQLException) method when an 
SQLException to inform the DatabaseManager. Depending on the reason of 
the exception, the DatabaseManager will drop the connection from the 
pool (a SQL syntax error will not drop it, a network error will...).
Note : If you have to restart the database server, every DBConnection 
previously in the pool will be invalid, so an SQL exception will occur 
for each DBConnection, until every DBConnection is dropped.


Here is a sample code on how to safely use DBConnections :

   DBConnection dbconnection = null;
   try {
     try {
       dbconnection = 
myApplication.getDatabaseManager().allocateConnection(myDBName);
     }
     catch (DatabaseManagerException e) {
       // custom error handling
       throw new CustomException("Impossible to get a database connection to 
database " + myDBName);

     }

     // use the connection...
     // .....

   } catch (SQLException esql) {

       // handle the Exception at the DatabaseMabager level
        if (dbconnection !=null) {
         dbconnection.handleException(esql);
       }
        // Here some custom error handling...
       // ...
   } finally {
     if (dbconnection != null) dbconnection.release();
   }


Hope this helps...

--
Frederic Garzon  <Frederic.Garzon@xxxxxxxx>
---------------------------------------------
  o              51, rue de Verdun
 /      _ __ _   92158 Suresnes cedex
/ /\  /_  / /_   France
\/  \/_  / /_/   Tel. +33 (0) 1 41 44 85 45
 Inetsys S.A.    Fax  +33 (0) 1 46 97 20 10