OracleLobHandler + WebSphereNativeJdbcExtractor doesn't work on Websphere 6 [SPR-1317]

[spring-projects-issues]

Davor Cengija opened SPR-1317 and commented

Hi,

we are developing application on Websphere 6 with Oracle 9i database. Some LOBs are saved to database using Hibernate 3. We are using Websphere datasource so we need to use WebSphereNativeJdbcExtractor which causes us problems.

Our configuration:

<!-- Lob handler for Oracle 9i and IBM Websphere data source -->
<bean id="lobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler">
<property name="nativeJdbcExtractor">
<bean class="org.springframework.jdbc.support.nativejdbc.WebSphereNativeJdbcExtractor"/>
</property>
</bean>
<!---- Hibernate session factory definition -->
<bean id="auditSessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="lobHandler" ref="lobHandler"/>
...

When LOB object is about to be saved exception is thrown:

org.springframework.dao.DataAccessResourceFailureException: Could not create Oracle LOB; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: OracleLobCreator needs to work on [oracle.jdbc.OracleConnection], not on [class oracle.jdbc.driver.OracleConnection] - specify a corresponding NativeJdbcExtractor
org.springframework.dao.InvalidDataAccessApiUsageException: OracleLobCreator needs to work on [oracle.jdbc.OracleConnection], not on [class oracle.jdbc.driver.OracleConnection] - specify a corresponding NativeJdbcExtractor
at org.springframework.jdbc.support.lob.OracleLobHandler$OracleLobCreator.getOracleConnection(OracleLobHandler.java:360)
at org.springframework.jdbc.support.lob.OracleLobHandler$OracleLobCreator.createLob(OracleLobHandler.java:328)

return in code below from WebSphereNativeJdbcExtractor returned connection is type of oracle.jdbc.driver.OracleConnection instead of oracle.jdbc.OracleConnection
/**

• Retrieve the Connection via WebSphere's <code>getNativeConnection</code> method.
  */
  protected Connection doGetNativeConnection(Connection con) throws SQLException {
  // WebSphere 5 connection?
  if (this.webSphere5ConnectionClass != null &&
  this.webSphere5ConnectionClass.isAssignableFrom(con.getClass())) {
  try {
  // WebSphere 5's WSJdbcUtil.getNativeConnection(wsJdbcConnection)
  return (Connection) this.webSphere5NativeConnectionMethod.invoke(null, new Object[] {con});
  }

So in org.springframework.jdbc.support.lob.OracleLobHandler in getOracleConnection() conToUse is type of oracle.jdbc.driver.OracleConnection (implements java.sql.Connection) and
connectionClass is oracle.jdbc.OracleConnection type. Because of that, exception is throwned (as specified in getOracleConnection()). We are using ojdbc14.jar for database connection.

We hope that you will do some testing on Websphere 6 to resolve this problem.

---

Affects: 1.2.4

[spring-projects-issues]

Juergen Hoeller commented

This is likely to be caused by a class loader issue: that is, the Oracle JDBC driver classes being available from multiple class loaders.

Spring's OracleLobHandler used a locally loaded OracleConnection class to check whether the given Connection handle is actually an Oracle JDBC connection. That might fail if the locally loaded OracleConnection class is not the same as the one used at the server level.

Consequently, we've relaxed that check to accept any kind of Connection handle. If a ClassCastException arises within the Oracle driver's BLOB/CLOB handling (a sign of a non-OracleConnection passed in), we throw an expressive exception that indicates to specify a correct NativeJdbcExtractor.

Essentially, it's a post-invocation check rather than a pre-invocation check now, which should avoid any potential class loader issues.

This change should be available in Monday night's nightly Spring snapshot. Please give it a try and let us know whether it works for you!

Juergen

[spring-projects-issues]

Miroslav Rešetar commented

Hy,
original posting was with my colleague account because at the time I didn't have mine. I made the effort and created one ;-)

Thanks for fixing and being so active on mailing list. Unfortunately your fix didn't solve the problem in our case. But you are right that this is classloader issue. We had ojdbc14.jar in our WEB-INF/lib and as server env variable. I removed jar from WEB-INF/lib and everything worked fine with or without your fix.

The exception that was throwned this time was from createLob method (lines around 350):

else if (con != null && ex.getTargetException() instanceof ClassCastException) {
throw new InvalidDataAccessApiUsageException(
"OracleLobCreator needs to work on [oracle.jdbc.OracleConnection], not on [" +
con.getClass() + "]: specify a corresponding NativeJdbcExtractor");
}
I'm at home now so I'm unable to post whole stack trace. If you need it or any other information I would be glad to provide.

Thanks, Miroslav Rešetar

[spring-projects-issues]

David Castro commented

We are running into the same issue while using OracleLobHandler. We have tried different JDBC extractors and have grabbed the latest build (spring-framework-1.2.6-with-dependencies-build.360-20051020.zip) and still no luck.

With org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor, the message is:

org.springframework.dao.InvalidDataAccessApiUsageException: OracleLobCreator needs to work on [oracle.jdbc.OracleConnection], not on [class org.apache.tomcat.dbcp.dbcp.PoolableConnection]: specify a corresponding NativeJdbcExtractor

With org.springframework.jdbc.support.nativejdbc.SimpleNativeJdbcExtractor, the message is:

org.springframework.dao.InvalidDataAccessApiUsageException: OracleLobCreator needs to work on [oracle.jdbc.OracleConnection], not on [class oracle.jdbc.driver.OracleConnection]: specify a corresponding NativeJdbcExtractor

This is using Spring 1.2.6 (latest snapshot), JNDI on Tomcat using DBCP 1.2.1. I looked in our Oracle driver jar, ojdbc14.jar (9.2.0.x), which contains both 'oracle.jdbc.OracleConnection' and 'oracle.jdbc.driver.OracleConnection'. Maybe that is part of the problem?

Are we supposed to use the DBCP extractor or the Simple one? It seems like we are closer to the solution when we specify the simple (potentially just a driver class name problem), but I assume we need to use the DBCP extractor to actually be able to get the native Oracle connection object that can be used for large object handling.

Here is the spring wire-up:

  <!-- Choose the dialect that matches your "dataSource" definition -->
  <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
        <property name="dataSource"><ref bean="myDataSource"/></property>
        <property name="lobHandler">
        <ref bean="oracleLobHandler"/>
    </property>          
  </bean>

  <!-- Transaction manager for a single Hibernate SessionFactory (alternative to JTA) -->
  <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
        <property name="sessionFactory"><ref local="sessionFactory"/></property>
  </bean>

  <bean id="oracleLobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler">
    <property name="nativeJdbcExtractor">
        <ref local="nativeJdbcExtractor"/>
    </property>
</bean>
<bean id="nativeJdbcExtractor" class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor" lazy-init="true"/>

Maybe this is a different problem, but looks awefully similar to me. Suggestions?

[spring-projects-issues]

Juergen Hoeller commented

If you got the "not on ...OracleConnection" error message, the native connection extraction worked. However, there seems to be an issue in the Oracle driver itself hiding here. Essentially, the exception you're seeing results from a BLOB.createTemporary call, passing in the OracleConnection handle as argument. The implementation of that Oracle-specific JDBC method then throws a ClassCastException, which you're seeing wrapped in an InvalidDataAccessApiUsageException. I have changed this to include the root ClassCastException for clearness. However, the underlying problem remains: The Oracle driver does not accept its own Connection handle passed into its own BLOB.createTemporary method...

Juergen

[spring-projects-issues]

Reg Skelton commented

David: I'm no expert, but for DBCP setups, I think you should be using CommonsDbcpNativeJdbcExtractor. I got it working when I set "accessToUnderlyingConnectionAllowed" to true (for me, that was in the applicationcontext.xml definition of my DBCP datasource).

Juergen: Having got the native extractor to work, I now see the "not on...OracleConnection" message. You seem to be suggesting this is an Oracle issue - do you know if they are aware of it, or if there's a workaround? (I'm on Spring 1.2.4, Oracle 9.2.0.5 with classes12.jar). Any suggestions much appreciated...

[spring-projects-issues]

David Castro commented

We actually found a workaround to the issue. We are now using org.springframework.jdbc.support.lob.DefaultLobHandler without an extractor explicitly specified and are currently using the Oracle 10g driver (which works for 9i databases as well). Apparently there is an Oracle driver bug which is causing this and we stumbled accross a solution (which was to upgrade to the 10g driver). Of course the 10g driver has new bugs that may cause you issues, but it resolved the issue temporarily at least.

[spring-projects-issues]

Reg Skelton commented

Thanks, David. I'll give it a go, but I'm not optimistic about (the politics of) upgrading the driver.

[spring-projects-issues]

Juergen Hoeller commented

Reopening the issue, although I expect this to be an Oracle driver issue...

Juergen

[spring-projects-issues]

Juergen Hoeller commented

If you try a very recent nightly 1.2.6 snapshot, you should see the underlying ClassCastException behind the "not on..." message. Could you please post it here, so that we can see where exactly it originates? I would expect this to be within the Oracle driver's createTemporary implementation...

Juergen

[spring-projects-issues]

heaven commented

I met this problem today , and I think it is a Oracle driver issue.Because when I change anthoer driver that some older .The problem disapeared.

[spring-projects-issues]

heaven commented

We have resolved this problem by replace the getClass().getClassLoader().loadClass() to Thread.currentThread().getContextClassLoader().loadClass().
We have tested ok using spring 1.1.4 and oracle 9i.

[spring-projects-issues]

heaven commented

ps: We modify the OracleLobHandler .

[spring-projects-issues]

Juergen Hoeller commented

Do you have multiple versions of the Oracle driver in your classpath? One at the application level, on at the system class loader level? It seems so, since the different ClassLoaders seem to pick up different versions of the OracleConnection class. Or do you have multiple spring.jar at different levels of the class loader hierarchy?

Juergen

[spring-projects-issues]

heaven commented

yes , we indeed have multiple versions of the Oracle driver in
our classpath,but some special reasons that we can not resolve this
problem in the web server.
So we add a Class modifyed from OracleLobHandler,and it works .

                 protected Object prepareLob(Connection con, Class lobClass) throws Exception {

		ClassLoader cl = con.getClass().getClassLoader();
		if (logger.isDebugEnabled()) {
			logger.debug("" + con + "的classloader" + cl);
		}
		
		Class clazz = cl.loadClass("java.sql.Connection");
		.......
	     }

We just changed the class loader from getClass().getClassLoader() to con.getClass().getClassLoader() to make
sure that we use the same classloder.It looks not effective than the original implement,but it just works.

We hope Hoeller to give us a better implement to resolve this problem :-)

[spring-projects-issues]

Juergen Hoeller commented

OK, thanks for the hint. I've refined OracleLobHandler to lazily initializes the Oracle driver classes that is uses via reflection, always loading them via the actual Oracle Connection's class loader. This should avoid potential class loader issues upfront.

Juergen