updates to regular oracle table with molecule idx no dups

I have a regular oracle table with a smiles column and a few other columns.

CREATE TABLE STRUCTURE
(
   ID decimal(19) PRIMARY KEY NOT NULL,
   VERSION decimal(19),  
   SMILES varchar2(4000) NOT NULL,
   someVal varchar2(50)
);

I've placed an index on the smiles column:

CREATE INDEX idx_structure_smile
ON structure(smiles)
INDEXTYPE IS jchem.jc_idxtype
PARAMETERS('duplicateFiltering=y,absoluteStereo=y,haltOnError=y,tableType=molecules')

if I insert  a smiles

INSERT INTO structure (SMILES) VALUES ('c1ccccc1');

then try to update the smiles to the same values as it currently has

update structure
set smiles = 'c1ccccc1'
where smiles = 'c1ccccc1'

I see :

Error: ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine
ORA-29532: Java call terminated by uncaught Java exception: java.lang.Exception: Structure is a duplicate of structure with ROWID AAqLqzAApAACbCdAAA
ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 253
ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 653

SQLState:  99999
ErrorCode: 29877

Some context, the table in question is mapped by hibernate in the application so the inserts and updates are managed via hibernate.  In the application, a structure.smiles won't frequently be modified but the other properties on the table can be modified.  When any property changes hibernate updates all the columns with the new values.  This is where I'm hitting this type or thing.

Is it possible to use the duplicateFiltering=y, when hibernate can issue an update statement setting the smiles?

It seems like performing this update, results in a new record in the jchem tables that back the domain index resulting in this duplicate error.

Oracle environment:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bi
PL/SQL Release 10.1.0.5.0 - Production
CORE    10.1.0.5.0    Production
TNS for Linux: Version 10.1.0.5.0 - Production
NLSRTL Version 10.1.0.5.0 - Production

JChem Server environment:
Java VM vendor: Sun Microsystems Inc.
Java version: 1.6.0_12
Java VM version: 11.2-b01
JChem version: 5.2.5.1
JChem Index version: 5020400
JDBC driver version: 11.1.0.7.0-Production

thanks for any comments,

Dan

Dan,

This looks very much like a JChem Cartridge bug. I am going to investigate it and get back to you as soon as I have something definitive.

Thanks

Peter

Thanks Peter.

Dan,

This is a bug in JChem Cartridge and will be fixed in JChem 5.3.1. There is no known easy workaround.

Thank you for reporting this problem and apologies for the inconvenience.

Peter

Great, thanks for the update!

When are you looking to release a 5.3.1 ?

5.3.1 release is planned for Wednesday, March 24, 2010.

5.3.1 release is planned for Wednesday, March 24, 2010.

 

 

JChem 5.3.2 has been released with the fix.

Peter