multimolecules in JChem tables

Hi,

1. Can I store two molecules, e.g. CH₃Br.CH₃O^- , as a single "molecule" in a JChem table?  


2. If so, and I search for CH₃Br, will CH₃Br.CH₃O^- be returned?  


3. Will a search for CH₃Br.CH₃O^- yield the same result as a search for CH₃O^-.CH₃Br ?

-- Bob

Hi Bob,

The answer is "yes" to all - assuming you meant SUBSTRUCTURE search mode in your question.

Best regards,

Szilard

Actually, I meant DUPLICATE search mode.

Hi Bob,

1. Yes.

2. As Szilárd mentioned, substructure search will find it. Full fragment search type will also find it, but Duplicate will not.

3. That is correct. None of the search types are dependent on the order of components.

So all search types will match.

Best regards,

Szabolcs

Great, thanks.  A few more questions.  

I want to make a JChem table that includes not only a column for the molecules on which I will do a JChem search, but also additional columns rxn_id and calcd_prods.  I see how I can use the UpdateHandler to insert the extra fields into the JChem table, but I have questions about searching the table.

(1) It appears to me that if I want to search for calcd_prods on both the molecules and the rxn_id columns, I will need to do it in two stages: one query using JChemSearch to get the cd_ids of the molecules that match mine, and then a second query like so:

select calcd_prods from jchem_table where rxn_id = 230 and cd_id in (3, 25, 140);

Is that correct, or can I add the "where rxn_id = 230" phrase to my JChemSearch?

(2) And I'm pretty sure of the answer to this question, but I'll ask it anyway.  I can use standard SQL to query the table based on compound IDs that I got from a JChemSearch operation, as in (1), correct?  I don't need to use special JChem objects like ConnectionHandler  to run these SQL commands, do I?

delete from jchem_table where rxn_id = 230;

I don't need to use an UpdateHandler to do this deletion, do I?

Thanks in advance for answering my naive questions.

1)  Please see the filterQueryOption:

http://www.chemaxon.com/jchem/doc/dev/java/api/chemaxon/sss/search/JChemSearchOptions.html#setFilterQuery(java.lang.String)

2) Yes you can use standard SQL to retrieve results.

Please note that for structure display the "cd_structure" column should be used (cd_smiles is for internal use!).

There are some helper methods you may find useful, but you do not have to use them, e.g::

http://www.chemaxon.com/jchem/doc/dev/java/api/chemaxon/util/DatabaseTools.html#readString(java.sql.ResultSet, int)

3)  You do have to use he API for delete as well.

Otherwise the Structure Cache will not be updated, and molecules no longer in the database will still be part of the search and the search results too.

Best,

Szilard

The filter query option looks helpful, but it seems to me that I must still do two queries if I want to retrieve the value of calcd_prods for a given rxn_id and structure, right?  Because JChemSearch will only return cd_id values for the matching compounds, and it won't return values in related columns?

Hi Bob,

But it is only one query for the search part, executed for you inside JChemSearch.

Display is always a different matter, usually it consists of many queryes becuse you do not want to retreive all the results for a huge result set - instead fetching only what's visible during paging or scrolling.

I forgot to mention the getHitsAsMolecules() method as an alternative for displaying queries:

http://www.chemaxon.com/jchem/doc/dev/java/api/chemaxon/jchem/db/JChemSearch.html#getHitsAsMolecules(int[], chemaxon.util.HitColoringAndAlignmentOptions, java.util.ArrayList, java.util.ArrayList)

It does support extra fields, plus has other features like  coloring and alignment.

Please see the relevant section in our Developer's Guide as well:

http://www.chemaxon.com/jchem/doc/dev/search/index.html#sss_retrieve

Best,

Szilard

Thanks.  We're not actually interested in retrieving or displaying the matching structures.  We only want to retrieve (and not even to display) the values in the calcd_products field.  So I believe we will still have to do two queries, but we will use filterQuery to put the searches on both the structures and the rxn_id into a single query, as you suggest.

And now another question, about ConnectionHandler.  Does it use/implement connection pooling?

Hi,

ConnectionHandler does not implement connection pooling, each instance holds an individual Connection object.

Best,

Szilard

Oy, that's not good.  We are very strictly required to use connection pooling.

Would it be possible to make a new constructor, ConnectionHandler(Connection), which builds a ConnectionHandler based on an existing connection, which can be obtained from the connection pool?

Bob,

You can use the default constructor, and then:

http://www.chemaxon.com/jchem/doc/dev/java/api/chemaxon/util/ConnectionHandler.html#setConnection(java.sql.Connection)

You may also have to set the property table name if non-default:

http://www.chemaxon.com/jchem/doc/dev/java/api/chemaxon/util/ConnectionHandler.html#setPropertyTable(java.lang.String)

Best,

Szilard

Great!  But what's the propertyTable property?  I see I have to set the driver, url, loginname, and password *after* I set the connection -- is that what you're referring to?

Bob,

But what's the propertyTable property?

Thre is a good overview of our database technology in the developer's guide:

http://www.chemaxon.com/jchem/doc/dev/

This particular section covers the property table:

http://www.chemaxon.com/jchem/doc/dev/dbconcepts/index.html#JChemProperties

If you use the default you do not have to set it.

I see I have to set the driver, url, loginname, and password *after* I set the connection -- is that what you're referring to?

If using an external connection you do not have to set these  (except for regeneration, but we are planning to rewrite that too.)

Thanks,

Szilard

Even better!  That makes our lives a lot easier.  

It's not clear to me why you mention regeneration.  It's not handled through the Java API, is it?

But what's the propertyTable property?

I see I have to set the driver, url, loginname, and password *after* I set the connection -- is that what you're referring to?

So, based on this advice, I devised this code:

    private static ConnectionHandler getConnectionHandler(Connection con)

            throws SQLException {

        ConnectionHandler conHandler = new ConnectionHandler();

        try {

            conHandler.setConnection(con);

            conHandler.connectToDatabase();

        } catch (ClassNotFoundException e) {

            alwaysPrint("ReactorResultsRW.getConnectionHandler: could not "

                    + "connect to database.");

            throw new SQLException(e.getMessage());

        } // try

        return conHandler;

    } // getConnectionHandler(Connection)

And I get this runtime error:

Mar 3, 2011 10:48:44 PM org.apache.catalina.core.StandardWrapperValve invoke

INFO: SynthScheme.isResponseMatching: unknown exception: null

java.lang.NullPointerException

	at java.lang.Class.forName0(Native Method)

	at java.lang.Class.forName(Class.java:169)

	at chemaxon.util.ConnectionHandler.connectToDatabase(ConnectionHandler.java:321)

	at com.epoch.db.ReactorResultsRW.getConnectionHandler(ReactorResultsRW.java:259)

Is there a value that I need to set and that I am not setting?

Hi Bob,

Since your connection is set via setConnection(Connection) the driver, url, loginName and password parameters are set to null (see API doc of setConnection()). When you call connectToDatabase() it tries to create a connection using the above parameters and it results in a NullPointerException. You should simply avoid calling connectToDatabase() when you have a live connection (= connected to the database) set.

We will clarify this issue in the API doc of connectToDatabase().

Best regards,
Tamás

Ah, I see.  If I may suggest, it would also be useful to create a second example at the top of the API doc for ConnectionHandler.   And, because all one needs to do to create a ConnectionHandler from an existing connection is to use setConnection(), I suggest you make a new ConnectionHandler() constructor that takes a Connection as a parameter and is then ready to go.

Thanks for all your quick replies.

Thanks for your hints, the new example and constructor will be available from JChem 5.5.

Best regards,
Tamas

I am having trouble populating the JChem table via the API.  The code:

     public static void addCalcdProducts(String molStr, int rxnId,

            String calcProds) throws DBException {

        final String SELF = "ReactorResultsRW.addCalcdProducts: ";

        UpdateHandler uh = null;

        ConnectionHandler conHandler = null;

        try {

            conHandler = getConnectionHandler();

            final String[] existingCalcdProds =

                    getCalcdProducts(conHandler, molStr, rxnId);

            final String rxnName = getName(conHandler, rxnId);

            if (Utils.isEmpty(existingCalcdProds)) {

                final String addlCols = 

                        joinAll(REACT_RXN_ID, REACT_CALCD_PRODS).toString();

                debugPrint(SELF + "storing calculated products ",

                        calcProds, " for starting materials ", molStr,

                        " and reaction ", rxnName, " (ID ", rxnId, "); "

                        + "addlCols = ", addlCols);

                uh = new UpdateHandler(conHandler,

                        UpdateHandler.INSERT, REACTOR_RESULTS, addlCols);

                uh.setStructure(molStr);

                uh.setValueForAdditionalColumn(1, rxnId);

                uh.setValueForAdditionalColumn(2, calcProds);

                uh.execute();

            } else debugPrint(SELF + "products for starting materials ",

                    molStr, " and reaction ", rxnName, " (ID ", rxnId,

                    ") already in database: ", existingCalcdProds);

        } catch (PropertyNotSetException e) {

            alwaysPrint(SELF + "caught PropertyNotSetException.");

            e.printStackTrace();

            throw new DBException(e.getMessage());

        } catch (SQLException e) {

            alwaysPrint(SELF + "caught SQLException.");

            e.printStackTrace();

            throw new DBException(e.getMessage());

        } finally {

            try {

                if (uh != null) uh.close();

            } catch (SQLException e) { ; }

            Connection con = conHandler.getConnection();

            closeConnection(con);

        } // try

    } // addCalcdProducts(String, int, String)

The debug output:

ReactorResultsRW.addCalcdProducts: storing calculated products CCCC#[C-] for starting materials CCCC#C and reaction NaH (ID 203); addlCols = rxn_id, calcd_products

SynthScheme.isResponseMatching: unknown exception: null

java.lang.NullPointerException

	at chemaxon.jchem.db.UpdateHandlerOracle.getCLOB(UpdateHandlerOracle.java:50)

	at chemaxon.jchem.db.UpdateHandler.setValuesForPstmt(UpdateHandler.java:2011)

	at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:2342)

	at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:2240)

	at com.epoch.db.ReactorResultsRW.addCalcdProducts(ReactorResultsRW.java:58)

	at com.epoch.synthesis.SynthSolver.getProducts(SynthSolver.java:191)

SQL> describe reactor_results_v3;

 Name					   Null?    Type

 ----------------------------------------- -------- ----------------------------

 CD_ID					   NOT NULL NUMBER(10)

 CD_STRUCTURE				   NOT NULL BLOB

 CD_SMILES					    VARCHAR2(4000)

 CD_FORMULA					    VARCHAR2(100)

 CD_SORTABLE_FORMULA				    VARCHAR2(500)

 CD_MOLWEIGHT					    FLOAT(126)

 CD_HASH				   NOT NULL NUMBER(10)

 CD_FLAGS					    VARCHAR2(20)

 CD_TIMESTAMP				   NOT NULL DATE

 CD_PRE_CALCULATED			   NOT NULL NUMBER(1)

 CD_FP1 				   NOT NULL NUMBER(10)

 CD_FP2 				   NOT NULL NUMBER(10)

 CD_FP3 				   NOT NULL NUMBER(10)

 CD_FP4 				   NOT NULL NUMBER(10)

 CD_FP5 				   NOT NULL NUMBER(10)

 CD_FP6 				   NOT NULL NUMBER(10)

 CD_FP7 				   NOT NULL NUMBER(10)

 CD_FP8 				   NOT NULL NUMBER(10)

 CD_FP9 				   NOT NULL NUMBER(10)

 CD_FP10				   NOT NULL NUMBER(10)

 CD_FP11				   NOT NULL NUMBER(10)

 CD_FP12				   NOT NULL NUMBER(10)

 CD_FP13				   NOT NULL NUMBER(10)

 CD_FP14				   NOT NULL NUMBER(10)

 CD_FP15				   NOT NULL NUMBER(10)

 CD_FP16				   NOT NULL NUMBER(10)

 RXN_ID 					    NUMBER(38)

 CALCD_PRODUCTS 				    CLOB

Is the CLOB causing the problem, or something else?

BTW, the connection handler appears to be working properly now, as I can query the table (although it is currently empty).

Bob,

Could you let as know your exact JChem version please  ? 

Best,

Szilard

JChem 5.4.1 (latest version).

I worked around the problem by doing a separate update to insert the CLOB.  But it would be better to insert the CLOB at the same time as I insert the other two fields.

Bob,

I have test-driven the affected code in varipous ways, it seems to be working for us fine.

I seems this standard Oracle JDBC call returns null (should not) or throws exception for you :

import oracle.sql.CLOB;

[...]

tempClob = CLOB.createTemporary( con, true, CLOB.DURATION_SESSION );

http://download.oracle.com/docs/cd/E16338_01/appdev.112/e13995/oracle/sql/CLOB.html#createTemporary_java_sql_Connection__boolean__int_

Could you let us know

- are you using the JDBC driver for Oracle that was shipped with JChem ?

- Your Oracle version

- Can you execute the call above in you environment ?

Also here is a sample code that works fine here, does it work for you ?

(please amend the table name and connnection paramters)

 public static void addCalcdProducts(String molStr, int rxnId,


            String calcProds) throws Exception {


        ConnectionHandler ch = new ConnectionHandler();


        ch.setDriver("oracle.jdbc.driver.OracleDriver");


        ch.setUrl("jdbc:oracle:thin:@localhost:1521:o92");


        ch.setLoginName("scott");


        ch.setPassword("tiger");


        ch.connectToDatabase();


        


        UpdateHandler uh = new UpdateHandler(ch,


                UpdateHandler.INSERT, "CL", "RXN_ID,CALCD_PRODUCTS");


        uh.setStructure(molStr);


        uh.setValueForAdditionalColumn(1, rxnId);


        uh.setValueForAdditionalColumn(2, calcProds);


        uh.execute();


        


        ch.close();


    } 

Ideally a sample code for a bug report should best contain String literals when possible instead of unknown input data and unresolved functions for a better cahnce to reproduce.

You may start with this and try to get it closer and closer to what happens in your actual code.

Best regards,

Szilard 

(1) We're using ojdbc14.jar as our Oracle driver.

(2) We're using Oracle 10.2.0 express edition.

(3) I can't even compile the code when I include your test statement:

ReactorResultsRW.java:342: cannot find symbol

symbol  : variable DURATION_SESSION

location: class java.lang.String

	CLOB.createTemporary(con, true, CLOB.DURATION_SESSION);

	                                    ^

The code:

import oracle.sql.CLOB;

...

    /** For debugging JChem Oracle calls. */

    public static void main() {

        ConnectionHandler ch = new ConnectionHandler();

        Connection con = null;

        try {

            con = getPoolConnection();

            final CLOB tempClob =

                    CLOB.createTemporary(con, true, CLOB.DURATION_SESSION);

            System.out.println("ReactorResultsRW.main: created tempClob successfully.");

...

P.S.  Is there a problem with setting autoCommit to false for the Connection inside of the ConnectionHandler before using the ConnectionHandler to update the JChem table, as in the following code? 

            conHandler = getConnectionHandler();

            con = conHandler.getConnection();

                con.setAutoCommit(false);

                uh = new UpdateHandler(conHandler, UpdateHandler.INSERT,

                        REACTOR_RESULTS, REACT_RXN_ID);

                uh.setStructure(molStr);

                uh.setValueForAdditionalColumn(1, rxnId);

                final int smId = uh.execute(true);

                final StringBuilder bld = new StringBuilder();

                bld.append(UPDATE + REACTOR_RESULTS

                        + SET + REACT_CALCD_PRODS + EQUALS + QMARK // CLOB

                        + WHERE + REACT_RXN_ID + EQUALS).append(rxnId);

                bld.append(AND + REACT_SM_ID + EQUALS).append(smId);

                tryUpdate(con, bld.toString(), calcProds); // CLOB

                con.setAutoCommit(true);

Hi Bob,

You Oracle JDBC driver is too old and is not supported by JChem.

(I could reproduce the same problem with ojdbc14.jar)

Please use the JDBC driver we provide with the package for best results.

As the documentation mentions, you can use transactions, but you have to call one of the methods before you commit:

WARNING! 
For optimization reasons logs are buffered in UpdateHandler from JChem 5.4. 
When you use transactions saveUpdateLogs() must be called in order to flush buffers before you commit! 
(close() method performs a call to saveUpdateLogs(), no extra calls are needed.)

http://www.chemaxon.com/jchem/doc/dev/java/api/chemaxon/jchem/db/UpdateHandler.html

BTW you have to use one of them if not in transaction as well (usually close() after operations on 1 or more molecules) to store the update logs, and enable the structure cache to detect the changes.

Szilard

So, if I want to do an insert and then an update before committing them both:

        ConnectionHandler conHandler = null;

        Connection con = null;

        try {

            conHandler = getConHandler();

            con = conHandler.getConnection();

                con.setAutoCommit(false);

                uh = new UpdateHandler(conHandler, UpdateHandler.INSERT,

                        REACTOR_RESULTS, REACT_RXN_ID);

                uh.setStructure(molStr);

                uh.setValueForAdditionalColumn(1, rxnId);

                final int smId = uh.execute(true);

                final StringBuilder bld = new StringBuilder();

                bld.append(UPDATE + REACTOR_RESULTS

                        + SET + REACT_CALCD_PRODS + EQUALS + QMARK // CLOB

                        + WHERE + REACT_RXN_ID + EQUALS).append(rxnId);

                bld.append(AND + REACT_SM_ID + EQUALS).append(smId);

                tryUpdate(con, bld.toString(), calcProds); // CLOB

                uh.saveUpdateLogs();

                con.commit();

        } catch (PropertyNotSetException e) {

            e.printStackTrace();

            rollbackConnection(con);

            throw new DBException(e.getMessage());

        } catch (SQLException e) {

            e.printStackTrace();

            rollbackConnection(con);

            throw new DBException(e.getMessage());

        } finally {

            try {

                if (uh != null) uh.close();

            } catch (SQLException e) { ; }

            closeConnection(con);

        } // try

At a quick glance it looks OK.

I'm not aware of any problems with later Oracle JDBC drivers.

I think it must be a very mature and very widely tested codebase.

Trust us: we are trying to give you the best choice you can have  - in the hope of having to deal with less support problems :)

Best,

Szilard

We have upgraded to ojdbc5.jar, and we are still getting an exception.  The code:

    protected static final String REACTOR_RESULTS = "reactor_results_v3";

        protected static final String REACT_SM_ID = "cd_id";

        protected static final String REACT_RXN_ID = "rxn_id";

        protected static final String REACT_CALCD_PRODS = "calcd_products";

    public static void addCalcdProductsOneStep(String molStr, int rxnId,

            String calcdProds) throws DBException {

        final String SELF = "ReactorResultsRW.addCalcdProductsOneStep: ";

        UpdateHandler uh = null;

        ConnectionHandler conHandler = null;

        try {

            conHandler = getConHandler();

            final String rxnName = getName(conHandler, rxnId);

            final String addlCols =

                    joinAll(REACT_RXN_ID, REACT_CALCD_PRODS).toString();

            debugPrint(SELF + "storing calculated products ",

                    calcdProds, " for starting materials ", molStr,

                    " and reaction ", rxnName, " (ID ", rxnId, "); "

                    + "addlCols = ", addlCols);

            uh = new UpdateHandler(conHandler,

                    UpdateHandler.INSERT, REACTOR_RESULTS, addlCols);

            uh.setStructure(molStr);

            uh.setValueForAdditionalColumn(1, rxnId);

            uh.setValueForAdditionalColumn(2, calcdProds);

            uh.execute();

        } catch (PropertyNotSetException e) {

            alwaysPrint(SELF + "caught PropertyNotSetException.");

            e.printStackTrace();

            throw new DBException(e.getMessage());

        } catch (SQLException e) {

            alwaysPrint(SELF + "caught SQLException.");

            e.printStackTrace();

            throw new DBException(e.getMessage());

        } finally {

            try {

                if (uh != null) uh.close();

            } catch (SQLException e) { ; }

            closeConHandler(conHandler);

        } // try

    } // addCalcdProductsOneStep(String, int, String)

The log output:

ReactorResultsRW.addCalcdProductsOneStep: storing calculated products  for starting materials c1ccccc1 and reaction H2O, cat. weak acid (ID 51); addlCols = rxn_id, calcd_products

SynthScheme.isResponseMatching: unknown exception: null

java.lang.NullPointerException

	at chemaxon.jchem.db.UpdateHandlerOracle.getCLOB(UpdateHandlerOracle.java:50)

	at chemaxon.jchem.db.UpdateHandler.setValuesForPstmt(UpdateHandler.java:2011)

	at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:2342)

	at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:2240)

	at com.epoch.db.ReactorResultsRW.addCalcdProductsOneStep(ReactorResultsRW.java:77)

We are using JChem 5.4.1.1.  Here's the structure of our JChem database table:

 Name					   Null?    Type

 ----------------------------------------- -------- ----------------------------

 CD_ID					   NOT NULL NUMBER(10)

 CD_STRUCTURE				   NOT NULL BLOB

 CD_SMILES					    VARCHAR2(4000)

 CD_FORMULA					    VARCHAR2(100)

 CD_SORTABLE_FORMULA				    VARCHAR2(500)

 CD_MOLWEIGHT					    FLOAT(126)

 CD_HASH				   NOT NULL NUMBER(10)

 CD_FLAGS					    VARCHAR2(20)

 CD_TIMESTAMP				   NOT NULL DATE

 CD_PRE_CALCULATED			   NOT NULL NUMBER(1)

 CD_FP1 				   NOT NULL NUMBER(10)

 CD_FP2 				   NOT NULL NUMBER(10)

 CD_FP3 				   NOT NULL NUMBER(10)

 CD_FP4 				   NOT NULL NUMBER(10)

 CD_FP5 				   NOT NULL NUMBER(10)

 CD_FP6 				   NOT NULL NUMBER(10)

 CD_FP7 				   NOT NULL NUMBER(10)

 CD_FP8 				   NOT NULL NUMBER(10)

 CD_FP9 				   NOT NULL NUMBER(10)

 CD_FP10				   NOT NULL NUMBER(10)

 CD_FP11				   NOT NULL NUMBER(10)

 CD_FP12				   NOT NULL NUMBER(10)

 CD_FP13				   NOT NULL NUMBER(10)

 CD_FP14				   NOT NULL NUMBER(10)

 CD_FP15				   NOT NULL NUMBER(10)

 CD_FP16				   NOT NULL NUMBER(10)

 RXN_ID 					    NUMBER(38)

 CALCD_PRODUCTS 				    CLOB

Any ideas as to what is going wrong?

-- Bob

Hi Bob,

For the first glance it seems the following call fails in the code or returns null:

import oracle.sql.CLOB;


CLOB tempClob = null;


tempClob = CLOB.createTemporary( con, true, CLOB.DURATION_SESSION );

Unfortunately the original exception is not displayed (we need to fix exception handling for this part), but maybe you could try to execute this simple call and see if there is an exception (please paste the trace) or it returns null ?

It seems this call only depens on the connection and the JDBC driver - not on input data.

Please paste here the JDBC connection URL too - just in case.

Best,

Szilard 

Because of the way we handle connections to Oracle, it took a little bit of working around, but here's what I did:

    public static void testOracle() throws SQLException, NamingException {

        Connection con = null;

        try {

            con = getPoolConnection();

            System.out.println("ReactorResultsRW.testOracle: "
                    + "creating tempClob.");

            final CLOB tempClob =

                    CLOB.createTemporary(con, true, CLOB.DURATION_SESSION);

            System.out.println("ReactorResultsRW.testOracle: "
                    + "created tempClob successfully.");

        } finally {

            closeConnection(con);

        }

    } // testOracle()

    protected static Connection getPoolConnection() throws SQLException {

        try {

            final Context initContext = new InitialContext();

            final Context envContext =

                    (Context) initContext.lookup("java:/comp/env");

            final DataSource ds =

                    (DataSource) envContext.lookup("jdbc/acepool");

            final Connection connxn = ds.getConnection();

            connxn.setAutoCommit(true);

            return connxn;

        } catch (NamingException e) {

            alwaysPrint("DBTables.getPoolConnection: naming exception.");

            return null;

        }

    } // getPoolConnection()

Then I call testOracle() from a JSP page:

<%@ page language="java" %>

<%@ page import="

    com.epoch.chem.ChemUtils,

    com.epoch.db.ReactorResultsRW,

    chemaxon.struc.PeriodicSystem"

%>

<%

    String result = "";

    try {

        ReactorResultsRW.testOracle();

    } catch (Exception e) {

        result = e.getMessage();

        e.printStackTrace();

    }

%>

And here's the result:

Aug 17, 2011 7:59:10 PM org.apache.catalina.core.StandardWrapperValve invoke

INFO: ReactorResultsRW.testOracle: creating tempClob.
java.lang.ClassCastException: org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper cannot be cast to oracle.jdbc.OracleConnection

	at oracle.sql.CLOB.createTemporary(CLOB.java:676)

	at oracle.sql.CLOB.createTemporary(CLOB.java:640)

	at com.epoch.db.ReactorResultsRW.testOracle(ReactorResultsRW.java:391)

As for the "JDBC connection URL," are you referring to the following lines from context.xml?

    <Resource name="jdbc/acepool" auth="Container" type="javax.sql.DataSource"

        driverClassName="oracle.jdbc.OracleDriver"

        url="jdbc:oracle:thin:@localhost:1521:xe"

The cause of the problem seems to be the following:

oracle.sql.CLOB.createTemporary(..) expects specifically an oracle.jdbc.OracleConnection - even though it's parameter is the more generic java.sql.Connection.

As you are passing a connection from an Apache connection pool, which is basically a wrapper around the real connection, thus a different type of object, and the mentioned method fails trying to use it.

One solution can be to get the real Oracle connection out of the wrapper and pass it to Updatehandler instead.

Here is a good thread on this problem:

https://forums.oracle.com/forums/thread.jspa?threadID=279238

We will probably try to do something internally on the long run - until then I recommend one of the workarounds metioned in the referenced discussion.

Please let us know if this helps.

Best,

Szilard

Still no joy.  The code:

        public static void addCalcdProducts(String molStr, int rxnId,

            String calcdProds) throws DBException {

        final String SELF = "ReactorResultsRW.addCalcdProducts: ";

        Connection con = null;

        UpdateHandler uh = null;

        try {

            con = getPoolConnection();

            final ConnectionHandler conHandler = getConHandler(con);

            if (conHandler == null) {

                debugPrint(SELF + "can't get innermost Oracle connection; "

                        + "need to add data to table in two steps.");

                addCalcdProductsTwoSteps(molStr, rxnId, calcdProds);

            } else {

                final String rxnName = getName(con, rxnId);

                final String addlCols =

                        joinAll(REACT_RXN_ID, REACT_CALCD_PRODS).toString();

                debugPrint(SELF + "storing calculated products ",

                        calcdProds, " for starting materials ", molStr,

                        " and reaction ", rxnName, " (ID ", rxnId, "); "

                        + "addlCols = ", addlCols);

                uh = new UpdateHandler(conHandler, UpdateHandler.INSERT,

                        REACTOR_RESULTS, addlCols);

                uh.setStructure(molStr);

                uh.setValueForAdditionalColumn(1, rxnId);

                uh.setValueForAdditionalColumn(2, calcdProds);

                uh.execute();

            } // if we got a good connection handler

        } catch (PropertyNotSetException e) {

            alwaysPrint(SELF + "caught PropertyNotSetException.");

            e.printStackTrace();

            throw new DBException(e.getMessage());

        } catch (SQLException e) {

            alwaysPrint(SELF + "caught SQLException.");

            e.printStackTrace();

            throw new DBException(e.getMessage());

        } finally {

            try {

                if (uh != null) uh.close();

            } catch (SQLException e) { ; }

            closeConnection(con);

        } // try

    } // addCalcdProducts(String, int, String)



    private static ConnectionHandler getConHandler(Connection con)

            throws SQLException {

        final String SELF = "ReactorResultsRW.getConHandler: ";

        ConnectionHandler conHandler = null;

        if (con instanceof DelegatingConnection) {

            // This returns a PoolableConnection

            final Connection pc = ((DelegatingConnection) con).getDelegate();

            if (pc != null) {

                // The PoolableConnection is a DelegatingConnection itself - 

                // get the delegate (the Oracle connection)

                final Connection dc = ((DelegatingConnection) pc).getDelegate();

                if (dc != null) conHandler = new ConnectionHandler(dc);

                else debugPrint(SELF + "innermost Oracle connection is null; "

                        + "returning null.");

            } else debugPrint(SELF + "poolable connection is null; "

                    + "returning null.");

        } else conHandler = new ConnectionHandler(con);

        return conHandler;

        /*

        return new ConnectionHandler(con instanceof DelegatingConnection

                ? ((DelegatingConnection) con).getInnermostDelegate()

                : con);

        */

    } // getConHandler(Connection)



    protected static Connection getPoolConnection() throws SQLException {

        try {

            final Context initContext = new InitialContext();

            final Context envContext =

                    (Context) initContext.lookup("java:/comp/env");

            final DataSource ds =

                    (DataSource) envContext.lookup("jdbc/acepool");

            final Connection connxn = ds.getConnection();

            connxn.setAutoCommit(true);

            return connxn;

        } catch (NamingException e) {

            alwaysPrint("DBTables.getPoolConnection: naming exception.");

            return null;

        }

    } // getPoolConnection()

The log output:

ReactorResultsRW.getConHandler: poolable connection is null; returning null.

ReactorResultsRW.addCalcdProductsOneStep: can't get innermost Oracle connection; need to add data to table in two steps.

As you can see, we also tried to use getInnermostDelegate(), but the result was the same.

context.xml reads:

    <Resource name="jdbc/acepool" auth="Container" type="javax.sql.DataSource"

        ... 

        connectionProperties="SetBigStringTryClob=true"

        accessToUnderlyingConnectionAllowed="true"

        ...

    />

We are using the ojdbc5.jar that you packaged with JChem 5.6.  Any ideas?

Well, it started working properly today.  Not sure why; maybe we needed to do a full shutdown and restart.  Thanks for your help.