commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dirk Verbeeck <dirk.verbe...@pandora.be>
Subject Re: [DBCP] per-user pooling with Oracle JDBC thin driver
Date Wed, 20 Oct 2004 20:51:36 GMT
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


Mime
View raw message