ChemAxon Forum Archive » Storage & search: JChem Base and JChem Oracle Cartridge

accessing inchi functionality

ChemAxon 60ee1f1328

22-02-2007 16:20:38

select jcf_molconvert ('CCC','inchi') from dual





has caused:





ORA-29532: Java call terminated by uncaught Java exception: java.lang.Exception: The following exception has been thrown by the servlet:


Exception: InChi library is not loaded.


ORA-06512: at "CSPACE.JCHEM_CORE_PKG", line 233


ORA-06512: at "CSPACE.JCF_MOLCONVERT", line 7





How can I load this library (presumably it is libinchi.jnilib) - should it be available in 3.2.1?





Cheers,


Daniel.

ChemAxon aa7c50abf8

22-02-2007 17:21:02

Quote:
How can I load this library (presumably it is libinchi.jnilib) - should it be available in 3.2.1?
Yes, inchi support is already available in JChem 3.2.1. Please, see step 3. of JChem Server installation in the JChem Cartridge installation guide (http://www.chemaxon.com/jchem/doc/admin/cartridge.html#server_install): "Copy the jar files from the lib directory of JChem to the shared/lib directory of Tomcat. If you plan to use INCHI format, copy the inchi shared library for your platform (e.g. inchi.dll for Windows or libinchi.so for Linux) along with the jar files to the same directory."


(libinchi.jnilib is for Mac.)





Restart Tomcat after you're done with the copying.





Cheers,


Peter

ChemAxon aa7c50abf8

22-02-2007 19:31:51

Note that inchi is not running on all platforms where Java is supported (http://www.chemaxon.com/marvin/doc/user/inchi-doc.html): "At the moment Linux, Windows and Mac platforms are supported, but we will also add Sun and possibly HP Unix."





Peter

ChemAxon 60ee1f1328

23-02-2007 14:27:11

Hi Peter,





Thanks it works fine for us now.





I just wanted to ask about the inchi strings that Marvin produces.





Just wondered if the StereoChemical layer is supported?





I tried generating the inchi for a chiral centre and do not find the expected "/t" - is this as expected?





Cheers,


Daniel.

ChemAxon aa7c50abf8

23-02-2007 15:18:05

Hi Daniel,





We use IUPAC's own code which handles chiral centers.





Would it be possible for you to share with us the original structure so we can make sure that no eventual syntax failures slipped in such as marking stereo info on a symmetrical structure?





Cheers,


Peter

ChemAxon 60ee1f1328

23-02-2007 15:26:38

Hi Peter,





It was just a quicko test structure: sdf and inchi generated by marvin are attached.





Bascially we would like to generate and inchi string which is trimmed " just after the stereochemical layer is defined. Can this be done using jcf_molconvert / string manipulation of the return result - yes if "/t" is present which does not seem to be the case in the example attached/below?





InChI=1/C7H14O/c1-7(8)5-3-2-4-6-7/h8H,2-6H2,1H3 AuxInfo=1/0/N:6,4,3,5,2,1,7,8/E:(3,4)(5,6)/rA:8nCCCCCCCO/rB:;s2;s3;s1s4;;s1s2p6;N7;/rC:1.9375,-.3625,0;1.223,-1.6,0;1.9375,-2.0125,0;2.652,-1.6,0;2.652,-.775,0;.5086,-.3625,0;1.223,-.775,0;1.223,.05,0;





Thanks for all your great help,


Daniel.

ChemAxon a3d59b832c

23-02-2007 16:06:30

Hi Daniel,





I recommend to have a look at the InChi export options in the documentation:


http://www.chemaxon.com/marvin/doc/user/inchi-doc.html





Probably you are looking for something like this:?
Quote:
SNon Exclude stereo (Deafult: Include Absolute stereo)
For questions specific to the Inchi format itself I recommend to contact the IUPAC guys directly.





Best regards,


Szabolcs

ChemAxon 60ee1f1328

23-02-2007 16:10:18

Can I specify these options somehow using jcf_molconvert?





If not is there any other operator/function which I can use to pass a smiles with these options and return an inchi?





If not is can show me how this might be implemented in straight java and I can create a java stored procedure equivalent.





Cheers,


Daniel.

ChemAxon a3d59b832c

23-02-2007 17:00:58

inhibox wrote:
Can I specify these options somehow using jcf_molconvert?
Yes, see the link above:
Quote:
Export options can be specified in the format string. The format descriptor and the options are separated by a colon, the options by commas.
That is, use 'inchi:SNon' instead of 'inchi'.

ChemAxon 60ee1f1328

07-03-2007 10:26:39

Hello,





We are just trying to generate inchi from SMILES as you might have gathered from above:





We find that in some cases we cannot complete the conversion on chemaxon generated SMILES strings - is this well known or unexpected behaviour? Here is the syntax I used, so we tried a dearomatized and aromatized version of the SMILES:





Code:






select cd_id,cd_smiles,arosmiles,jcf_molconvert(cd_smiles,'inchi:SUCF:SRel:SRac:SUU')


from moleculesv3b


where cd_id < 40000





select cd_id,cd_smiles,arosmiles,jcf_molconvert(arosmiles,'inchi:SUCF:SRel:SRac:SUU')


from moleculesv3b


where cd_id < 40000






The error recieved:





Exception: Could not export to InChi format.


ORA-06512: at "CSPACE.JCHEM_CORE_PKG", line 233


ORA-06512: at "CSPACE.JCF_MOLCONVERT", line 7





We would like to obtain inchi for the whole table but find cannot do this in bulk.





Running this slowly "one at a time" syntax also ends up hanging if it finds a record it cannot convert even though we wrap a catch and say continue...:





Code:






DECLARE


CURSOR GetDataSet IS


select cd_id,cd_smiles from moleculesv3b where inchi is null order by cd_id desc;


counter number(6);


BEGIN


counter :=0;


FOR recs IN GetDataSet


LOOP


EXIT WHEN GetDataSet%NOTFOUND;


BEGIN


update moleculesv3b


set inchi = rtrim(rtrim(substr (ltrim(jcf_molconvert(recs.cd_smiles,'inchi:SUCF:SRel:SRac:SUU'),'InChi='),0,instr(jcf_molconvert(recs.cd_smiles,'inchi:SUCF:SRel:SRac:SUU'),'AuxInfo='))),'AuxInfo=')


where cd_id = recs.cd_id;


EXCEPTION WHEN OTHERS THEN NULL;


END;


counter := counter + 1;


if counter > 10000 then


commit;


counter:=0;


end if;


END LOOP;


COMMIT;


CLOSE GetDataSet;


EXCEPTION WHEN OTHERS THEN RAISE;


END;


/






Any comments you can add would be gratefully received.





Cheers,


Daniel.

ChemAxon aa7c50abf8

07-03-2007 11:29:06

Hi Daniel,





Is it possible to identify which structure causes this problem?





Peter

ChemAxon 60ee1f1328

07-03-2007 12:09:16

Hi Peter,





I have looked in cdump - no SMILES.





I have looked in CATALINA_HOME/logs - no smiles.





So it is difficult to isolate the exact problem.





I would be quite happy to skip a problem if it arises as in one at a time example, but it appears that a hang occurs - could you comment on why that might be? Could you advise on a better way to continue rather than





Code:



EXCEPTION WHEN OTHERS THEN NULL;






NULL -> CONTINUE? That would be great.





I will try to find out where the problem occurs in the meantime but there might be more examples and I ideally would get this working and dealing with excpetions.





Cheers,


Daniel.

ChemAxon 60ee1f1328

07-03-2007 14:23:29

both queries worked this time round...

ChemAxon aa7c50abf8

07-03-2007 15:07:58

Daniel,





Do I understand correctly that you have two distinct problems: in one instance you get an error message and in the other your anonymous procedure never returns. Is this correct?





Thanks


Peter

ChemAxon 60ee1f1328

12-03-2007 11:00:04

Running this select





select jcf_molconvert(arosmiles,'inchi:SUCF:SRel:SRac:SUU')


from chd_dc1_feb07new





gives this error:





ORA-29532: Java call terminated by uncaught Java exception: java.lang.Exception: The following exception has been thrown by the servlet:


Exception: Error in temporary files: The system cannot find the path specified


ORA-06512: at "CSPACE.JCHEM_CORE_PKG", line 233


ORA-06512: at "CSPACE.JCF_MOLCONVERT", line 7





Please tell me how I can get round this.


db.

ChemAxon 60ee1f1328

12-03-2007 11:00:29

the relevant column does have a jc index on it...

ChemAxon 60ee1f1328

12-03-2007 11:05:44

java.lang.Exception: The following exception has been thrown by the servlet:


Exception: Error in temporary files: The system cannot find the path specified at chemaxon.jchem.cartridge.jcservcli.JcsClientBase.checkForException(JcsClientBase.java:174) at chemaxon.jchem.cartridge.jcservcli.BlockingClient.callService(BlockingClient.java:191) at chemaxon.jchem.cartridge.jcservcli.JcsClientBase.call(JcsClientBase.java:54) at chemaxon.jchem.cartridge.JFunctions.sendAndRecBytes(JFunctions.java:1353) at chemaxon.jchem.cartridge.JFunctions.molconvertToBytes(JFunctions.java:1831) at chemaxon.jchem.cartridge.JFunctions.molconvert(JFunctions.java:1750) *** 2007-03-12 10:47:58.532

ChemAxon 60ee1f1328

12-03-2007 14:16:39

I think this could refer to the CATALINA_HOME/temp folder?





Is there a java read/write privs that should be applied to this folder.





I could do with a fix for this asap please.





Thanks,





Daniel.

ChemAxon aa7c50abf8

12-03-2007 17:06:44

Hi Daniel,
Quote:
Running this select





select jcf_molconvert(arosmiles,'inchi:SUCF:SRel:SRac:SUU')


from chd_dc1_feb07new





gives this error:





ORA-29532: Java call terminated by uncaught Java exception: java.lang.Exception: The following exception has been thrown by the servlet:


Exception: Error in temporary files: The system cannot find the path specified


ORA-06512: at "CSPACE.JCHEM_CORE_PKG", line 233


ORA-06512: at "CSPACE.JCF_MOLCONVERT", line 7
Please, could you paste the Java stack trace from the Tomcat error logs containing the string "Error in temporary files:"?





Thanks


Peter

ChemAxon 60ee1f1328

13-03-2007 10:11:29

Hi Peter,





Trace file attached and further tomcat output.





No reference to a particular folder I think?





Daniel.

ChemAxon 60ee1f1328

13-03-2007 10:30:53

Hey Peter,





You can stand down - the missing folder is CATALINA_HOME/temp.





You can lynch me in June - should I make it to Budapest!





Cheers,


Daniel.

ChemAxon 60ee1f1328

14-03-2007 17:18:34

Hi Peter,





If this select runs OK





select jcf_molconvert(cleansmiles,'inchi:SUCF:SRel:SRac:SUU') from chd_dc2_feb07temp;





You would expect that this update might complete + run in a similar time?





update chd_dc2_feb07temp set inchi = jcf_molconvert (cleansmiles,'inchi:SUCF:SRel:SRac:SUU');





Strangely we find this update don't complete?! Perhaps I am using the jcf_xxx in the wrong context?





Daniel.

ChemAxon aa7c50abf8

15-03-2007 08:39:42

Hi Daniel,





Yes, if the first statement completes, the second should also complete. I expect the second statement to take longer than the first one -- somewhat longer or significantly longer depending on what kind of indexes there are on the column chd_dc2_feb07temp.inchi. If this column has a jc_idxtype index on it, the second statement will take significantly longer to complete than the first one.





Another possible explanation for the second statement not completing is that the table or one of the rows to be updated are locked in another database session (by someone else or in another TOAD window).





A third possibility is that you encountered this problem: http://www.chemaxon.com/forum/ftopic941.html&highlight=windows+2000 . It may occur during operations involving a large number of round-trips between Oracle and Tomcat when Oracle is version 9i and is running on Windows. The only thing which makes me somewhat uncertain about this probability is that this particular problem should affect both statements (so the first one should also hang, if the second hangs).





Cheers


Peter

ChemAxon 60ee1f1328

29-04-2007 14:59:07

This SQL takes ~30 minutes to complete for about 330000 rows which is fine...





SELECT jcf_molconvert(cleansmiles,'inchi:SUCF:SRel:SRac:SUU') as inchi,supplier_no


FROM LIF_TNG_APR07QUERY;





However the CREATE TABLE AS SELECT equivalent takes significantly longer (I've yet to see it complete)





CREATE TABLE LIF_TNG_APR07INCHI NOLOGGING AS


SELECT jcf_molconvert(cleansmiles,'inchi:SUCF:SRel:SRac:SUU') as inchi,supplier_no


FROM LIF_TNG_APR07QUERY;





Is there a problem creating a table in this way?





The CATALINA_HOME/temp folder can fill up really quickly with temp data and this could be part of the problem?





Cheers,


Daniel.

ChemAxon 60ee1f1328

29-04-2007 15:00:14

This statement is no faster (i.e. not seen it complete yet)





INSERT /*+ APPEND */ INTO LIF_TNG_APR07INCHI (inchi,supplier_no)


SELECT jcf_molconvert(cleansmiles,'inchi:SUCF:SRel:SRac:SUU') as inchi,supplier_no


FROM LIF_TNG_APR07QUERY;

ChemAxon 60ee1f1328

30-04-2007 08:51:35

CREATE TABLE LIF_TNG_APR07INCHI NOLOGGING AS


SELECT jcf_molconvert(cleansmiles,'inchi:SUCF:SRel:SRac:SUU') as inchi,supplier_no FROM LIF_TNG_APR07QUERY


WHERE rownum < 10;


>01:13





CREATE TABLE LIF_TNG_APR07INCHI NOLOGGING AS


SELECT jcf_molconvert(cleansmiles,'inchi:SUCF:SRel:SRac:SUU') as inchi,supplier_no FROM LIF_TNG_APR07QUERY


WHERE rownum < 100;


>13:26





Conclusion is my original statement would take weeks to complete.


(The SQL takes 30 minutes to run?) I have checked the Oracle wait time and it is certainly 99.99% of total elapsed time...


So tomcat returning results set completes in reasonable time, however when attempting the create table or insert as a result of select the execution time goes through the roof - can anybody offer an explanation?





Thanks,


Daniel.

ChemAxon aa7c50abf8

01-05-2007 10:44:09

Hi Daniel,
Quote:
when attempting the create table or insert as a result of select the execution time goes through the roof
Do I understand it correctly: the SELECT statement alone is significantly faster than combined with the CREATE TABLE statetement?





Thanks


Peter

ChemAxon 60ee1f1328

01-05-2007 11:03:01

it looks that way...





it might be that I do not have adequate undotbs...





I am now trying this approach instead now





Code:



update lif_tng_apr07query set inchi = jc_molconvert(cleansmiles,'inchi:SUCF:SRel:SRac:SUU');






Trouble with above if comes across an unconvertible SMILES then it breaks and you have to do very slow record at a time...





The isolated select does complete very quickly but trying to use it subsequently (in various ways) seems to be problematic...





Funnily I have less issues getting below SQL to work in anger...





Code:






select to_clob(concat (concat (concat(jcf_molconvert(cd_smiles,'SDF'),'> <molecule_id>' || CHR(10)),to_char( molecule_id) || CHR(10)),'$$$$')) as sdf,molecule_id

ChemAxon 60ee1f1328

01-05-2007 13:16:44

Runs in 1.5 minutes





Code:



SELECT jcf_molconvert(cleansmiles,'inchi:SUCF:SRel:SRac:SUU') as inchi,supplier_no


FROM LIF_TNG_APR07QUERY






Code:



update lif_tng_apr07query set inchi = jc_molconvert(cleansmiles,'inchi:SUCF:SRel:SRac:SUU');






Either never completes or breaks with error:





ERROR at line 1:


ORA-29532: Java call terminated by uncaught Java exception:


java.lang.Exception: The following exception has been thrown by the servlet:


Exception: Could not export to InChi format.


ORA-06512: at "CSPACE.JCHEM_CORE_PKG", line 233


ORA-06512: at "CSPACE.MOLCONVERT_FUNC", line 23





I've tried everything!!!

ChemAxon 60ee1f1328

01-05-2007 13:26:31

Same SQL apparently hangs within cursor





Code:



DECLARE


CURSOR curs IS SELECT jcf_molconvert(cleansmiles,'inchi:SUCF:SRel:SRac:SUU') as inchi,supplier_no


FROM lif_tng_apr07query;


vInchi VARCHAR2 (4000);


counter NUMBER (4) := 0;


BEGIN


FOR recs IN curs LOOP


EXIT WHEN curs%NOTFOUND;


COUNTER := COUNTER + 1;


IF (COUNTER = 1000) THEN


dbms_output.put_line(counter);


COMMIT;


COUNTER:=0;


END IF;


END LOOP;


COMMIT;


END;


/

ChemAxon 60ee1f1328

01-05-2007 13:48:55

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod


PL/SQL Release 10.2.0.1.0 - Production


CORE 10.2.0.1.0 Production


TNS for 32-bit Windows: Version 10.2.0.1.0 - Production


NLSRTL Version 10.2.0.1.0 - Production


NLSRTL Version 10.2.0.1.0 - Production


JChem version in the database: 3.2.1


JChem version in the Tomcat server: 3.2.1


java.vm.version: 1.5.0_09-b03


java.vm.vendor: Sun Microsystems Inc.


Apache Tomcat/5.5.20


Major JDBC version in Tomcat: 10


Minor JDBC version in Tomcat: 2

ChemAxon 60ee1f1328

01-05-2007 14:02:41

do you suggest thi change in server.xml?





connectionTimeout="20"

ChemAxon 60ee1f1328

02-05-2007 08:50:58

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production


PL/SQL Release 9.2.0.1.0 - Production


CORE 9.2.0.1.0 Production


TNS for 32-bit Windows: Version 9.2.0.1.0 - Production


NLSRTL Version 9.2.0.1.0 - Production


NLSRTL Version 9.2.0.1.0 - Production


JChem version in the database: 3.2.1


JChem version in the Tomcat server: 3.2.1


java.vm.version: 1.5.0_09-b03


java.vm.vendor: Sun Microsystems Inc.


Apache Tomcat/5.5.20


Major JDBC version in Tomcat: 1


Minor JDBC version in Tomcat: 0





OK - so I have tried similar operations in a different env above?





I find that for 10000 records:





Code:



update lif_tng_apr07query set inchi = jc_molconvert(cleansmiles,'inchi:SUCF:SRel:SRac:SUU');








takes 10 minutes (at least 100* faster than 10g env)





This query still runs ok in a short space of time:





Code:



SELECT jcf_molconvert(cleansmiles,'inchi:SUCF:SRel:SRac:SUU') as inchi,supplier_no


FROM LIF_TNG_APR07QUERY








However, this still hangs at the cursor similar to 10g env:





Code:






DECLARE


CURSOR curs IS SELECT jcf_molconvert(cleansmiles,'inchi:SUCF:SRel:SRac:SUU') as inchi,supplier_no


FROM lif_tng_apr07query;


vInchi VARCHAR2 (4000);


counter NUMBER (4) := 0;


BEGIN


FOR recs IN curs LOOP


EXIT WHEN curs%NOTFOUND;


COUNTER := COUNTER + 1;


IF (COUNTER = 1000) THEN


dbms_output.put_line(counter);


COMMIT;


COUNTER:=0;


END IF;


END LOOP;


COMMIT;


END;


 

ChemAxon 60ee1f1328

02-05-2007 11:01:03

And the answer is :





DO NOT PUT THE CATALINA_HOME ON THE SAME DISK AS THE ORACLE_HOME!!!





I now have this statement running at equivalent speed to my laptop i.e. 10000 in 10 minutes which is fine:





Code:



update lif_tng_apr07query set inchi = jc_molconvert(cleansmiles,'inchi:SUCF:SRel:SRac:SUU');








I think the hang still stands in both environments:





Code:



DECLARE


CURSOR curs IS SELECT jcf_molconvert(cleansmiles,'inchi:SUCF:SRel:SRac:SUU') as inchi,supplier_no


FROM lif_tng_apr07query;


vInchi VARCHAR2 (4000);


counter NUMBER (4) := 0;


BEGIN


FOR recs IN curs LOOP


EXIT WHEN curs%NOTFOUND;


COUNTER := COUNTER + 1;


IF (COUNTER = 1000) THEN


dbms_output.put_line(counter);


COMMIT;


COUNTER:=0;


END IF;


END LOOP;


COMMIT;


END;






but we can live with that since the operator works fine.


Cheers,


Daniel.

ChemAxon aa7c50abf8

07-05-2007 10:15:49

Hi Daniel,





Does jcf_molconvert hang with formats other then inchi? Or is this problem limited to inchi?





Thanks


Peter

ChemAxon 60ee1f1328

08-05-2007 10:52:04

Hi Peter,





In the case of inchi generation and using jc_molconvert we find that for large updates the transaction cannot complete and this from observation appears to be because the CATALINA_HOME/temp folder rapidly fills up and then I suspect the process may have difficulty writing to the folder.





Smaller updates (10000 / 10 mins) works ok but performace degrades significantly as this number increases.


With regular flush of the temp folder seems to work ok but it is obviously more of a pain to automate this. I don't know how easy this is but if you removed al inchi temp files after use then this might help with the scalability. I presume any use of jc_molconvert that generates similar temp files is prone to the same scalability issues.





Does jcf_molconvert similarly create temp files? Still have not got the jcf_molconvert cursor based approach to this to work yet but the underlying sql is highly scalable...strange all in all...





Cheers,


Daniel.