DBCP Connection Problem with Sun One Application Server 7.0

Given below is the code that setup the driver, my class automatically maps standard ConnectionString to Apache DBCP connection string and sets up the driver on the fly, code is a bit long to be pasted completed right here... please note my connection does not depend upon any jndi resource, it needs to open connection to the database using manually setup Dbcp connection pool. The get connection call works with Tomcat however it doesn't work with SunOne Application server.

I'm using Sun One Application Server 7.

private void loadDriver(String driverName)

{

Logger logger = DbcpConnectionConfiguration.getLogger();

try

{

if( !loadedDriverList.contains( driverName ) )

{

Class.forName(driverName).newInstance();

loadedDriverList.add( driverName );

}

}

catch( ClassNotFoundException ce )

{

logger.error("In Loading Provider driver class"+driverName+". Error:"+ce.getMessage());

System.out.println("In Loading Provider driver class"+driverName+". Error:"+ce.getMessage());

}

catch( InstantiationException e )

{

logger.error("Error Initializing Driver class"+driverName+". Error:"+e.getMessage());

System.out.println("Error Initializing Driver"+driverName+". Error:"+e.getMessage());

}

catch (IllegalAccessException e)

{

logger.error("Error Initializing Driver"+driverName+"Error:"+e.getMessage());

System.out.println("Error Initializing Driver"+driverName+"Error:"+e.getMessage());

}

}

public void setupDriver(String connectURI, String userId, String password) throws Exception

{

//

// First, we'll need a ObjectPool that serves as the

// actual pool of connections.

//

// We'll use a GenericObjectPool instance, although

// any ObjectPool implementation will suffice.

//

GenericObjectPool genericPool = new GenericObjectPool(null);

//configure the pool from the configuration file.

configurePool( genericPool );

ObjectPool connectionPool = genericPool;

//

// Next, we'll create a ConnectionFactory that the

// pool will use to create Connections.

// We'll use the DriverManagerConnectionFactory,

// using the connect string passed in the command line

// arguments.

//

ConnectionFactory connectionFactory = null;

if( userId.length() > 0 )

connectionFactory = new DriverManagerConnectionFactory(connectURI,userId, password);

else

connectionFactory = new DriverManagerConnectionFactory(connectURI,null);

//

// Now we'll create the PoolableConnectionFactory, which wraps

// the "real" Connections created by the ConnectionFactory with

// the classes that implement the pooling functionality.

//

PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory,connectionPool,null,null,false,true);

//

// Finally, we create the PoolingDriver itself...

//

Class.forName("org.apache.commons.dbcp.PoolingDriver");

PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");

String connectionIdentifier = getConnectionIdentifier(connectURI);

//

// ...and register our pool with it.

//

driver.registerPool(connectionIdentifier,connectionPool);

//adding the pooled connection into the pooled connection list.

pooledConnectionList.add(connectURI);

//

// Now we can just use the connect string "jdbc:apache:commons:dbcp:example"

// to access our pool of Connections.

//

}

/* (non-Javadoc)

* @see sa.com.futureware.fastwork.fastdbconnectionmanagement.interfaces.IDbConnectionProvider#getConnection(java.lang.String, java.lang.String, java.lang.String, java.lang.String)

*/

public Connection getConnection(String driver, String url, String userName, String password) throws DbConnectionException

{

try

{

if( !pooledConnectionList.contains(url) )

{

if( !loadedDriverList.contains(driver) )

loadDriver( driver );

try

{

setupDriver( url, userName, password );

}

catch(Exception exception)

{

throw new DbConnectionException("Could load setup DBCP driver for the given url : "+url+". Error:"+exception.getMessage() );

}

}

String connectionIdentifier = getConnectionIdentifier( url );

if( connectionIdentifier.length() <= 0 )

throw new DbConnectionException("Could not create identifier for the connection url:"+url);

Connection con = DriverManager.getConnection(APACHE_DBCP_CONNECTION_URL+connectionIdentifier);

return con;

}

catch(SQLException sqlException )

{

throw new DbConnectionException("Could not create connection to "+url+". Error:"+sqlException.getMessage());

}

}

/**

* Configures the pool according to the set configuration.

* @param genericPool The pool to be configured.

*/

private void configurePool(GenericObjectPool genericPool)

{

// TODO Auto-generated method stub

DbcpConnectionConfiguration configuration = DbcpConnectionConfiguration.getConfiguration();

genericPool.setMaxActive(configuration.getMaxActive());

genericPool.setMaxIdle( configuration.getMaxIdle() );

genericPool.setMaxWait( configuration.getMaxWait() );

genericPool.setMinEvictableIdleTimeMillis( configuration.getMinEvictableIdleTimeMillis() );

genericPool.setTestOnBorrow( configuration.isTestOnBorrow() );

genericPool.setTestOnReturn( configuration.isTestOnReturn() );

genericPool.setTestWhileIdle( configuration.isTestWhileIdle() );

genericPool.setWhenExhaustedAction( configuration.getWhenExhaustedAction() );

genericPool.setTimeBetweenEvictionRunsMillis( configuration.getTimeBetweenEvictionRunsMillis() );

}

/**

* Gets a mapped Connection identifier. It only works with DSN based connection right now. returns the name of the DSN which acts as Dbcp connection pool name later.

* @param url

* @return

*/

private String getConnectionIdentifier(String url)

{

String connectionIdentifier = url.substring( url.lastIndexOf(':') + 1 );

return connectionIdentifier;

}

My Configuration File

<object class="sa.com.futureware.fastwork.dbcpconnectionprovider.DbcpConnectionConfiguration">

<!-- **** DBCP Connection Provider configuration **** -->

<void property="driverClassName">

<string>sun.jdbc.odbc.JdbcOdbcDriver</string>

</void>

<void property="connectionUrl">

<!--string>jdbc:microsoft:sqlserver://OFFICEWARE10:1433</string-->

<string>jdbc:odbc:SQLServerDB</string>

</void>

<!-- You can pass empty string if you are not using default jdbc connection provider -->

<void property="userName">

<string>PAMS</string>

</void>

<!-- You can pass empty string if you are not using default jdbc connection provider -->

<void property="password">

<string>purc</string>

</void>

<!-- Following is the Dbcp GenericObjectPool class configuration -->

<!-- please visit the following url for more details of these configuration paramters-->

<!-- http://jakarta.apache.org/commons/pool/apidocs/org/apache/commons/pool/impl/GenericObjectPool.html -->

<void property="maxActive">

<int>20</int>

</void>

<void property="maxIdle">

<int>10</int>

</void>

<void property="maxWait">

<long>2160000</long>

</void>

<void property="whenExhaustedAction">

<byte>1</byte>

</void>

<void property="testOnBorrow">

<boolean>false</boolean>

</void>

<void property="testOnReturn">

<boolean>false</boolean>

</void>

<void property="timeBetweenEvictionRunsMillis">

<long>2160000</long>

</void>

<void property="minEvictableIdleTimeMillis">

<long>36000</long>

</void>

<void property="testWhileIdle">

<boolean>false</boolean>

</void>

</object>

AliIqbal at 2007-7-6 19:27:48 >

I'm not sure if I can solve your problem, but I several items that warrant further investigation:

1) the username that shows up in your stack trace is *not* the same username that is in your XML file -- with the JDBC/ODBC driver I believe you can either specify username/password information in the ODBC control panel or in the JDBC Connection. I'm not sure which takes precedence. Are you sure the wrong username isn't configured in the ODBC control panel?

2) this looks like it is doing a lot of work that would be better handled by the AppServer's JDBC connection pool and JNDI registry. It appears that the guts of your RDBProvider.getConnection or DevelopmentProvider.openConnection method could be pretty easily replaced by calls to look up the JNDI DataSource and return its Connection. Unless you have a need to dynamically connect to lots of different databases, I think it's a better/more supported way to go. It's also a supported method on modern versions of Tomcat (4.x+) which come with a JNDI-based DataSource (hiding the details of DBCP). That way, your code only depends on standard JDK classes (DataSource, Connection, Context) rather than the bowels of DBCP.

3) finally, just a warning the that JDBC/ODBC driver has plenty of issues and would not be a good solution for a production site. Either the Microsoft "thin" JDBC driver or the free "jTDS" drivers for SQL Server are good and production-ready.

HTH,

-=- D. J.

djhagberg at 2007-7-6 19:27:48 >