OR query with jc_compare

Hello

I am trying to reproduce an OR query as seen in the Developer Trainign Manual from this year's UGM (Section 5 - Database Programming, 3.3)

SELECT * 
FROM MY_TABLE 
WHERE jc_compare(COMPOUND_SMILES,'C1CCCC1 C1CCCCC1','t:d') = 1 

However, this only returns hits with the first Smiles, not the combination of bothe sets

I am trying to avoid, using the SQL below whcih is really slow:

SELECT * 
FROM MY_TABLE 
WHERE jc_compare(COMPOUND_SMILES,'C1CCCC1','t:d') = 1 
OR jc_compare(COMPOUND_SMILES,'C1CCCCC1','t:d') = 1 

I have tried it on several different version of JChem cartrdge without any luck. Is this a bug or a mistake in the Training manual ?

Thanks

Catherine

Hi Catherine,

The query structures need to be separated with a new line character: chr(10)

Let me know, if this doesn't help.

Peter

This works

Thanks a lot

Catherine

Another part of the same code Catherine has been working on does a substructure search.  The code uses EclipseLink JPA to create queries which are not working for some reason:

Core query:

SELECT *FROM MY_TABLE WHERE (jc_compare(SMILES,'C1CCCC1
C1CCCCC1','t:s') = 1);

which works fine

Now if you put some constraints on the code to limit the number of rows returned, e.g.

SELECT * FROM
  (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum FROM
    (SELECT *
    FROM MY_TABLE
    WHERE (jc_compare(SMILES,'C1CCCC1
C1CCCCC1','t:s') = 1)
    ) a
  WHERE ROWNUM <= 30
  )
WHERE rnum > 0

Gives the error:

SQL Error: ORA-29904: error in executing ODCIIndexClose() routine
ORA-29532: Java call terminated by uncaught Java exception: java.rmi.ServerException: RemoteException occurred in server thread; nested exception is:
    java.rmi.RemoteException
ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 224
ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 604
29904. 00000 -  "error in executing ODCIIndexClose() routine"
*Cause:    The execution of ODCIIndexClose routine caused an error.
*Action:   Examine the error messages produced by the indextype code and
           take appropriate action.

 

Any ideas? This is in both 5.2.3.2 and 5.2.5.1(Just upgraded just in case!)

thanks

Ian

 

Additional info: dump from log on the server

SEVERE: null
java.lang.NullPointerException
        at chemaxon.jchem.interop.core.search.MultiQueryHandler.stop(MultiQueryHandler.java:54)
        at chemaxon.jchem.cartridge.rmi.impl.JchemSearchResultImpl.dispose(JchemSearchResultImpl.java:58)
        at chemaxon.jchem.cartridge.rmi.impl.JchemSearchImpl.disposeSearch(JchemSearchImpl.java:120)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:305)
        at sun.rmi.transport.Transport$1.run(Transport.java:159)
        at java.security.AccessController.doPrivileged(Native Method)
        at sun.rmi.transport.Transport.serviceCall(Transport.java:155)
        at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:535)
        at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:790)
        at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:649)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:619)

Hi Ian,

This is a bug in JChem Cartridge. It will be fixed in the next JChem version. (Planned release date: 10/16/2009)

Thanks for reporting this problem and sorry for the inconvenience.

Peter

I have just re-tested this in the latest version and it works fine

Thanks for fixing this issue so quickly

Catherine