Hi,
I just saw another post about Oracle 9i and Blobs, so if somebody is going to get a look into it, here is some more:
When using the Oracle9i platform storing Blobs/Clobs does not work when using the DBCP connection factory (org.apache.ojb.broker.accesslayer.ConnectionFactoryDBCPImpl)
This is because of the code in PlatformOracle9iImpl:
methodSetBlob = ClassHelper.getMethod( ps, "setBLOB", new Class [] {
Integer.TYPE, blobClass } );The problem is that when using the DBCP connection factory the variable 'ps' does not contain an instance of proprietary OraclePreparedStatement with the proprietary setBLOB method. It is first necessary to extract the OraclePreparedStatemnt from the 'DBCP PreparedStatemnt stack' until we reach the original statement. Furthermore the Connection object bound to the OraclePreparedStatement must be used and not the one bound to the DBCP PreparedStatement.
And one more side notice: why the Oracle and Sybase (and maybe other) platforms are not configured to use SQL92 syntax? At least in Sybase there are less limitations on outer joins when using this syntax and it should be granted that you get same results on all platform when using the SQL92.
Below the code of the Platform implementation that we are using for Oracle 9i, configured for SQL92 syntax and with the workaround for the DBCP connection factory.
The code is ugly and may break if the DBCP API is modified, however I do not really see any better solution. (Maybe a recursive loop until the getDelegate() method can be found per reflection?)
Note that if p6spy is beeing used, the code will also not work, because the setBLOB method is also not available in the p6spy PreparedStatement wrapper.
And sorry for a couple of weeks more I'll be unable to post any test-cases.
public class PlatformANSIOracle9iImpl extends PlatformOracle9iImpl { /** Creates a new instance of PlatformOracle9iImpl */
public PlatformANSIOracle9iImpl() {
super();
} /** Get join syntax type for this RDBMS - one on of the constants from
* JoinSyntaxType interface
* @return SQL92_NOPAREN_JOIN_SYNTAX
*/
public byte getJoinSyntaxType() {
return SQL92_NOPAREN_JOIN_SYNTAX;
} /** @see Platform#setObjectForStatement */
public void setObjectForStatement( PreparedStatement ps, int index, Object
value, int sqlType ) throws SQLException {
boolean blobHandlingSupported = false;
boolean clobHandlingSupported = false;
Method methodSetBlob = null;
Method methodSetClob = null;
PreparedStatement innerPS;
if ( ps instanceof DelegatingPreparedStatement ) {
innerPS = (PreparedStatement) ((DelegatingPreparedStatement)
ps).getDelegate();
if ( innerPS instanceof PoolablePreparedStatement ) {
innerPS = (PreparedStatement) ((PoolablePreparedStatement)
innerPS).getDelegate();
}
} else {
innerPS = ps;
} // Check for Oracle JDBC-driver LOB-support
if ( sqlType == Types.CLOB ) {
try {
Class clobClass = ClassHelper.getClass( "oracle.sql.CLOB",
false );
methodSetClob = ClassHelper.getMethod( innerPS, "setCLOB", new
Class [] { Integer.TYPE, clobClass } );
clobHandlingSupported = methodSetClob != null;
} catch ( Exception ignore ) {
// ignore it
}
} else if (sqlType == Types.BLOB ) {
try {
Class blobClass = ClassHelper.getClass( "oracle.sql.BLOB",
false );
methodSetBlob = ClassHelper.getMethod( innerPS, "setBLOB", new
Class [] { Integer.TYPE, blobClass } );
blobHandlingSupported = methodSetBlob != null;
} catch ( Exception ignore ) {
// ignore it
}
} if ( clobHandlingSupported && ( value instanceof String ) ) {
try {
Object clob = Oracle9iLobHandler.createCLOBFromString(
innerPS.getConnection(), (String) value);
methodSetClob.invoke( innerPS, new Object[] { new
Integer(index), clob } );
} catch (Exception e) {
throw new SQLException( e.getLocalizedMessage() );
}
} else if ( blobHandlingSupported && ( value instanceof byte[] ) ) {
try {
Object blob = Oracle9iLobHandler.createBLOBFromByteArray(
innerPS.getConnection(), (byte[]) value );
methodSetBlob.invoke( innerPS, new Object[] {new Integer( index
), blob } );
} catch ( Exception e ) {
throw new SQLException( e.getLocalizedMessage() );
}
} else {
// Fall-through to superclass
super.setObjectForStatement( ps, index, value, sqlType );
}
}
}
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
