Has anyone managed to get per-user database connection pooling to work with Tomcat and the Oracle 9.2.0 JDBC driver? My application requires per-user database authentication (we're using Oracle VPD) so the usual sharing of one database user is not an option. I've been trying with the Jakarta commons-DBCP "PerUserPoolDataSource" but with mixed success:

- when using the default BasicDataSource, the Oracle driver throws an exception when calling dataSource.getConnection(username, password) suggesting the driver doesn't support it? "getConnection()" with JNDI-configured username and password works fine, so I know ny setup is okay.

- when using the CPDS to "wrap" the Oracle driver a read-only connection works fine, and performs slightly quicker than direct connections, but when looking up a second (read-write) datasource I get a javax.naming.NamingException complaing about the name of the _other_ datasource (yes I am looking up the correct one). Yes initialContext.list("java:comp/env/jdbc") lists both datasources correctly.

I'm using Tomcat 4.1.29, DBCP 1.1, JDK 1.4 and Oracle JDBC 9.2.0.

Also, has anyone managed to configure this sort of thing on a commercial app server?

To give some more details on the BasicDataSource failures:

When calling getConnection(username, password) I get the following exceptions thrown:

org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (invalid arguments in call)
        at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:855)
        at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:554)
        at com.abmuk.oms.core.common.database.ConnectionManager.getPooledConnection(ConnectionManager.java:152)
        at com.abmuk.oms.core.common.database.ConnectionManager.getReadOnlyConnection(ConnectionManager.java:73)
...
Caused by: java.sql.SQLException: invalid arguments in call
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
        at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1160)
        at oracle.jdbc.ttc7.TTC7Protocol.logon(TTC7Protocol.java:183)
        at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:346)

        at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:468)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:314)
        at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:37)
        at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:290)
        at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:877)
        at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:851)

I thought this was a basic requirement of the JDBC API, so I'm suprised if Oracle doesn't really support it?

I've also tried using newer Commons DBCP (1.2.1) and Pool (1.2) but this hasn't made any difference. ;-(

Chris -
It's been a long time, but you might check into using Oracle's identity server for user auth as it applies your VPD contexts to the user. Also, Oracle has a conn pool that you can implement in Tomcat rather than using theirs. I haven't tried it in Tomcat but have gotten it to work in other commerical app servers like Sun's, WebLogic etc. Hope this helps and isn't a red-herring. :)

Thanks -
Jon

Yes, you need to specify the correct factory and driver however. The Tomcat default driver will not work with the Oracle JDBC driver, you must use the class in the Oracle driver that will override the default javax.sql class.

type="oracle.jdbc.pool.OracleDataSource" driverClassName="oracle.jdbc.driver.OracleDriver"
factory="oracle.jdbc.pool.OracleDataSourceFactory"

I now have a full whitepaper on setting everything up here:Tomcat JNDI - Oracle JDBC Connection Pooling How-To

Cheers.....Gregg

I now have a full whitepaper on setting everything up here:Tomcat JNDI - Oracle JDBC Connection Pooling How-To

Gregg,

Great white paper on Tomcat + JNDI + Oracle.

Using Oracle 10g and the ojdbc14 that comes with it, your examples as given gave me SQLException "invalid arguments in call" from dataSource.getConnection(). In the <Resource> element, changing the attribute "username" to "user" fixed the problem (having noticed the OracleDataSource has a setUser() method, I tried it, and it worked).

Since your examples worked for you, it could be a difference in the very latest ojdbc14, just a guess.

Hugh

Oh, and I'm using Tomcat 5.5

Unfortunately this How-To does not work 100%. It will allow you to get a connection from the database however it will not use DBCP. Take a look at this link for more information. http://forums.oracle.com/forums/message.jspa?messageID=1731340

Take a look at this posting Creating a connection pool for Tomcat using Oracle driver http://javaonthefly.blogspot.com/2008/01/creating-connection-pool-for-tomcat.html