I am trying to get the LobHandler working with WebSphere and I am having a little trouble. I've tried using the SimpleNativeJdbcExtractor but it doesn't seemt to be working.
Iam getting this exception:
Could not create Oracle LOB; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: OracleLobHandler needs to work on OracleConnection - maybe set the nativeJdbcExtractor property?
A getClass().getName() on the conection object give me "com.ibm.ws.rsadapter.jdbc.WSJdbcConnection".
Am I assuming that perhaps I need to write a custom Extractor for this type of datasource?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Based on the error message, it looks as if you are using the OracleLobhandler and not the DefaultLobHandler. Switch to the DefaultLobHandler and see what error you get then. I don't have much experience with DB2/WS but it migh just work.
Thomas
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I am using Oracle and the code works when using oracle.jdbc.pool.OracleConnectionPoolDataSource in a unit test. I think it's a problem trying to get a native connection from WSJdbcConnetion but I am not sure.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Spring 1.0.2's SimpleNativeJdbcExtractor checks Connection.getMetaData().getConnection(), which is the native Connection with many connection pools. Unfortunately, this doesn't seem to be the case with WebSphere.
So you probably need to implement a NativeJdbcExtractor for WebSphere. We have already added one for WebLogic in Spring 1.0.2, BTW. It would be great to have one for WebSphere too as of Spring 1.0.3 :-)
Take WebLogicNativeJdbcExtractor as template. Basically, derive from NativeJdbcExtractorAdapter and implement getNativeConnection (which is all that OracleLobHandler depends on) for WebSphere.
Juergen
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Reading your message again - I don't know where I got DB2 from :-) What version of Oracle driver are you using. I have been successful using the latest 10g Thin driver and the DefaultLobHandler. This would make it possible that you could get by without extracting the native connection from the WS wrapper, since there is no Oracle specific handling at that point.
Thomas
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I am using "C:\oracle\ora9i\jdbc\lib\ojdbc14.jar", I have no clue if this is the latest version of the Thin driver. I would have to assume this came with the Oracle 9i installation that is on my box.
I tried using the Default Handler just to see what would happen and I got:
SQLStateSQLExceptionTranslator - Translating SQLException with SQLState 'null' and errorCode '17002' and message [Io exception: Connection reset by peer: socket write error];
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
That's the 9i driver (based on the directory name - and the error that you encounter when the LOB is bigger than 4k). You can go to http://otn.oracle.com/software/tech/java/sqlj_jdbc/index.html
and download the 10g driver - that one should work.
If you look in beginning of the log, there is a Spring message that tells you the version of the driver.
You would also have to use this new driver in WebSphere and see if you can get by without extracting the native connection.
Thomas
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
In Tomcat, I get confirmation that it's the 10.1.0.2.0 driver and I seem to be able to use the Default handler instead of the Oracle one.
But when I try it in Websphere, I am getting 9.2.0.1.0 as the driver. Sounds like a classloader issue to me. I updated my JDBC Provider in WAS Admin to point to the new jar and restarted the whole server just to be sure but it still comes up 9.2. I'm wondering if you had the same problem upgrading to 10.2 on Websphere.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
If you are using Websphere 5.1 and ojdbc14.jar with the 9.2.0.1.0 driver, you must use the OracleLobHander with a native extractor.
If your are using the 10.1.0.2.0 driver, it seems you can use the DefaultHandler with the CommonsDbcpNativeJdbcExtractor in Tomcat (if you're using DBCP for pooling, of course). I can not seem to get the 10.1 drivers working in Websphere so I can not test this but I don't see any reason it would not work in Websphere with a native extractor.
As for the custom extractor, I managed to take Juergen's advice and write a Websphere extractor. It is extraordinarily similar to the Weblogic one, the only difference is that it has an empty constructor and the getNativeConnection implementation:
public class WebsphereNativeJdbcExtractor extends NativeJdbcExtractorAdapter {
private final static Log log = LogFactory.getLog(WebsphereNativeJdbcExtractor.class);
public WebsphereNativeJdbcExtractor() {
}
public boolean isNativeConnectionNecessaryForNativeStatements() {
return true;
}
public boolean isNativeConnectionNecessaryForNativePreparedStatements() {
return true;
}
public boolean isNativeConnectionNecessaryForNativeCallableStatements() {
return true;
}
public Connection getNativeConnection(Connection con) throws SQLException {
log.debug("Connection type: " + con.getClass().getName());
log.debug("Attempting to obtain native connection.");
try {
return (Connection) WSJdbcUtil.getNativeConnection((WSJdbcConnection)con);
}
catch (Exception ex) {
throw new DataAccessResourceFailureException("Could not obtain a native
connection from WSJdbcConnetion.", ex);
}
}
}
The Websphere specific classes are found in rsadapterspi.jar which is in the lib directory of your Websphere installation.
This seems to work fine for me (I using it to store fax images into Oracle but have been testing with much larger images to be safe ~600k). If there is room for improvement, please let me know. I am also using a JNDI datasource which is using oracle.jdbc.pool.OracleConnectionPoolDataSource as the implementation classname.
Juergen, I can send the full class to you if you like but you should be able to take a copy of the Weblogic version and create a websphere version really quick (just like you said ;-) ).
Hopefully, this will help out all of you Websphere BLOBers out there. Thanks for everybody's help.
Dave
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I'll be happy to include this in Spring 1.0.3, but please rewrite it with reflection to avoid a dependency on WebSphere libraries. (We did the same for WebLogicNativeJdbcExtractor.) Which version of WebSphere have you tested this against?
BTW, could you check whether the WebSphere connection pool actually wraps Statements, PreparedStatements, and CallableStatements? We could fine-tune the "isNativeConnectionNecessaryForXxx" methods, just returning "true" if the respective statement type is actually wrapped.
The latter are apparently not used by OracleLobHandler, but they are used by various JdbcTemplate methods if a NativeJdbcExtractor is specified there. For completeness, it would be good to properly cover statement unwrapping too.
Thomas, could you maybe check whether the WebLogic 8.1 connection pool actually wraps each of the statement types? All of the "isNativeConnectionNecessaryForXxx" methods currently return true there too.
Juergen
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I've just done a quick attempt at a reflection-based version of WebSphereNativeJdbcExtractor, for inclusion in Spring 1.0.3. Unfortunately, I don't have a WebSphere installation, so can't give it a try...
Dave, can you please run this version against your WebSphere installation? And tell me the WebSphere version number? :-)
public class WebSphereNativeJdbcExtractor extends NativeJdbcExtractorAdapter {
private static final String JDBC_ADAPTER_CONNECTION_NAME = "com.ibm.ws.rsadapter.jdbc.WSJdbcConnection";
private static final String JDBC_ADAPTER_UTIL_NAME = "com.ibm.ws.rsadapter.jdbc.WSJdbcUtil";
private final Class jdbcAdapterConnectionClass;
private final Method getNativeConnectionMethod;
public WebSphereNativeJdbcExtractor() throws ClassNotFoundException, NoSuchMethodException {
this.jdbcAdapterConnectionClass = getClass().getClassLoader().loadClass(JDBC_ADAPTER_CONNECTION_NAME);
Class jdbcAdapterUtilClass = getClass().getClassLoader().loadClass(JDBC_ADAPTER_UTIL_NAME);
this.getNativeConnectionMethod =
jdbcAdapterUtilClass.getMethod("getNativeConnection", new Class[] {this.jdbcAdapterConnectionClass});
}
public Connection getNativeConnection(Connection con) throws SQLException {
if (this.jdbcAdapterConnectionClass.isAssignableFrom(con.getClass())) {
try {
return (Connection) this.getNativeConnectionMethod.invoke(null, new Object[] {con});
}
catch (Exception ex) {
throw new DataAccessResourceFailureException("Could not invoke WebSphere's getNativeConnection method", ex);
}
}
return con;
}
}
I've omitted the "isNativeConnectionNecessaryForXxx" methods for simplicity's sake. If all statement types are wrapped by WebSphere, we'll simply let all of those methods return true.
However, as I said earlier, we should check which statement types are actually wrapped. We should also do that for WebLogic, to make our NativeJdbcExtractor implementations as comprehensive as possible.
Juergen
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I've just checked the online docs of the WebLogic 8.1 API: There doesn't seem to be a means for statement unwrapping there. So we'll need to stick to WebLogicNativeJdbcExtractor's current strategy: return true from all "isNativeConnectionNecessaryFromXxx" methods.
Juergen
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
BTW, SimpleNativeJdbcExtractor - basically, con.getMetaData().getConnection() - works for a surprising number of connection pools: Resin 2.1 and 3.0, OC4J 9.0.3, C3P0, and even Commons DBCP.
As a side note, CommonsDbcpNativeJdbcExtractor is therefore just necessary for more advanced needs, like efficient statement unwrapping.
In total, we support native JDBC extraction for a broad range of pools now: the above mentioned plus XAPool (Jonas), JBoss, WebLogic 8.1, WebSphere 5.1 (to be tested on earlier versions).
Juergen
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Yeah, I was thinking about doing it using refelction since the WebLogic version did just that, but I am under heavy time contraints and I just needed to get it done and working, especially since it is such a small part of the project.
As for the refelction verison of the class, it seems to work just fine. :-)
As for wrapped Statements, as much as I hate to admit it, I am not sure how to test for this. Do I just prepare a statement with a WSJdbcConnection object and check the type using .getClass().getName()? I can tell you that the rsadaptarspi.jar does contain WSJdbcStatement, WSJdbcPreparedStatement and WSJdbcCallableStatement classes. I would assume then that they would be used for wrapping.
My Websphere verison is 5.1.0, build b0344.02.
Hope this helps
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
OK, thanks for testing the reflection version! I'll commit the full version (including docs etc) to CVS promptly.
What I meant with testing for wrapped statements is indeed just checking the class names of created statements. If there are all those WSxxxStatement wrapper classes in the jar, I bet that they're all used, though. You could make sure by simply logging the class name of each statement type.
If you have a minute left, could you do a quick check whether the WSJdbcUtil class supports a "getNativeStatement" method or the like? This would allow for more efficient native statement retrieval in WebSphereNativeJdbcExtractor (not needed by OracleLobHandler, though).
Juergen
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks - that confirmed what I expected. All "isNativeConnectionNecessaryForXxx" methods have to return true then. So that's it: WebSphereNativeJdbcExtractor is ready! :-)
Juergen
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I am trying to get the LobHandler working with WebSphere and I am having a little trouble. I've tried using the SimpleNativeJdbcExtractor but it doesn't seemt to be working.
Iam getting this exception:
Could not create Oracle LOB; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: OracleLobHandler needs to work on OracleConnection - maybe set the nativeJdbcExtractor property?
A getClass().getName() on the conection object give me "com.ibm.ws.rsadapter.jdbc.WSJdbcConnection".
Am I assuming that perhaps I need to write a custom Extractor for this type of datasource?
Based on the error message, it looks as if you are using the OracleLobhandler and not the DefaultLobHandler. Switch to the DefaultLobHandler and see what error you get then. I don't have much experience with DB2/WS but it migh just work.
Thomas
I am using Oracle and the code works when using oracle.jdbc.pool.OracleConnectionPoolDataSource in a unit test. I think it's a problem trying to get a native connection from WSJdbcConnetion but I am not sure.
Spring 1.0.2's SimpleNativeJdbcExtractor checks Connection.getMetaData().getConnection(), which is the native Connection with many connection pools. Unfortunately, this doesn't seem to be the case with WebSphere.
So you probably need to implement a NativeJdbcExtractor for WebSphere. We have already added one for WebLogic in Spring 1.0.2, BTW. It would be great to have one for WebSphere too as of Spring 1.0.3 :-)
Take WebLogicNativeJdbcExtractor as template. Basically, derive from NativeJdbcExtractorAdapter and implement getNativeConnection (which is all that OracleLobHandler depends on) for WebSphere.
Juergen
Reading your message again - I don't know where I got DB2 from :-) What version of Oracle driver are you using. I have been successful using the latest 10g Thin driver and the DefaultLobHandler. This would make it possible that you could get by without extracting the native connection from the WS wrapper, since there is no Oracle specific handling at that point.
Thomas
I am using "C:\oracle\ora9i\jdbc\lib\ojdbc14.jar", I have no clue if this is the latest version of the Thin driver. I would have to assume this came with the Oracle 9i installation that is on my box.
I tried using the Default Handler just to see what would happen and I got:
SQLStateSQLExceptionTranslator - Translating SQLException with SQLState 'null' and errorCode '17002' and message [Io exception: Connection reset by peer: socket write error];
That's the 9i driver (based on the directory name - and the error that you encounter when the LOB is bigger than 4k). You can go to
http://otn.oracle.com/software/tech/java/sqlj_jdbc/index.html
and download the 10g driver - that one should work.
If you look in beginning of the log, there is a Spring message that tells you the version of the driver.
You would also have to use this new driver in WebSphere and see if you can get by without extracting the native connection.
Thomas
"If you look in beginning of the log, there is a Spring message that tells you the version of the driver. "
Duh. I should have figured that out. 9.2.1.0
And yes, my lobs are bigger than 4k. Why would the 9.2 driver not support BLOBs over 4k? Seems very odd.
Downloading 10g now and I'll give that a try.
>> And yes, my lobs are bigger than 4k. Why would the 9.2 driver not support BLOBs over 4k? Seems very odd.
It's Oracle :-)
They do support larger LOB's with their own proprietary features. That's why we need a special OracleLobHandler for the 9i driver.
Thomas
In Tomcat, I get confirmation that it's the 10.1.0.2.0 driver and I seem to be able to use the Default handler instead of the Oracle one.
But when I try it in Websphere, I am getting 9.2.0.1.0 as the driver. Sounds like a classloader issue to me. I updated my JDBC Provider in WAS Admin to point to the new jar and restarted the whole server just to be sure but it still comes up 9.2. I'm wondering if you had the same problem upgrading to 10.2 on Websphere.
Sorry, can't help there - I use WebLogic :-)
Sound like the old classes are available on the classpath somewhere - check the startup scripts.
Thomas
Okay. I think I finally got it working. :-)
So to sum things up:
If you are using Websphere 5.1 and ojdbc14.jar with the 9.2.0.1.0 driver, you must use the OracleLobHander with a native extractor.
If your are using the 10.1.0.2.0 driver, it seems you can use the DefaultHandler with the CommonsDbcpNativeJdbcExtractor in Tomcat (if you're using DBCP for pooling, of course). I can not seem to get the 10.1 drivers working in Websphere so I can not test this but I don't see any reason it would not work in Websphere with a native extractor.
As for the custom extractor, I managed to take Juergen's advice and write a Websphere extractor. It is extraordinarily similar to the Weblogic one, the only difference is that it has an empty constructor and the getNativeConnection implementation:
import com.ibm.ws.rsadapter.jdbc.WSJdbcConnection;
import com.ibm.ws.rsadapter.jdbc.WSJdbcUtil;
public class WebsphereNativeJdbcExtractor extends NativeJdbcExtractorAdapter {
private final static Log log = LogFactory.getLog(WebsphereNativeJdbcExtractor.class);
public WebsphereNativeJdbcExtractor() {
}
public boolean isNativeConnectionNecessaryForNativeStatements() {
return true;
}
public boolean isNativeConnectionNecessaryForNativePreparedStatements() {
return true;
}
public boolean isNativeConnectionNecessaryForNativeCallableStatements() {
return true;
}
public Connection getNativeConnection(Connection con) throws SQLException {
log.debug("Connection type: " + con.getClass().getName());
log.debug("Attempting to obtain native connection.");
try {
return (Connection) WSJdbcUtil.getNativeConnection((WSJdbcConnection)con);
}
catch (Exception ex) {
throw new DataAccessResourceFailureException("Could not obtain a native
connection from WSJdbcConnetion.", ex);
}
}
}
The Websphere specific classes are found in rsadapterspi.jar which is in the lib directory of your Websphere installation.
This seems to work fine for me (I using it to store fax images into Oracle but have been testing with much larger images to be safe ~600k). If there is room for improvement, please let me know. I am also using a JNDI datasource which is using oracle.jdbc.pool.OracleConnectionPoolDataSource as the implementation classname.
Juergen, I can send the full class to you if you like but you should be able to take a copy of the Weblogic version and create a websphere version really quick (just like you said ;-) ).
Hopefully, this will help out all of you Websphere BLOBers out there. Thanks for everybody's help.
Dave
I'll be happy to include this in Spring 1.0.3, but please rewrite it with reflection to avoid a dependency on WebSphere libraries. (We did the same for WebLogicNativeJdbcExtractor.) Which version of WebSphere have you tested this against?
BTW, could you check whether the WebSphere connection pool actually wraps Statements, PreparedStatements, and CallableStatements? We could fine-tune the "isNativeConnectionNecessaryForXxx" methods, just returning "true" if the respective statement type is actually wrapped.
The latter are apparently not used by OracleLobHandler, but they are used by various JdbcTemplate methods if a NativeJdbcExtractor is specified there. For completeness, it would be good to properly cover statement unwrapping too.
Thomas, could you maybe check whether the WebLogic 8.1 connection pool actually wraps each of the statement types? All of the "isNativeConnectionNecessaryForXxx" methods currently return true there too.
Juergen
I've just done a quick attempt at a reflection-based version of WebSphereNativeJdbcExtractor, for inclusion in Spring 1.0.3. Unfortunately, I don't have a WebSphere installation, so can't give it a try...
Dave, can you please run this version against your WebSphere installation? And tell me the WebSphere version number? :-)
public class WebSphereNativeJdbcExtractor extends NativeJdbcExtractorAdapter {
private static final String JDBC_ADAPTER_CONNECTION_NAME = "com.ibm.ws.rsadapter.jdbc.WSJdbcConnection";
private static final String JDBC_ADAPTER_UTIL_NAME = "com.ibm.ws.rsadapter.jdbc.WSJdbcUtil";
private final Class jdbcAdapterConnectionClass;
private final Method getNativeConnectionMethod;
public WebSphereNativeJdbcExtractor() throws ClassNotFoundException, NoSuchMethodException {
this.jdbcAdapterConnectionClass = getClass().getClassLoader().loadClass(JDBC_ADAPTER_CONNECTION_NAME);
Class jdbcAdapterUtilClass = getClass().getClassLoader().loadClass(JDBC_ADAPTER_UTIL_NAME);
this.getNativeConnectionMethod =
jdbcAdapterUtilClass.getMethod("getNativeConnection", new Class[] {this.jdbcAdapterConnectionClass});
}
public Connection getNativeConnection(Connection con) throws SQLException {
if (this.jdbcAdapterConnectionClass.isAssignableFrom(con.getClass())) {
try {
return (Connection) this.getNativeConnectionMethod.invoke(null, new Object[] {con});
}
catch (Exception ex) {
throw new DataAccessResourceFailureException("Could not invoke WebSphere's getNativeConnection method", ex);
}
}
return con;
}
}
I've omitted the "isNativeConnectionNecessaryForXxx" methods for simplicity's sake. If all statement types are wrapped by WebSphere, we'll simply let all of those methods return true.
However, as I said earlier, we should check which statement types are actually wrapped. We should also do that for WebLogic, to make our NativeJdbcExtractor implementations as comprehensive as possible.
Juergen
I've just checked the online docs of the WebLogic 8.1 API: There doesn't seem to be a means for statement unwrapping there. So we'll need to stick to WebLogicNativeJdbcExtractor's current strategy: return true from all "isNativeConnectionNecessaryFromXxx" methods.
Juergen
BTW, SimpleNativeJdbcExtractor - basically, con.getMetaData().getConnection() - works for a surprising number of connection pools: Resin 2.1 and 3.0, OC4J 9.0.3, C3P0, and even Commons DBCP.
As a side note, CommonsDbcpNativeJdbcExtractor is therefore just necessary for more advanced needs, like efficient statement unwrapping.
In total, we support native JDBC extraction for a broad range of pools now: the above mentioned plus XAPool (Jonas), JBoss, WebLogic 8.1, WebSphere 5.1 (to be tested on earlier versions).
Juergen
Yeah, I was thinking about doing it using refelction since the WebLogic version did just that, but I am under heavy time contraints and I just needed to get it done and working, especially since it is such a small part of the project.
As for the refelction verison of the class, it seems to work just fine. :-)
As for wrapped Statements, as much as I hate to admit it, I am not sure how to test for this. Do I just prepare a statement with a WSJdbcConnection object and check the type using .getClass().getName()? I can tell you that the rsadaptarspi.jar does contain WSJdbcStatement, WSJdbcPreparedStatement and WSJdbcCallableStatement classes. I would assume then that they would be used for wrapping.
My Websphere verison is 5.1.0, build b0344.02.
Hope this helps
OK, thanks for testing the reflection version! I'll commit the full version (including docs etc) to CVS promptly.
What I meant with testing for wrapped statements is indeed just checking the class names of created statements. If there are all those WSxxxStatement wrapper classes in the jar, I bet that they're all used, though. You could make sure by simply logging the class name of each statement type.
If you have a minute left, could you do a quick check whether the WSJdbcUtil class supports a "getNativeStatement" method or the like? This would allow for more efficient native statement retrieval in WebSphereNativeJdbcExtractor (not needed by OracleLobHandler, though).
Juergen
Looks like it does. I placed this code in the getNativeConnection() method and got these results.
public Connection getNativeConnection(Connection con) throws SQLException {
log.debug("Connection type: " + con.getClass().getName());
Statement stmt = con.createStatement();
log.debug("Statement type: " + stmt.getClass().getName());
stmt = null;
PreparedStatement ps = con.prepareStatement("SELECT * FROM DUAL");
log.debug("PreparedStatement type: " + ps.getClass().getName());
ps = null;
CallableStatement cs = con.prepareCall("CALL SP");
log.debug("CallableStatement type: " + cs.getClass().getName());
cs = null;
...
}
DEBUG ... WebSphereNativeJdbcExtractor - Connection type: com.ibm.ws.rsadapter.jdbc.WSJdbcConnection
DEBUG ... WebSphereNativeJdbcExtractor - Statement type: com.ibm.ws.rsadapter.jdbc.WSJdbcStatement
DEBUG ... WebSphereNativeJdbcExtractor - PreparedStatement type: com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement
DEBUG ... WebSphereNativeJdbcExtractor - CallableStatement type: com.ibm.ws.rsadapter.jdbc.WSJdbcCallableStatement
Unforunately, the only getNativeXxx method in the WSJdbcUtil class is getNativeConnection().
Thanks - that confirmed what I expected. All "isNativeConnectionNecessaryForXxx" methods have to return true then. So that's it: WebSphereNativeJdbcExtractor is ready! :-)
Juergen