I've just tried out the 24/10/2004 nightly release of DBCP and POOL, and I get the same error ;-(
-----Original Message----- From: Dirk Verbeeck [mailto:[EMAIL PROTECTED] Sent: 20 October 2004 21:52 To: Jakarta Commons Users List Subject: Re: [DBCP] per-user pooling with Oracle JDBC thin driver Hi Chris, Your analysis is correct, it is the same error. The issue was fixed in BasicdataSource, unfortunately the same error still existed in SharedPoolDataSource & PerUserPoolDataSource. http://issues.apache.org/bugzilla/show_bug.cgi?id=31811 It should be fixed now, you can build from cvs or use the next nightly build. Cheers Dirk Chris Nappin wrote: > Hi, > > > > I'm trying to configure per-user connection pooling with Tomcat, DBCP > and the Oracle 9i JDBC driver. A connection with default driver settings > seems to work, but when setting auto-commit to false I get errors. I've > tried DBCP 1.1 and 1.2.1 (with Pool 1.1 or 1.2). > > > > I have configured DBCP as follows (Tomcat server.xml extracts): > > > > <Resource name="jdbc/omsdev" auth="Container" > > > type="org.apache.commons.dbcp.datasources.PerUserPoolDataSource"/> > > <ResourceParams name="jdbc/omsdev"> > > <parameter> > > <name>factory</name> > > > <value>org.apache.commons.dbcp.datasources.PerUserPoolDataSourceFactory< > /value> > > </parameter> > > <parameter> > > <name>dataSourceName</name> > > <value>java:comp/env/jdbc/CPDS</value> > > </parameter> > > <parameter> > > <name>maxActive</name> > > <value>10</value> > > </parameter> > > <parameter> > > <name>maxIdle</name> > > <value>2</value> > > </parameter> > > <parameter> > > <name>maxWait</name> > > <value>-1</value> > > </parameter> > > <parameter> > > <name>defaultReadOnly</name> > > <value>false</value> > > </parameter> > > <parameter> > > <name>defaultAutoCommit</name> > > <value>false</value> > > </parameter> > > </ResourceParams> > > > > This then uses the CPDS driver-wrapper, configured as follows (i.e. no > username or password): > > > > <Resource name="jdbc/CPDS" auth="Container" > > > type="org.apache.commons.dbcp.cpdsadapter.DriverAdapterCPDS"/> > > <ResourceParams name="jdbc/CPDS"> > > <parameter> > > <name>factory</name> > > > <value>org.apache.commons.dbcp.cpdsadapter.DriverAdapterCPDS</value> > > </parameter> > > <parameter> > > <name>driver</name> > > <value>oracle.jdbc.driver.OracleDriver</value> > > </parameter> > > <parameter> > > <name>url</name> > > <value>..my connection string..</value> > > </parameter> > > </ResourceParams> > > > > > > I'm then using the following code to open a connection (name is > "jdbc/omsdev", user is a bean populated on login): > > > > Context initContext = new InitialContext(); > > DataSource ds = (DataSource) initContext.lookup("java:comp/env/" + > name); > > Connection con = ds.getConnection(user.getUsername(), > user.getPassword()); > > > > This works fine, once (a read query using a new connection). However, > when DBCP tries to re-use a pooled connection, I get: > > > > java.sql.SQLException: ORA-01453: SET TRANSACTION must be first > statement of transaction > > > > at > oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) > > at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) > > at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573) > > at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891) > > at > oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093) > > at > oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java: > 2047) > > at > oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1 > 940) > > at > oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement. > java:2709) > > at > oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedS > tatement.java:589) > > at > oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStateme > nt.java:656) > > at > oracle.jdbc.driver.OracleConnection.setReadOnly(OracleConnection.java:15 > 43) > > at > org.apache.commons.dbcp.cpdsadapter.ConnectionImpl.setReadOnly(Connectio > nImpl.java:347) > > at > org.apache.commons.dbcp.datasources.PerUserPoolDataSource.setupDefaults( > PerUserPoolDataSource.java:416) > > at > org.apache.commons.dbcp.datasources.InstanceKeyDataSource.getConnection( > InstanceKeyDataSource.java:654) > > > > I would ideally like to use two data sources (one read-only, one > read-write with auto-commit off). Unfortunately there seems to be a bug > in Tomcat JNDI code that prevents a second data source being accessed. > So I am now trying to use one data source (read-write with auto-commit > off). > > > > The bug I'm seeing seems very similar to the following, which was > resolved in DBCP 1.2.1: > > > > Bugzilla Bug 25001 > > PATCH: Oracle 9i and default isolation settings > > > > Any help would be gratefully received! > > > > Regards > > Chris Nappin > > Senior Analyst Programmer > > ABM United Kingdom Limited > Telephone: +44 (0) 115 977 6999 > Facsimile: +44 (0) 115 977 6850 > Web: http://www.abm-uk.com > > ABM for Intelligent Solutions > > The information contained in this email is intended only for the named > recipient(s) and may be confidential and/or privileged. Unauthorised use > or reproduction (including storage or re-distribution in any media) is > prohibited. > > ABM-United Kingdom Limited may monitor the content of e-mails and files > sent and received via its network for the purposes of ensuring > compliance with its legal obligations and its policies and procedures. > > > > --------------------------------------------------------------------- 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]
