Phil, First of all, let me say thank you for your time and for the most useful detailed responses. I've just realized that you are one of the DBCP's project developers and it's nice to know that you guys care about the project and its users. Now, I understand how the DBCP's InstanceKeyDataSource pool is communicating with the CPDS implementation. My previous understanding of the contract between a pool manager and a CPDS was wrong but I guess I got it right now. The provided links to the Oracle documentation proved to be very useful and explanatory as well. Yes, I am using an older Oracle version 9i with the ojdbc14-9i client driver jar. The main source of confusion I believe stems from the fact that in this version of Oracle the OracleDataSource and the OracleConnectionPoolDataSource both return the object of type OracleConnection when the client calls ds.getConnection(). However, in the former case the OracleConnection object represents a physical database connection but in the latter case the same OracleConnection object is a logical connection i.e. the temporary handle to the physical database connection. I guess Oracle guys realized that this was not very good and cleaned up their jdbc implementation in the later releases. Yet, it'd be interesting to decompile the OracleConnection and see how it's working, for example, look at its close() method. Anyway, I've tried to check the number of physical connections open in my test. I do not have Oracle monitor installed nor could I use the ps since I am using the Oracle jdbc thin driver in my configuration (I guess you used oci driver + oci client but that does not replicate my test configuration). But, concluding from the results of netstat -apn | grep 1521, I did verified that there are only 2 physical connections open to the database after I've run the test. So, the pool is working correctly and all my questions are resolved. Thanks again for support. --Seva
________________________________ From: Phil Steitz [mailto:[EMAIL PROTECTED] Sent: Tue 6/19/2007 7:09 PM To: Jakarta Commons Users List Subject: Re: [DBCP] The PerUserPoolDataSource/SharedPoolDataSource does not reuse/pool the db connections On 6/18/07, Seva Popov <[EMAIL PROTECTED]> wrote: > Hi Phil, > > Here is the config for the physical connections: > > <Resource name="jdbc/MyCPDS" > auth="Container" > type="oracle.jdbc.pool.OracleConnectionPoolDataSource" > factory="oracle.jdbc.pool.OracleDataSourceFactory" > url="jdbc:oracle:thin:@some_url:1521:some_sid"/> > > Yes, I know about the differences between PerUserPoolDataSource and > SharedPoolDataSource and I agree that the test for PerUserPoolDataSource > was not valid as you've indicated and I will try it again. > Actually, looking at the example again, even with the defaults it should only open two connections. See comments below. > However, I've used the same configuration for the SharedPoolDataSource > with the same negative results. > > Also, in both cases when I've used ds.getConnection() I've obtained the > raw Oracle connection but I've expected to get > org.apache.tomcat.dbcp.dbcp.PoolableConnection as with the > BasicDataSource. Note, that I am not expecting to get > OraclePooledConnection as you've indicated as this would be wrong as the > OraclePooledConnection (that implements javax.sql.PooledConnection) is > supposed to be used internally by the Pool Manager implementation. The > OraclePooledConnection does not implement java.sql.Connection. So, what > the DBCP's Pool Manager should do is to wrap the OraclePooledConnection > with the org.apache.tomcat.dbcp.dbcp.PoolableConnection (or some other > wrapper that implements java.sql.Connection interface) and return the > latter to the client. > > Is it not the case with DBCP? InstanceKeyDataSource (which both PerUserPoolDataSource and SharedPoolDataSource extend) does not wrap connection handles returned by ConnectionPoolDataSource impls. The pool maintains PooledConnections obtained from the configured ConnectionPoolDataSource and the InstanceKeyDataSource getConnection method returns the result of getConnection applied to PooledConnections in the pool. The client should get back connection handles provided by the driver. The pool (actually the factory) implements the ConnectionEventListener interface. Tomcat 5.5.9 bundles (repackaged) commons dbcp 1.2.1. You can see the sources for this version of dbcp either by downloading the 1.2.1 source distribution here: http://archive.apache.org/dist/jakarta/commons/dbcp/source/ or online via the svn viewer: http://svn.apache.org/viewvc/jakarta/commons/proper/dbcp/tags/DBCP_1_2_1/ I am by no means an Oracle expert, but using Oracle Database 10g Express Edition Release 10.2.0.1.0 and the client driver jar it ships with on Fedora Core 4, tomcat 5.5.9, I think I was able to verify correct functioning of both PerUserPoolDataSource and SharedPoolDataSource with your example jsp. My output for PerUserPoolDataSource looks like this: DataSource: [EMAIL PROTECTED] Connection 1: [EMAIL PROTECTED] Connection 2: [EMAIL PROTECTED] Connection 3: [EMAIL PROTECTED] Note the difference in types from your output. The LogicalConnections are handles. Looking at the Oracle monitor and also ps, I can see that there are in fact only 2 physical connections open to the database (at least that what it looks like to me - as I said, I am not an Oracle expert, I am concluding this from looking at "sessions" identified in the web monitor and ps -aux | grep Oracle on the command line). What version of Oracle and Oracle drivers are you using? From the docs here: http://download-east.oracle.com/docs/cd/A87861_01/NT817EE/java.817/a83724/connpoc2.htm it looks like previous versions somehow used OracleConnection instances as handles (as your output above indicates). Can you check the number of physical connections open somehow? Phil --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
