Data consistency issue in oracle with Jchem Cartridge

Hi,





We are using Jchem Cartrige 3.1.5 and oracle 10g. We have encountered a problem with data consistency issue.





Example


Session 1:


Insert into molecule(smiles) values ('c1ccncc1');





select * from molecules


where jc_equals(smiles,'c1ccncc1') = 1;





No rows selected





If I now commit then, the above select returns row. We would expect data to be seen with in the same session before commiting the data. This may be because of the fact that seperate oracle session is being used to perform the jchem index search. This is causing an issue in our plsql code where we donot want to commit a logical unit of work ie DML statements.





Many Thanks


Rajeev

Hi Rajeev,





Your assessment of the problem is correct.





Is this a problem for you only with perfect search or you would need to solve this problem for other types of structure searches as well? I am asking this because we think we can provide a solution for perfect search with relatively little cost, whereas the options for solving the same transaction consistency problem for, say, substructure search is significantly more involved.





Thanks


Peter

At the moment the problem is for exact search.


For example - We are loading series of molecules into a table and we want to check if the molecule exist before inserting into the table and for each molecule we insert we need to get the id and use it as a foreign to other tables. At the moment even if we have inserted the molecule ,the check returns false becuase of this problem and when we try to add it again system return unique constraint violation. If you want more details then we could send you our data model and the progress in seperate email.





It would be nice to fix the problem for other searches too. Does this problem exist in the new Jchem version?





Many Thanks


Catherine and Rajeev

The basic architecture of JChem Cartridge has not changed in the new JChem versions: for structure search, JChem Server uses a database connection distinct from that initiating the structure search -- so changes need to be committed before they are included in structure search. This is a limitation which has been persisting through current versions of JChem Cartridge.





Since solving this problem for perfect search appears to be relatively simple we will try to provide a solution initially only for perfect search.





Thanks


Peter

JChem 5.1 has been released including the requireCommit option for perfect search: http://www.chemaxon.com/jchem/doc/guide/cartridge/cartapi.html#jc_compare_requireCommit. The following query will include structures inserted, but not yet committed in the current session:

Code:

select id from structures where jc_compare(struct, 'Brc1ccccc1', 't:p requireCommit:n') = 1

Note that version 5.1 also has a related new feature, the duplicateFiltering index parameter: http://www.chemaxon.com/jchem/doc/guide/cartridge/index.html#duplicate_filtering. The duplicate filtering associated with this parameter will also include not-yet-committed inserts in the current database session. Please, see also http://www.chemaxon.com/jchem/doc/guide/cartridge/index.html#tautomer_duplicate_filtering and http://www.chemaxon.com/jchem/doc/guide/cartridge/index.html#exlusive_duplicate_filtering





Thanks


Peter

We have tested with new JCHEM version ie 5.1 and I can confirm that this problem does not exist anymore.





Thanks for your help and the release.