Exception with ComboPooledDatasource and OracleLobHandler

[chenrici]

I get the following exception:

DataAccessResourceFailureException: Could not create Oracle LOB; nested exception is org.springframework.dao.InvalidDataAccessApiUsageE xception: OracleLobCreator needs to work on [oracle.jdbc.OracleConnection], not on [class com.mchange.v2.c3p0.impl.NewProxyConnection] - specify a corresponding NativeJdbcExtractor

On the following Code in a Subclass of StoredProcedure:

        public String execute(String data)
        {

            HashMap inputMap = new HashMap();
            final OracleLobHandler lh = new OracleLobHandler();
            lh.setNativeJdbcExtractor(new SimpleNativeJdbcExtractor());
            inputMap.put(CLOB_INPUT, new SqlLobValue(data, lh));
            Map resultMap = super.execute(inputMap);
            return (String) resultMap.get(RETURN_VALUE);
        }

The respective bean configuration for the DataSource is:

  	<bean id="pooledDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
		<property name="driverClass"><value>$&#123;jdbc.driverClassName&#125;</value></property>
		<property name="jdbcUrl"><value>$&#123;jdbc.url&#125;</value></property>
		<property name="user"><value>$&#123;jdbc.username&#125;</value></property>
		<property name="password"><value>$&#123;jdbc.password&#125;</value></property>
	</bean>

In the Spring API Documentation i read that the SimpleNativeJdbcExtractor has been tested against C3P0 0.8.x.

So at the odds. Any hints, what could be wrong? Probably something simple ;-)
Christoph

[Juergen Hoeller]

Hi Christoph,

SimpleNativeJdbcExtractor does work with C3P0 0.8.4.5 for me. Could you double-check that SimpleNativeJdbcExtractor actually applies to the operation that throws the exception?

You could also manually test whether extraction works: grab a reference to the ComboPooledDataSource, call getConnection() on it, and see what SimpleNativeJdbcExtractor.getNativeConnection returns for the given Connection handle.

Juergen

[chenrici]

Hi Jürgen,

With the Dao which is configured that dataSource points to com.mchange.v2.c3p0.ComboPooledDataSource
I do:

Connection conn = dataSource.getConnection(); 
System.out.println(conn.toString()); 
SimpleNativeJdbcExtractor xtr = new SimpleNativeJdbcExtractor(); 
Connection natConn = xtr.getNativeConnection(conn);
System.out.println(natConn.toString());

for both System.out's i get:

com.mchange.v2.c3p0.impl.NewProxyConnection@da6bf4

The stacktrace points to OracleLobCreator.getConnection() as the last statement:

testUploadWithCred(com.apgsga.vs.db.UploadClobDaoT est)org.springframework.dao.DataAccessResourceFail ureException: Could not create Oracle LOB; nested exception is org.springframework.dao.InvalidDataAccessApiUsageE xception: OracleLobCreator needs to work on [oracle.jdbc.OracleConnection], not on [class com.mchange.v2.c3p0.impl.NewProxyConnection] - specify a corresponding NativeJdbcExtractor
[junit] org.springframework.dao.InvalidDataAccessApiUsageE xception: OracleLobCreator needs to work on [oracle.jdbc.OracleConnection], not on [class com.mchange.v2.c3p0.impl.NewProxyConnection] - specify a corresponding NativeJdbcExtractor
[junit] at org.springframework.jdbc.support.lob.OracleLobHand ler$OracleLobCreator.getOracleConnection(OracleLob Handler.java:362)
[junit] at org.springframework.jdbc.support.lob.OracleLobHand ler$OracleLobCreator.createLob(OracleLobHandler.ja va:330)
[junit] at org.springframework.jdbc.support.lob.OracleLobHand ler$OracleLobCreator.setClobAsString(OracleLobHand ler.java:261)
[junit] at org.springframework.jdbc.core.support.SqlLobValue. setTypeValue(SqlLobValue.java:201)
[junit] at org.springframework.jdbc.core.StatementCreatorUtil s.setParameterValue(StatementCreatorUtils.java:102 )
[junit] at org.springframework.jdbc.core.StatementCreatorUtil s.setParameterValue(StatementCreatorUtils.java:63)
[junit] at org.springframework.jdbc.core.CallableStatementCre atorFactory$CallableStatementCreatorImpl.createCal lableStatement(CallableStatementCreatorFactory.jav a:193)
[junit] at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:625)
[junit] at org.springframework.jdbc.core.JdbcTemplate.call(Jd bcTemplate.java:654)
[junit] at org.springframework.jdbc.object.StoredProcedure.ex ecute(StoredProcedure.java:100)
[junit] at com.apgsga.vs.db.UploadOracleClobDaoImpl$OracleClo bProcedure.execute(Unknown Source)
[junit] at com.apgsga.vs.db.UploadOracleClobDaoImpl.upload(Un known Source)
[junit] at com.apgsga.vs.db.UploadClobDaoTest.testUploadWithC red(Unknown Source)
[junit] at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
[junit] at sun.reflect.NativeMethodAccessorImpl.invoke(Unknow n Source)
[junit] at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)

Could it be that i got something with the configuration wrong?

 <bean id="pooledDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass"><value>$&#123;jdbc.driverClassName&#125;</value></property>
<property name="jdbcUrl"><value>$&#123;jdbc.url&#125;</value></property>
<property name="user"><value>$&#123;jdbc.username&#125;</value></property>
<property name="password"><value>$&#123;jdbc.password&#125;</value></property>
</bean>

Interesting enough the same code works fine against a org.apache.commons.dbcp.BasicDataSource, which is configured with the same jdbc.properties as the ComboPooledDataSource.
There i get the System.out's:

[junit] .org.apache.commons.dbcp.PoolableConnection@18235e d
[junit] oracle.jdbc.driver.OracleConnection@18235ed

And i get the expected results.
It work also without problems as expected against org.springframework.jdbc.datasource.DriverManagerD ataSource
Have i overseen something with the configuration of the ComboPooledDataSource? Or is it the version? I am using c3p0-0.8.5-pre7a. And i just checked the readme, which says

Version 0.8.5-pre4 and above contains a major, relatively untested, restructuring of
c3p0's codebase.

Could that be the problem?

Christoph

[chenrici]

It is a version problem. Just tested against c3p0-0.8.4.5. Works fine.
Sorry for the noise, atleast at hint at possible compatability problems with c3p0-0.8.5-pre2 and above ;-)
Thanx for your help,
Christoph

[Juergen Hoeller]

Damn, so Steve wraps the connection returned by con.getMetaData().getConnection() now, in C3P0 0.8.5... SimpleNativeJdbcExtractor relies on that connection being unwrapped, which works for a surprisingly large number of pools!

This means that we will need to add a C3p0NativeJdbcExtractor for C3P0 >= 0.8.5. According to the release notes, C3P0 should have a utility method for extracting the underlying native connection now, so a NativeJdbcExtractor should hopefully be straightforward to implement. We'll do this as soon as a stable version is available in the 0.8.5 branch.

Thanks for raising the attention on 0.8.5 :-)

Juergen