[RFE] Highly-available connection pooling.

[Patrick Eger <pe...@automotive.com>]

Please cc: me any comments, I haven't subscribe as yet.  Thanks.

Now on to my point:
I use connection pooling extensively, but the one advantage I have seen
of connection-per-request has been better error recovery.  Specifically,
when a connection dies (due to network or db issues), it becomes
necessary for the application to deal with error issues itself, this
tends to result in highly complex application code to deal with errors
(just look at the connection pool example.  sheesh!).  I have a
partially complete implementation of a high-availability connection pool
which I would be willing to support if there is any interest for it.
Right now the code is relatively clean, but quite limited (ie a
restricted JDBC implementation).  I have implemented wrapper classes
around the Connection, CallableStatement, ResultSet, and
ResultSetMetaData classes, which allow me to catch IOExceptions & other
connection errors and restart the pooled connections.  This allows me to
restart queries as well, in effect providing uninterrupted query service
if a database is bounced (provided the timeout does not expire or the
connection does not die in the middle of retrieving a result set).
Example of very typical usage in my app as follows:

<<snip>>
//Setup code (run on application startup)

//Implements connection pooling as well
ConnectionManager.addConnectionSpec("login",new
JDBCConnectionSpecification(
	properties.getProperty("login.db.connection_type"),
	properties.getProperty("login.db.jdbc_connection_string"),
	properties.getProperty("login.db.username"),
	properties.getProperty("login.db.password"),
	properties.getProperty("login.db.pool_min_connections"),
	properties.getProperty("login.db.pool_max_connections")));

<<snip>>
//Example of typical use from login code in my webapp
DBManagedConnection conn=null;
try {
	//Connections are requested by name
	conn = ConnectionManager.acquireConnection("login");

	//added a timeout parameter to prepareCall
	//parameter 3=true means that this query can be retried on error
	DBManagedCallableStatement stmt =
conn.prepareCall("login_pkg.login",120,true);
	stmt.setInt(1,user_name);
	stmt.setString(2,user_pass);
	stmt.registerOutParameter(3,Types.INTEGER);

	stmt.execute();

	login_success = stmt.getInt(3);

	if(login_success==LOGIN_SUCCESS)
		{
		DBManagedResultSet rs=stmt.getResultSet();

		while(rs.next())
			preferences.put(
				rs.getString("preference_type"),
				rs.getString("preference_value"));
		}

} catch(Exception e) {
	login_success = LOGIN_CONNECTION_ERROR;
} finally {
	ConnectionManager.releaseConnection(conn);
	//Note that this is the only required cleanup code.
}


In the above example, the exception will trigger if there was a SQL
execution error during the stmt.execute(), or the database is
unavailable for > 120 seconds, or a connection error occured when
cycling through the result-set (pretty sure this is an unsolvable case).
Otherwise, the query is guaranteed to complete.  Currently it works with
Microsoft SQL Server & Oracle 8i (both with the same set of features &
restrictions).  There are certainly many issues to work out, but it has
proven extremely useful for me and I thought it might be so for others.
Any interest in such a feature?

--Patrick

Re: [RFE] Highly-available connection pooling.

[Ted Husted <hu...@apache.org>]

The best place to bring this up would be Jakarta-Commons. There's a
connection pool product over there (DBCP), but I think it is still
released. And even it it was, the charter says we can have more than one
;-)

http://jakarta.apache.org/commons/charter.html

If the Commons ever ships a connection pool, I would suggest we
deprecate the Generic Connection Pool in Struts, and stay out of the
model business altogether ;-)

-- Ted Husted, Husted dot Com, Fairport NY USA.
-- Custom Software ~ Technical Services.
-- Tel +1 716 737-3463
-- http://www.husted.com/about/struts/


Patrick Eger wrote:
> 
> Please cc: me any comments, I haven't subscribe as yet.  Thanks.
> 
> Now on to my point:
> I use connection pooling extensively, but the one advantage I have seen
> of connection-per-request has been better error recovery.  Specifically,
> when a connection dies (due to network or db issues), it becomes
> necessary for the application to deal with error issues itself, this
> tends to result in highly complex application code to deal with errors
> (just look at the connection pool example.  sheesh!).  I have a
> partially complete implementation of a high-availability connection pool
> which I would be willing to support if there is any interest for it.
> Right now the code is relatively clean, but quite limited (ie a
> restricted JDBC implementation).  I have implemented wrapper classes
> around the Connection, CallableStatement, ResultSet, and
> ResultSetMetaData classes, which allow me to catch IOExceptions & other
> connection errors and restart the pooled connections.  This allows me to
> restart queries as well, in effect providing uninterrupted query service
> if a database is bounced (provided the timeout does not expire or the
> connection does not die in the middle of retrieving a result set).
> Example of very typical usage in my app as follows:
> 
> <<snip>>
> //Setup code (run on application startup)
> 
> //Implements connection pooling as well
> ConnectionManager.addConnectionSpec("login",new
> JDBCConnectionSpecification(
>         properties.getProperty("login.db.connection_type"),
>         properties.getProperty("login.db.jdbc_connection_string"),
>         properties.getProperty("login.db.username"),
>         properties.getProperty("login.db.password"),
>         properties.getProperty("login.db.pool_min_connections"),
>         properties.getProperty("login.db.pool_max_connections")));
> 
> <<snip>>
> //Example of typical use from login code in my webapp
> DBManagedConnection conn=null;
> try {
>         //Connections are requested by name
>         conn = ConnectionManager.acquireConnection("login");
> 
>         //added a timeout parameter to prepareCall
>         //parameter 3=true means that this query can be retried on error
>         DBManagedCallableStatement stmt =
> conn.prepareCall("login_pkg.login",120,true);
>         stmt.setInt(1,user_name);
>         stmt.setString(2,user_pass);
>         stmt.registerOutParameter(3,Types.INTEGER);
> 
>         stmt.execute();
> 
>         login_success = stmt.getInt(3);
> 
>         if(login_success==LOGIN_SUCCESS)
>                 {
>                 DBManagedResultSet rs=stmt.getResultSet();
> 
>                 while(rs.next())
>                         preferences.put(
>                                 rs.getString("preference_type"),
>                                 rs.getString("preference_value"));
>                 }
> 
> } catch(Exception e) {
>         login_success = LOGIN_CONNECTION_ERROR;
> } finally {
>         ConnectionManager.releaseConnection(conn);
>         //Note that this is the only required cleanup code.
> }
> 
> In the above example, the exception will trigger if there was a SQL
> execution error during the stmt.execute(), or the database is
> unavailable for > 120 seconds, or a connection error occured when
> cycling through the result-set (pretty sure this is an unsolvable case).
> Otherwise, the query is guaranteed to complete.  Currently it works with
> Microsoft SQL Server & Oracle 8i (both with the same set of features &
> restrictions).  There are certainly many issues to work out, but it has
> proven extremely useful for me and I thought it might be so for others.
> Any interest in such a feature?
> 
> --Patrick