User 8688ffe688
26-07-2012 21:19:23
Unable to insert/update structure using JChemBase UpdateHandler Java API with Tomcat 6.x and 7.x. Stack trace below.
java.lang.ClassCastException: org.apache.tomcat.dbcp.dbcp.DelegatingResultSet cannot be cast to oracle.jdbc.OracleResultSet
at chemaxon.jchem.db.UpdateHandlerOracle.updateBigLOBs(UpdateHandlerOracle.java:120)
at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:2404)
at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:2270)
at com.tsd.chemq.web.QueryManager$HandleRecord.update(Unknown Source)
at com.tsd.chemq.web.QueryManager$HandleRecord.run(Unknown Source)
at com.tsd.web.Controller.handle(Unknown Source)
at org.webmacro.servlet.WMServlet.doRequest(WMServlet.java:274)
at org.webmacro.servlet.WMServlet.doPost(WMServlet.java:232)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:151)
at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:129)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:151)
at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:129)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at jespa.http.HttpSecurityService.doFilter(HttpSecurityService.java:1508)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:225)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
at org.apache.catalina.authenticator.SingleSignOn.invoke(SingleSignOn.java:309)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1001)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:585)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Probably better to use java.sql.* objects instead of casting to vendor specific objects which often times will not be accessible when going through a connection pool mechanism. We have found that updating blob columns using java.sql.PreparedStatement.setBinaryStream() to be fool proof and work across numerous databases (Oracle, MS SQL Server, PostgreSQL, MySQL).
ChemAxon aa7c50abf8
27-07-2012 08:08:14
We're sorry, but currently you have to use Oracle connection pool (part of the Oracle JDBC drivers) to connect to Oracle with JChem Base. (JChem Base currently uses Oracle-specific API and we currently don't support the Wrapper interface either.) We plan to remove this limitation.
Peter
PS: I think there is a theoretical possibility of implementing a connection pool which exposes java.sql objects which not merely wrap, but also dynamically proxy the vendor-specific interfaces of the underlying JDBC driver. Perhaps there are such smart implementations amongst (older) connection pool products.
User 8688ffe688
27-07-2012 15:59:05
Instead of removing could you do an instanceof check for "oracle.jdbc.OracleResultSet" before casting and have an else block to handle other cases, which I suspect you are already handling for other databases. We do not see this problem on PostgreSQL, MS SQL Server or MySQL.
I did some preliminary investigation on attempting to switch Tomcat's connection pool management to use oracle.jdbc.pool.OracleDataSource and that failed with NPE. Since Tomcat's connection pools are a solved problem, I'd rather not spend any more time on a vendor specific implementation of connection management. I look forward to a fix in the future release.
ChemAxon aa7c50abf8
27-07-2012 16:15:28
Instead of removing could you do an instanceof check for "oracle.jdbc.OracleResultSet" before casting and have an else block to handle other cases.
Thanks for the tip. Indeed, as the saying goes: "A 3lb hammer is sometimes exactly the right tool to get a screw set in quickly."
ChemAxon aa7c50abf8
03-08-2012 19:48:32
One potential work-around (applicable or not depending on your circumstances) is to use an older version of DBCP, such as 1.2.2, whose pooled connections return the wrapped connection object on executing pooledConnection.getMetaData().getConnection() (the code currently used by JCB to obtain the wrapped connection).
ChemAxon 61b4fee994
07-08-2012 09:40:40
Hi,
We will use the java.sql.* package and setBinaryStream in later releases when OracleResultSet casting check fails, as you suggested.
Thanks for your help,
Tamas
ChemAxon 61b4fee994
16-08-2012 11:00:17
Hi,
JChem 5.10.3. was released with this fix, I hope it works now with Tomcat.
Tamas
User 8688ffe688
07-11-2012 15:17:32
UpdateHandler is still trying to attempt to use Oracle specifics instead of using java.sql.* interfaces libraries. Exception below:
Uncaught exception: org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper cannot be cast to oracle.jdbc.OracleConnection
ChemAxon aa7c50abf8
07-11-2012 17:31:42
We're sorry to hear this.
Are you sure the error occurs in UpdateHandler? I found no place in UpdateHanlder or related code which would cast to OracleConnection
Please, could you provide the full Java stack trace?
The following test case works for us with Apache commons pool version 1.6 and Apache DBCP 1.4.jar (and threw the same exception you intially reported before we applied what we thought would be a fix):
public class UpdateHandlerJc3290Test extends TestCase {
private GenericObjectPool connectionPool;
private ConnectionHandler ch;
@Override
public void setUp() throws Exception {
Properties dbProps = DBTools.getConnectionProperties();
System.out.println("dbProps=" + dbProps);
Class.forName(dbProps.getProperty("connection.jdbcDriver"));
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
dbProps.getProperty("connection.jdbcUrl"),
dbProps.getProperty("connection.login"),
dbProps.getProperty("connection.password"));
String propertyTableName = dbProps.getProperty("connection.propertyTable");
connectionPool = new GenericObjectPool(null, -1);
connectionPool.setMaxIdle(-1);
connectionPool.setTimeBetweenEvictionRunsMillis(10000);
connectionPool.setNumTestsPerEvictionRun(10000);
connectionPool.setMinEvictableIdleTimeMillis(30000);
PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
connectionFactory, connectionPool, null, null, false, false);
connectionPool.setFactory(poolableConnectionFactory);
PoolingDataSource dataSource = new PoolingDataSource(connectionPool);
ch = new ConnectionHandler(dataSource.getConnection(),
propertyTableName, true);
Connection tmpConnection = dataSource.getConnection();
try {
tmpConnection.setAutoCommit(true);
try {
CacheRegistrationUtil cru = new CacheRegistrationUtil(
new ConnectionHandler(tmpConnection, propertyTableName,
true));
cru.registerCache();
} finally {
tmpConnection.setAutoCommit(false);
}
} finally {
tmpConnection.close();
}
}
@Override
public void tearDown() throws Exception {
try {
ch.close();
} finally {
connectionPool.close();
}
}
public void testSimple() throws Exception {
final String tableName = "jc3290";
final String query = "c1ccccc1";
final String[] targets = {
"Br",
"Brc1ccccc1",
"CN1CCC23C4OC5C2C(CC1C3CCC4O)C(CC5O)C1[C@@H]2[C@@H]3C=C[C@H](O)[C@@H]4OC5=C(C1=CC(=C5O)C1=C[C@H]5[C@H]6CC7=CC(=C(O)C8=C7[C@@]5(CCN6C)[C@@H](O8)[C@H]1O)C1=C[C@H]5[C@H]6CC7=C(C=C(O)C8=C7[C@@]5(CCN6C)[C@@H](O8)[C@H]1O)C1C5CCC(O)C6OC7C(O)CCC8C1N(C)CCC78C56)[C@]34CCN2C" };
System.out.println("ch.getConnection().getClass().getName()=" + ch.getConnection().getClass().getName());
DBTools.deleteTable(ch, tableName, false);
DBTools.createTable(ch, tableName, null);
UpdateHandler uh = new UpdateHandler(ch, UpdateHandler.INSERT_WITH_ID,
tableName, "");
try {
for (int ix = 0; ix < targets.length; ix++) {
insert(uh, targets[ix], ix + 1);
}
} finally {
uh.close();
}
JChemSearch jcSearch = new JChemSearch();
jcSearch.setStructureTable(tableName);
jcSearch.setSearchOptions(new JChemSearchOptions(
SearchConstants.SUBSTRUCTURE));
jcSearch.setConnectionHandler(ch);
jcSearch.setQueryStructure(query);
jcSearch.run();
int[] ids = jcSearch.getResults();
assertEquals(2, ids.length);
jcSearch.setQueryStructure("*");
jcSearch.run();
ids = jcSearch.getResults();
assertEquals(3, ids.length);
}
private void insert(UpdateHandler uh, String target, int i)
throws SQLException, MolFormatException {
uh.setStructure(new MolHandler(target).toFormat("mrv"));
uh.setID(i);
uh.execute();
}
}
What kind of connection pool do you use?
Thanks
Peter
User 8688ffe688
08-11-2012 16:51:30
Below is the stack trace:
Uncaught exception: org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper cannot be cast to oracle.jdbc.OracleConnection
chemaxon.jchem.db.UpdateHandlerOracle.getCLOB(UpdateHandlerOracle.java:59)
chemaxon.jchem.db.UpdateHandler.setFieldCdStructure(UpdateHandler.java:1749)
chemaxon.jchem.db.UpdateHandler.setFields(UpdateHandler.java:1494)
chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:2155)
chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:2091)
com.tsd.syrchem.reg.JchemLoader.insert(Unknown Source)
com.tsd.syrchem.reg.JchemLoader.insertDb(Unknown Source)
com.tsd.syrchem.RegistrarManager$HandleCompound.registerEntry(Unknown Source)
com.tsd.syrchem.RegistrarManager$HandleCompound.run(Unknown Source)
com.tsd.web.AppManager.handleWrapper(AppManager.java:445)
com.tsd.syrchem.RegistrarManager.handle(Unknown Source)
org.webmacro.servlet.WMServlet.doRequest(WMServlet.java:274)
org.webmacro.servlet.WMServlet.doPost(WMServlet.java:232)
javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:151)
net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:129)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:225)
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:581)
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
org.apache.catalina.authenticator.SingleSignOn.invoke(SingleSignOn.java:336)
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1001)
org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:585)
org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
java.lang.Thread.run(Unknown Source)
We are using the built in connection pool from Tomcat 7.0.23.
ChemAxon aa7c50abf8
08-11-2012 17:59:30
Thanks.
Which JChem version is this? (I thought you were using 5.10.3, but the stack trace doesn't match that version.)
ChemAxon aa7c50abf8
08-11-2012 18:26:43
I can reproduce the problem with CLOB cd_structure column. We will fix it ASAP.
ChemAxon aa7c50abf8
14-01-2013 22:50:36
We fixed the problem for CLOB structure columns as well. The fix has been released in version 5.11.5.
Please, let us know if we still missed something.
Peter