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 Wed, 20 Oct 2004 16:38:19 GMT
Hi,

  I've managed to get a little further by adding a "connection.commit()"
call after all SELECT queries (we were only committing UPDATEs and
INSERTs, not reads) which is a nasty workaround but needed since I can't
have a read-only data-source.

However, I'm now getting some *really* odd errors in our Clob-handling
code:

java.sql.Clob realClob = rs.getClob(1);
System.out.println("realClob classname is: " +
realClob.getClass().getName());
        
if (realClob instanceof oracle.sql.CLOB) {
	System.out.println("realClob is a oracle.sql.CLOB");
} else {
	System.out.println("realClob is NOT a oracle.sql.CLOB");
}
oracle.sql.CLOB clob = (oracle.sql.CLOB) realClob; 

This code prints out a classname of "oracle.sql.CLOB", but instanceof
fails and the last line produces a ClassCastException!

That's really weird and shouldn't be possible in Java (getClass is final
so can't every contradict instanceof)????

Everything works fine using direct connections though, it's only when
using DBCP that I get the above error. Does DBCP use wrapped Connections
and ResultSets? I've tried Oracle JDBC 9.2.0.1.0 and 9.2.0.5.0 (buggy
but matches our dbms version) drivers, both give this error.

Cheers,

   Chris Nappin

-----Original Message-----
From: Chris Nappin 
Sent: 18 October 2004 15:03
To: commons-user@jakarta.apache.org
Subject: [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. 

 



---------------------------------------------------------------------
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