You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by Chris Nappin <C....@abm-uk.com> on 2004/10/18 16:02:55 UTC
[DBCP] per-user pooling with Oracle JDBC thin driver
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.
Re: [DBCP] per-user pooling with Oracle JDBC thin driver
Posted by Dirk Verbeeck <di...@pandora.be>.
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