Hello,
Maybe others also encountered similar problems like us
so I decided to proclaim the issues we've experinced using
DBCP
DataSource factory and MSSQL2000 JDBC driver. I've
implemented
a custom DataSource factory based on DBCP's
BasicDataSourceFactory with 2 additional advantages:
- allow to override default configuration options
like username by the environment parameter of
getObjectInstance.
- it perserves one BasicDataSource instance per user in a
Map.
if username has been omitted it falls back to the
one secified in the config file (server.xml in Tomcat).
Let's look an example for usage:
Hashtable env = new Hashtable();
env.put("username", "Duke");
env.put("password", "Earl");
InitialContext context = new InitialContext(env);
DataSource dataSource =
(DataSource) PortableRemoteObject.narrow
(context.lookup(jndiName), javax.sql.DataSource.class);
That's it. I've tested the class and it worked fine
for a couple of minutes. But for a longer testing
(eg. more than about 5 minutes) it produces strange results
on MSSQL:
- This issue is same on Tyrex:
MSSQL seems to close connection in arbitrary moments
irrespectively of Query time-out setting
(Enterprise Manager select server >>
Properties >> SQL Server Properties >> Connections >>
Remote Server Connections >> Query time-out) and at
last end up
the JDBC driver throws exception:
Caused by: java.sql.SQLException: [Microsoft][SQLServer
2000 Driver for JDBC]
Object has been closed.
at
com.microsoft.jdbc.base.BaseExceptions.createException
(Unknown
Source)
at
com.microsoft.jdbc.base.BaseExceptions.getException(Unknown
Source)
at
com.microsoft.jdbc.base.BaseResultSet.validateClosedState
(Unknown
Source)
at com.microsoft.jdbc.base.BaseResultSet.wasNull
(Unknown Source)
at
org.apache.commons.dbcp.DelegatingResultSet.wasNull
(DelegatingResultSet.java:136)
- Tried a lot of different setting (removeAbandoned,
removeAbandonedTimeout etc.)
but eventually DBCP locks or if maxWait is set then
throws an exception:
"DBCP could not obtain an idle db connection, pool
exhausted"
- some exlcusive locks remain held on tables however we
take care
to close connection in the finally blocks with a util
method.
However timing out server connections by the server
itself
makes sense (resource sparing) I don't encounter
this "Object has been closed." problem on other
database servers
(mysql, oracle, etc.).
This behaviour of MSSQL server make it impossible to
make
connection pooling for it.
The validationQuery helps nothing here because at the
moment
when borrowing a connection may be valid but a moment
later
the server may close it.
From the user perspective this appication is completly
instable
because this error appears in different times.
Any comments?
Regards
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
