Andrew Conrad <[EMAIL PROTECTED]> wrote:
> Do you experience the problem when you use DBCP w/o your
custom DS
> factory?
>
No, I don't. Does it mean my approach is wrong?
(However I experience frequent blocks because of pool
exhaustion.)
You may ask why is not enough to specify user/password etc.
setting in a configuration file. This is because we have
to log database activity of users (insert, update, delete
only).
We wrote triggers for tables that can log operations with
the connected user login id. So after the user log in
the system this username/password information is also
used to connect the database. This is carried out by
passing environment filled out with username/password to
JNDI SPI
that creates an account initialized datasource.
On the other hand there are login unaware components in the
system (eg. form validator) where select statements
executed only.
This case the default account settings used by JNDI SPI.
I've attached the source code of the factory class. Please
look into, it very simple.
Thanks.
[Common-Logging]
If you try this code the last lines shows you another
exception this time
with from commons-logging dispite I did the checklist for
Tomcat:
- set JAVA_OPTS=-
Dlog4j.configuration=e:\kalman\projects\dbcp\conf\log4j.prop
erties
- copy log4j.properties %CATALINA_HOME%/commons/classes
- copy commons-logging.jar %CATALINA_HOME%/commons/lib
- copy commons-logging-api.jar %CATALINA_HOME%/commons/lib
- copy log4j-1.2.6.jar %CATALINA_HOME%/commons/lib
content of log4j.properties file:
log4j.rootCategory=DEBUG, fileAppender
log4j.logger.my.dbcp=DEBUG
log4j.appender.fileAppender=org.apache.log4j.FileAppender
log4j.appender.fileAppender.File=e:\\my.dbcp.txt
log4j.appender.fileAppender.Append=false
log4j.appender.fileAppender.Threshold=DEBUG
Regards,
tele
> - Andrew
>
> > -----Original Message-----
> > From: teletype [mailto:[EMAIL PROTECTED]]
> > Sent: Tuesday, August 27, 2002 7:58 AM
> > To: [EMAIL PROTECTED]
> > Subject: [DBCP] Connection pooling on MSSQL
> >
> >
> > 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]>
> >
>
>
> --
> To unsubscribe, e-mail: <mailto:commons-user-
[EMAIL PROTECTED]>
> For additional commands, e-mail: <mailto:commons-user-
[EMAIL PROTECTED]>
>
>
>
>
package my.dbcp;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Map;
import javax.naming.Context;
import javax.naming.Name;
import javax.naming.NamingException;
import javax.naming.Reference;
import javax.naming.RefAddr;
import javax.naming.spi.ObjectFactory;
import org.apache.commons.dbcp.BasicDataSource;
public class PoolingDataSourceFactory implements ObjectFactory {
public Object getObjectInstance(Object obj, Name name, Context nameCtx,
Hashtable environment)
throws Exception {
if ((obj == null) || !(obj instanceof Reference)) {
return null;
}
Reference ref = (Reference) obj;
if (!"javax.sql.DataSource".equals(ref.getClassName())) {
return null;
}
if (environment == null) {
environment = new Hashtable();
}
RefAddr ra = null;
String username = (String) environment.get("username");
if (username == null || "".equals(username.toString().trim())) {
ra = ref.get("username");
username = ra.getContent().toString();
}
boolean initialize = false;
BasicDataSource dataSource = null;
dataSource = (BasicDataSource) repository.get(username);
if (dataSource == null) {
dataSource = new BasicDataSource();
repository.put(username, dataSource);
initialize = true;
}
if (initialize) {
ra = ref.get("defaultAutoCommit");
if (ra != null) {
dataSource.setDefaultAutoCommit
(Boolean.valueOf(ra.getContent().toString()).booleanValue());
}
ra = ref.get("defaultReadOnly");
if (ra != null) {
dataSource.setDefaultReadOnly
(Boolean.valueOf(ra.getContent().toString()).booleanValue());
}
ra = ref.get("driverClassName");
if (ra != null) {
dataSource.setDriverClassName(ra.getContent().toString());
}
ra = ref.get("maxActive");
if (ra != null) {
dataSource.setMaxActive
(Integer.parseInt(ra.getContent().toString()));
}
ra = ref.get("maxIdle");
if (ra != null) {
dataSource.setMaxIdle
(Integer.parseInt(ra.getContent().toString()));
}
ra = ref.get("maxWait");
if (ra != null) {
dataSource.setMaxWait
(Long.parseLong(ra.getContent().toString()));
}
ra = ref.get("password");
if (ra != null) {
dataSource.setPassword(ra.getContent().toString());
}
ra = ref.get("url");
if (ra != null) {
dataSource.setUrl(ra.getContent().toString());
}
ra = ref.get("username");
if (ra != null) {
dataSource.setUsername(ra.getContent().toString());
}
ra = ref.get("validationQuery");
if (ra != null) {
dataSource.setValidationQuery(ra.getContent().toString());
}
ra = ref.get("removeAbandoned");
if (ra != null) {
dataSource.setRemoveAbandoned
(Boolean.valueOf(ra.getContent().toString()).booleanValue());
}
ra = ref.get("removeAbandonedTimeout");
if (ra != null) {
dataSource.setRemoveAbandonedTimeout
(Integer.parseInt(ra.getContent().toString()));
}
ra = ref.get("logAbandoned");
if (ra != null) {
dataSource.setLogAbandoned
(Boolean.valueOf(ra.getContent().toString()).booleanValue());
}
//
// Override configuration file defaults with the passed
// environment
//
Object value = environment.get("defaultAutoCommit");
if (value != null) {
dataSource.setDefaultAutoCommit
(Boolean.valueOf(value.toString()).booleanValue());
}
value = environment.get("defaultReadOnly");
if (value != null) {
dataSource.setDefaultReadOnly
(Boolean.valueOf(value.toString()).booleanValue());
}
value = environment.get("driverClassName");
if (value != null) {
dataSource.setDriverClassName(value.toString());
}
value = environment.get("maxActive");
if (value != null) {
dataSource.setMaxActive
(Integer.parseInt(value.toString()));
}
value = environment.get("maxIdle");
if (value != null) {
dataSource.setMaxIdle
(Integer.parseInt(value.toString()));
}
value = environment.get("maxWait");
if (value != null) {
dataSource.setMaxWait
(Long.parseLong(value.toString()));
}
value = environment.get("password");
if (value != null) {
dataSource.setPassword(value.toString());
}
value = environment.get("url");
if (value != null) {
dataSource.setUrl(value.toString());
}
value = environment.get("username");
if (value != null) {
dataSource.setUsername(value.toString());
}
value = environment.get("validationQuery");
if (value != null) {
dataSource.setValidationQuery(value.toString());
}
value = environment.get("removeAbandoned");
if (value != null) {
dataSource.setRemoveAbandoned
(Boolean.valueOf(value.toString()).booleanValue());
}
value = environment.get("removeAbandonedTimeout");
if (value != null) {
dataSource.setRemoveAbandonedTimeout
(Integer.parseInt(value.toString()));
}
value = environment.get("logAbandoned");
if (value != null) {
dataSource.setLogAbandoned
(Boolean.valueOf(value.toString()).booleanValue());
}
}
return dataSource;
}
private Map repository = new HashMap();
//private static Log log = LogFactory.getLog(PoolingDataSourceFactory.class);
private static Log log;
static {
try {
log = LogFactory.getLog(PoolingDataSourceFactory.class);
}
catch (Throwable t) {
t.printStackTrace();
}
}
}
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
