Re: [DBCP] per-user pooling with Oracle JDBC thin driver

[Dirk Verbeeck]

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]