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 [DBCP] per-user pooling with Oracle JDBC thin driver
Date Mon, 18 Oct 2004 14:02:55 GMT
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. 

 


Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message