commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Chris Nappin" <C.Nap...@abm-uk.com>
Subject RE: [DBCP] per-user pooling with Oracle JDBC thin driver
Date Mon, 25 Oct 2004 09:53:02 GMT
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:dirk.verbeeck@pandora.be] 
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: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org


Mime
View raw message