Tversky similarity search in IJC 5.4

Hi Tim,

Similarity search using Tversky coefficient seems to be broken in IJC 5.4.1.1 when using oracle with jchem cartridge tables I get the error:

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT DISTINCT STARLITE.COMPOUNDS."MOLREGNO" FROM STARLITE.COMPOUNDS WHERE jc_compare(STARLITE.COMPOUNDS."STRUCTURE", ?, ?) = 1]; SQL state [99999]; error code [29902]; ORA-29902: error in executing ODCIIndexStart() routine

ORA-20101: For input string: "0.5;0.5"

ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 31

ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 474

; nested exception is java.sql.SQLException: ORA-29902: error in executing ODCIIndexStart() routine

ORA-20101: For input string: "0.5;0.5"

ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 31

ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 474



	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)

	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)

	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)

	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)

	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636)

	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:661)

	at com.im.ijcs.impl.query.Executor.executeQuery(Executor.java:275)

	at com.im.ijcs.impl.query.Executor.visitSqlQuery(Executor.java:241)

	at com.im.ijcs.impl.query.Executor.execute(Executor.java:162)

	at com.im.df.server.local.LocalQueryExecutor.access$001(LocalQueryExecutor.java:26)

	at com.im.df.server.local.LocalQueryExecutor$1.doInTransactionWithoutResult(LocalQueryExecutor.java:60)

	at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:33)

	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)

	at com.im.df.server.local.LocalQueryExecutor.execute(LocalQueryExecutor.java:58)

	at com.im.ijcs.impl.query.DefaultQueryService.findImpl(DefaultQueryService.java:130)

	at com.im.ijcs.impl.query.DefaultQueryService$FindExecutor.call(DefaultQueryService.java:109)

	at com.im.ijcs.impl.query.DefaultQueryService$FindExecutor.call(DefaultQueryService.java:94)

	at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

	at java.util.concurrent.FutureTask.run(FutureTask.java:138)

	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:680)

Caused by: java.sql.SQLException: ORA-29902: error in executing ODCIIndexStart() routine

ORA-20101: For input string: "0.5;0.5"

ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 31

ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 474



	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)

	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)

	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)

	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)

	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)

	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)

	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)

	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)

	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)

	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)

	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)

	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1203)

	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)

	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)

	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)

	at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:643)

	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)

	... 18 more

however if I try the same search from SQLPlus

SELECT STRUCTURE,MOLREGNO FROM COMPOUNDS WHERE JC_TVERSKY(TO_CHAR(STRUCTURE),'O=C1NC(=O)N(N=C1)C1=CC=CC=C1',0.5,0.5) >= 0.8;

I don't get any errors and the search returns OK. It appears for some reason IJC tries to call jc_compare cartridge function instead of jc_tversky?

Oleg.

Hi Oleg,

This seems to be working OK for me with IJC 5.4.1.1.

The jc_compare() operator is correct. It takes parameters that define the type of search as the third parameter. An example is:

sep=, earlyResults:0,t:i,simThreshold:0.7,dissimilarityMetric:TVERSKY,dissimilarityMetricParameters:0.5;0.5

From the error message you report it seems it is the value of the dissimilarityMetricParameters property (0.5;0.5) that is causing the error. Its not obvious why this is happening. Are you just running a structure search or are other query criteria involved? And which version of the JChem cartridge is involved?

Tim

Hi Tim

I was trying to run a similarity search using "O=C1NC(=O)N(N=C1)C1=CC=CC=C1" as query and tversky as similarity coefficient. The cartridge version is jchem.version=5.3.0.2 which seems to be the problem, the new version uses ';' instead of ',' as parameter separator I can't run the query as you specified but I can run the following query:

select structure,molregno from compounds where jc_compare(structure,'O=C1NC(=O)N(N=C1)C1=CC=CC=C1','t:t dissimilarityThreshold:0.8 dissimilarityMetric:tversky,0.2,0.8')=1

I need to upgrade the cartridge version. Thank you for clarification.

Oleg.