Re: DBCP, Oracle9i Blob/Clob broken
Danilo Tommasina wrote: we just upgraded to OJB 1.0.2, the fixes in Oracle9i platform work like a charm many, many thanks again for the great work to everybody that contributed. Hi Danilo, that's great to hear! Thank you for contributing valuable feedback, code suggestions and for reporting back to the list. Cheers, Martin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: DBCP, Oracle9i Blob/Clob broken
Hi Martin, we just upgraded to OJB 1.0.2, the fixes in Oracle9i platform work like a charm many, many thanks again for the great work to everybody that contributed. bye danilo Nice work, many thanks. cheers danilo Danilo Tommasina wrote: I ended up implementing a custom DBCP connection factory that is using a BasicDataSource instead of a PooledDataSource. Since the documentation of the DBCP stuff is really ugly I didn't find out what the difference really is between the PooledDataSource and the BasicDataSource. Fact is that with the BasicDataSource based implementation the problem with the MAX_OPEN_CURSORS is solved, because a fixed-size PreparedStatement cache is used. I had a look in DBCP CVS and the BasicDataSource is really only a specialiced implementation of PooledDataSource with more user-friendly getter/setters. It will use AbandonedObjectPool or GenericObjectPool for connection "storage". With PooledDataSource you can choose any underlying pool you want and just supply a factory that could eg return your own pool implementations. (But you have no "nice" setter like setMaxIdle, since you can't rely on any specifics on the object pool.) I think it makes sence to keep PooledDataSource in OJB, which enables the configuration to be more generic in OJB1.1. However, I was able to work around the MAX_OPEN_CURSORS error by adding a check in the default DBCP connection factory: if platform is Oracle9i then don't use DBCP PreparedStatement caching. After fixing so that the factory calls initializeJdbcConnection properly, this allows the Oracle9i platform impl to manage the statement caching. Note that it is necessary to implement/override also the releaseAllResources() method for closing the pooled connections on application shutdown. This is now fixed in the DBCP factory in CVS. These changes are only in OJB_1_0_RELEASE branch yet, I will now merge them with head. Regards, Martin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: DBCP, Oracle9i Blob/Clob broken
Nice work, many thanks. cheers danilo Danilo Tommasina wrote: I ended up implementing a custom DBCP connection factory that is using a BasicDataSource instead of a PooledDataSource. Since the documentation of the DBCP stuff is really ugly I didn't find out what the difference really is between the PooledDataSource and the BasicDataSource. Fact is that with the BasicDataSource based implementation the problem with the MAX_OPEN_CURSORS is solved, because a fixed-size PreparedStatement cache is used. I had a look in DBCP CVS and the BasicDataSource is really only a specialiced implementation of PooledDataSource with more user-friendly getter/setters. It will use AbandonedObjectPool or GenericObjectPool for connection "storage". With PooledDataSource you can choose any underlying pool you want and just supply a factory that could eg return your own pool implementations. (But you have no "nice" setter like setMaxIdle, since you can't rely on any specifics on the object pool.) I think it makes sence to keep PooledDataSource in OJB, which enables the configuration to be more generic in OJB1.1. However, I was able to work around the MAX_OPEN_CURSORS error by adding a check in the default DBCP connection factory: if platform is Oracle9i then don't use DBCP PreparedStatement caching. After fixing so that the factory calls initializeJdbcConnection properly, this allows the Oracle9i platform impl to manage the statement caching. Note that it is necessary to implement/override also the releaseAllResources() method for closing the pooled connections on application shutdown. This is now fixed in the DBCP factory in CVS. These changes are only in OJB_1_0_RELEASE branch yet, I will now merge them with head. Regards, Martin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: DBCP, Oracle9i Blob/Clob broken
Danilo Tommasina wrote: I ended up implementing a custom DBCP connection factory that is using a BasicDataSource instead of a PooledDataSource. Since the documentation of the DBCP stuff is really ugly I didn't find out what the difference really is between the PooledDataSource and the BasicDataSource. Fact is that with the BasicDataSource based implementation the problem with the MAX_OPEN_CURSORS is solved, because a fixed-size PreparedStatement cache is used. I had a look in DBCP CVS and the BasicDataSource is really only a specialiced implementation of PooledDataSource with more user-friendly getter/setters. It will use AbandonedObjectPool or GenericObjectPool for connection "storage". With PooledDataSource you can choose any underlying pool you want and just supply a factory that could eg return your own pool implementations. (But you have no "nice" setter like setMaxIdle, since you can't rely on any specifics on the object pool.) I think it makes sence to keep PooledDataSource in OJB, which enables the configuration to be more generic in OJB1.1. However, I was able to work around the MAX_OPEN_CURSORS error by adding a check in the default DBCP connection factory: if platform is Oracle9i then don't use DBCP PreparedStatement caching. After fixing so that the factory calls initializeJdbcConnection properly, this allows the Oracle9i platform impl to manage the statement caching. Note that it is necessary to implement/override also the releaseAllResources() method for closing the pooled connections on application shutdown. This is now fixed in the DBCP factory in CVS. These changes are only in OJB_1_0_RELEASE branch yet, I will now merge them with head. Regards, Martin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: DBCP, Oracle9i Blob/Clob broken
Danilo Tommasina wrote: There is a problem with using Oracle and the default DBCP connection factory, since PreparedStatement caching in DBCP will "eat" all your Oracle cursors rather quickly. See the release notes for a work-around (set PSPoolFactory==null). In OJB 1.1 the configuration mechanism will be a bit more flexible so you will probably not see any new connection factories in the 1.0.x line of releases. Dang, I spent a couple on days on this issue last week, I didn't saw the release notes... They were not there last week so you didn't miss out. ;) I did not have the time to do extensive testing of the DBCP stuff until now and several of the regression tests will catch if cursors are leaking. I ended up implementing a custom DBCP connection factory that is using a BasicDataSource instead of a PooledDataSource. Since the documentation of the DBCP stuff is really ugly I didn't find out what the difference really is between the PooledDataSource and the BasicDataSource. Fact is that with the BasicDataSource based implementation the problem with the MAX_OPEN_CURSORS is solved, because a fixed-size PreparedStatement cache is used. Interesting! Thank you for your code, I will review it and the DBCP factory again after the release of OJB1.0.2. Regards, Martin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: DBCP, Oracle9i Blob/Clob broken
Hi again,
That's perfectly right -- yesterday I added a recursive generic
unwrap-function to the default Oracle9i platform that will try to unwrap
Connection and PreparedStatement objects until and Oracle-compatible
class is found.
Current implementation knows about Oracle10g, Commons DBCP, P6Spy and
BEA WebLogic.
wow really nice...
There is a problem with using Oracle and the default DBCP connection
factory, since PreparedStatement caching in DBCP will "eat" all your
Oracle cursors rather quickly. See the release notes for a work-around
(set PSPoolFactory==null). In OJB 1.1 the configuration mechanism will
be a bit more flexible so you will probably not see any new connection
factories in the 1.0.x line of releases.
Dang, I spent a couple on days on this issue last week, I didn't saw the release notes...
I ended up implementing a custom DBCP connection factory that is using a BasicDataSource instead of a PooledDataSource. Since the documentation of the DBCP stuff is really ugly I
didn't find out what the difference really is between the PooledDataSource and the BasicDataSource. Fact is that with the BasicDataSource based implementation the problem with the
MAX_OPEN_CURSORS is solved, because a fixed-size PreparedStatement cache is used.
below the code we are using:
Sorry, I didn't took out the code specific to our application, static calls to ORSConfiguration. are used to load stuff from our config. files and could be replaced by
constants or values loaded from OJB config, calls to ORSLogger.SYSTEM. are used for our internal logging routines.
Note that it is necessary to implement/override also the releaseAllResources() method for closing the pooled connections on application shutdown.
public class ORSConnFactDBCPImpl extends ConnectionFactoryDBCPImpl {
private BasicDataSource pooledDs = null;
/** Creates a new instance of ORSConnFactDBCPImpl */
public ORSConnFactDBCPImpl() {}
/** Overrides ConnectionFactoryDBCPImpl.createConnectionFactory(
JdbcConnectionDescriptor jcd ) */
protected org.apache.commons.dbcp.ConnectionFactory
createConnectionFactory( JdbcConnectionDescriptor jcd ) {
// This is custom code for setting custom JDBC driver
attributes/properties
// adding support for extracting the attributes from the
JdbcConnectionDescriptor
// would be the required implementation of (my) feature request:
// OJB298 (Setting custom JDBC driver tuning options trough
jdbc-connection-descriptor)
Properties connProp = ORSConfiguration.getJDBCDriverOptions();
// Add user and password
connProp.put( "user", jcd.getUserName() );
connProp.put( "password", jcd.getPassWord() );
return new DriverManagerConnectionFactory( super.getDbURL( jcd ),
connProp );
}
/**
* Override this method to setup your own pool
*/
protected DataSource setupPool( JdbcConnectionDescriptor jcd ) {
ORSLogger.SYSTEM.debug( "Creating new ORS DBCP connection pool" );
try {
ClassHelper.newInstance( jcd.getDriver() );
} catch ( InstantiationException e ){
ORSLogger.SYSTEM.fatal( "Unable to instantiate the driver class: " +
jcd.getDriver() + " in ConnectionFactoryDBCImpl!" , e );
} catch (IllegalAccessException e ) {
ORSLogger.SYSTEM.fatal( "IllegalAccessException while instantiating the driver
class: " + jcd.getDriver() + " in ConnectionFactoryDBCImpl!" , e );
} catch (ClassNotFoundException e ) {
ORSLogger.SYSTEM.fatal( "Could not find the driver class : " +
jcd.getDriver() + " in ConnectionFactoryDBCImpl!" , e );
}
// get the configuration for the connection pool
GenericObjectPool.Config conf =
jcd.getConnectionPoolDescriptor().getObjectPoolConfig();
this.pooledDs = new BasicDataSource();
// Init using the data from connection descriptor in repository.xml
this.pooledDs.setDefaultAutoCommit( jcd.getUseAutoCommit() !=
JdbcConnectionDescriptor.AUTO_COMMIT_SET_FALSE );
this.pooledDs.setDriverClassName( jcd.getDriver() );
this.pooledDs.setMaxActive( conf.maxActive );
this.pooledDs.setMaxIdle( conf.maxIdle );
this.pooledDs.setMaxWait( conf.maxWait );
this.pooledDs.setMinEvictableIdleTimeMillis(
conf.minEvictableIdleTimeMillis );
this.pooledDs.setMinIdle( conf.minIdle );
this.pooledDs.setNumTestsPerEvictionRun( conf.numTestsPerEvictionRun );
this.pooledDs.setPassword( jcd.getPassWord() );
this.pooledDs.setTestOnBorrow( conf.testOnBorrow );
this.pooledDs.setTestOnReturn( conf.testOnReturn );
this.pooledDs.setTestWhileIdle( conf.testWhileIdle );
this.pooledDs.setTimeBetweenEvictionRunsMillis(
conf.timeBetweenEvictionRunsMillis );
this.pooledDs.setUrl( super.getDbURL( jcd ) );
this.pooledDs.setUsername( jcd.getUserName() );
this.pooledDs.setValidationQuery(
jcd
Re: DBCP, Oracle9i Blob/Clob broken
Danilo Tommasina wrote:
I found out that there is the method getInnermostDelegate() on the DBCP
DelegatingPreparedStatement calling this will return the original
PreparedStatement independently on how many hierarchies of wrappers DBCP
is using.
That's perfectly right -- yesterday I added a recursive generic
unwrap-function to the default Oracle9i platform that will try to unwrap
Connection and PreparedStatement objects until and Oracle-compatible
class is found.
Current implementation knows about Oracle10g, Commons DBCP, P6Spy and
BEA WebLogic.
It's easy to add new methods to the array of known names/argument types.
So for the first time ever the Oracle9i platform could handle large CLOB
and BLOB using both P6Spy and DBCP (which will render the Connection
"superwrapped" in multiple wrappers).
There is a problem with using Oracle and the default DBCP connection
factory, since PreparedStatement caching in DBCP will "eat" all your
Oracle cursors rather quickly. See the release notes for a work-around
(set PSPoolFactory==null). In OJB 1.1 the configuration mechanism will
be a bit more flexible so you will probably not see any new connection
factories in the 1.0.x line of releases.
Another issue is the "accesToUnderlyingConnection" in DBCP which can
stop the large LOB-support from working. This will also not be set
explicitly in 1.0.x since that requires a new connection factory impl or
hardcoding a new configuration attribute. In OJB1.1 a generic
name/value-approach till make it easier to pass implementation-specific
parameters to the connection factories. However, I made some testing
with the shipped DBCP v1.1 and I was able to unwrap the Connection by
default (which has not been the case with previous versions).
Ok I didn't think at the runtime dependencies, following code uses
reflection and avoids these external dependencies (maybe you already
implemented it like this)
PreparedStatement innerPS;
try
{
Method delgGetter = ClassHelper.getMethod( ps,
"getInnermostDelegate", null );
innerPS = ( delgGetter != null ) ? (PreparedStatement)
delgGetter.invoke( ps, null ) : ps;
} catch ( Exception ignore ) {
innerPS = ps;
}
Yes, that's pretty much exactly how the code in CVS looks now (although
the method name/type are parametrized to try multiple options).
Testing of the new functionality is appreciated; either check out from
CVS on OJB_1_0_RELEASE branch or wait a few days until OJB1.0.2 is
released. (Vote has already passed, we are just holding off while
investigating an issue in the ODMG.)
Regards,
Martin
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: DBCP, Oracle9i Blob/Clob broken
Hi Martin,
thank you for your answer.
I found out that there is the method getInnermostDelegate() on the DBCP DelegatingPreparedStatement calling this will return the original PreparedStatement independently on how
many hierarchies of wrappers DBCP is using.
I reviewed your suggested DBCP-fix and this will not make it into
the default Oracle9i platform, as the imports would add a DBCP run-time
dependency when using OJB and Oracle9i platform (which is not currently
the case and would apply even for users that don't want DBCP).
I will try to make a more flexible support for all types of wrapped
conenctions using reflection calls. If this is not possible your code could
always extend the default platform and make a new "PlatformDbcpOracle9iImp",
but this would be my personal last choice...
Ok I didn't think at the runtime dependencies, following code uses
reflection and avoids these external dependencies (maybe you already
implemented it like this)
PreparedStatement innerPS;
try
{
Method delgGetter = ClassHelper.getMethod( ps, "getInnermostDelegate", null
);
innerPS = ( delgGetter != null ) ? (PreparedStatement) delgGetter.invoke(
ps, null ) : ps;
} catch ( Exception ignore ) {
innerPS = ps;
}
where ps is the 'potentially' wrappped PreparedStatement and innerPS is the
original unwrapped PreparedStatement on which the setBLOB/CLOB methods can be
called.
bye
danilo
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: DBCP, Oracle9i Blob/Clob broken
Danilo Tommasina risksys.com> writes: > When using the Oracle9i platform storing Blobs/Clobs does not work when using > the DBCP connection factory > (org.apache.ojb.broker.accesslayer.ConnectionFactoryDBCPImpl) Hi Danilo, thank you for your patch and suggestions. Unfortunately this is true not only for DBCP, but all types of wrapped connections (eg in managed environments like BEA WebLogic). And as you so carefully spotted, using P6Spy will render the same problem. BLOB/CLOB is only one out of two known issues with wrapped connections. The other one is batching and the 2k/4k BLOB/CLOB length limits with the oracle:thin driver. If you select batch-mode=true these annoying limits will reappear, even if using PlatformOracle9iImpl. (Another item on the TODO-list.) Previously there has been a special platform added to circument the wrapped connection problem (PlatformWLOracle9iImpl for WebLogic), but this solution is IMHO also not satisfactory due to code duplication and too many platforms. I reviewed your suggested DBCP-fix and this will not make it into the default Oracle9i platform, as the imports would add a DBCP run-time dependency when using OJB and Oracle9i platform (which is not currently the case and would apply even for users that don't want DBCP). I will try to make a more flexible support for all types of wrapped conenctions using reflection calls. If this is not possible your code could always extend the default platform and make a new "PlatformDbcpOracle9iImp", but this would be my personal last choice... > And one more side notice: why the Oracle and Sybase (and maybe other) > platforms are not configured to use SQL92 syntax? Often this is simply because one developer adds a new platform method and do not have access to all RDBMS that OJB support, and therefore have to make a "qualified guess" for good defaults. If no-one using a specific platform yells this can go unnoticed for some time... > 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. Agreed. Since Oracle9i passes all regression tests regardless of ORACLE/SQL92 join syntax I added your suggestion to CVS (for both 1.0.x branch and HEAD). The upcoming OJB 1.0.2 release will use SQL92 join syntax for the Oracle9i platform. (Sybase is unknown territory for me.) Regards, Martin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
DBCP, Oracle9i Blob/Clob broken
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-
