Technical Support Forum Index
Technical Support Forum
Access ChemAxon scientists and developers here. For confidential or other support please email.		 The time now is Sat Jul 04, 2009 11:48 pm
 <b>Register</b> (required to post and download)Register (required to post and download)
 Username:    Password:   Remember login       
 FAQFAQ   SearchSearch   DigestsDigests 
Data consistency issue in oracle with Jchem Cartridge
To watch this topic for replies  Register (enables digests) or give email address:
Reply to topic    Home -> Forums -> Support -> Storage & search: JChem Base /Cartridge -> Data consistency issue in oracle with Jchem Cartridge

Display posts from previous:        View previous topic :: View next topic    
Author Message
rhalder
Joined: 30 Nov 2007
Posts: 44


View user's profile
Back to top
Post subject: Data consistency issue in oracle with Jchem Cartridge
Link to postPosted: Fri Mar 07, 2008 12:24 am  Reply with quote
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
pkovacs
Joined: 23 Jul 2004
Posts: 1038
ChemAxon personnel

View user's profile

Visit poster's website
Back to top
Post subject:
Link to postPosted: Mon Mar 10, 2008 2:24 pm  Reply with quote
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
rhalder
Joined: 30 Nov 2007
Posts: 44


View user's profile
Back to top
Post subject:
Link to postPosted: Mon Mar 10, 2008 3:28 pm  Reply with quote
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
pkovacs
Joined: 23 Jul 2004
Posts: 1038
ChemAxon personnel

View user's profile

Visit poster's website
Back to top
Post subject:
Link to postPosted: Tue Mar 11, 2008 6:40 pm  Reply with quote
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
pkovacs
Joined: 23 Jul 2004
Posts: 1038
ChemAxon personnel

View user's profile

Visit poster's website
Back to top
Post subject:
Link to postPosted: Mon Aug 11, 2008 4:45 pm  Reply with quote
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
rhalder
Joined: 30 Nov 2007
Posts: 44


View user's profile
Back to top
Post subject:
Link to postPosted: Wed Aug 13, 2008 9:33 pm  Reply with quote
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.
Display posts from previous:   
Reply to topic    Home -> Forums -> Storage & search: JChem Base /Cartridge -> Data consistency issue in oracle with Jchem Cartridge All times are GMT + 1 Hour
Page 1 of 1
To watch this topic for replies   Register (enables digests) or give email address  
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You can download files in this forum