molweight as java stored procedure - odd error

I have attempted to calculate molweight as a java stored procedure.

Code:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "JChemCartFncs" AS public class JChemCartFncs { public static double ibx_jc_molweight (String pInputSmiles) {            chemaxon.struc.Molecule vMol = null;     double mass = 0.0;         try    {    chemaxon.util.MolHandler molhandler = new chemaxon.util.MolHandler (pInputSmiles);        vMol = molhandler.getMolecule();    }    catch (chemaxon.formats.MolFormatException e) {System.err.println("Exception: " + e.getMessage());}                       try         {chemaxon.marvin.calculations.ElementalAnalyserPlugin plugin = new chemaxon.marvin.calculations.ElementalAnalyserPlugin();         // set target molecule         plugin.setMolecule(vMol);         // run the calculation         plugin.run();                 mass = plugin.getMass();    }    catch (chemaxon.marvin.plugin.PluginException e) {System.err.println("Exception: " + e.getMessage());}                            return mass;         } } /

Code:

CREATE OR REPLACE PACKAGE JChemCartFncs AS FUNCTION MolWeightFnc (pInputSmiles IN VARCHAR2) RETURN NUMBER; END JChemCartFncs; / CREATE OR REPLACE PACKAGE BODY JChemCartFncs AS FUNCTION MolWeightFnc (pInputSmiles IN VARCHAR2) RETURN NUMBER AS LANGUAGE JAVA NAME 'JChemCartFncs.ibx_jc_molweight (java.lang.String) return java.lang.Double'; END JChemCartFncs; /

When I execute this:

Code:

select JChemCartFncs.MolweightFnc('C') from dual;

I get an odd error message:





ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission (java.io.FilePermission \chemaxon\custom_aminoacids.dict read) has not been granted to SANDPIT. The PL/SQL to grant this is dbms_java.grant_permission( 'SANDPIT', 'SYS:java.io.FilePermission', '\chemaxon\custom_aminoacids.dict', 'read' )





I attempt to execute the permission as directed and try the select again and i get exactly the same error message again.





windows xp / oracle 9.2





Any comments gratefully received.


Cheers,


Daniel.

And now this error message occurs?





ORA-29532: Java call terminated by uncaught Java exception: java.lang.NoClassDefFoundError

and now slightly more helpful...





ERROR at line 1:


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


java.lang.ExceptionInInitializerError

revert to original error message...

Before going too technical, is not jc_molweight good for any reason?

we are attempting to avoid too many tomcat round trips and wanted to try out replacing jc_molweight/jcf_molweight with a java stored procedure equivalent...if it's no faster then I suspect we will stay with jc_molweight.

Hi Daniel,





I suggest to specify the drive letter as well for the path of the file in question (\chemaxon\custom_aminoacids.dict).





Cheers,


P.

In the 3.2 jar file which I have loaded into oracle there is a java resource called /chemaxon/aminoacids.dict





I don't think "/chemaxon/custom_aminoacids.dict" which is being asked for exists either in the jar file or external chemaxon home and so I guess a drive letter may not be required?





Any other suggestins please?





Cheers,


db.

I suggest to read the Java Developer’s Guide in the Oracle Database documentation collection. It contains much valuable information on how to use Java classes and resources within Oracle.





Cheers,


Peter

Hi Peter,





We really just intend to attempt to replace the functions/operators that do not require a jchem query or an insert (standardize/molweight) and so it would be really helpful if someone could help us resolve this issue - i.e. a file is referenced that does not appear to exist in jchem.jar and appears to bare no relation to the molweight of C as expected in this stored procedure. Reading the documentation is a great idea of course, but I don't think this will help me to resolve this particular issue any quicker.





Thanks for any comments,


Daniel.

Hullo, it is trying to read the file where the SMARTS patterns are for custom amino acids - usually it is at the local disk or at the same location as where the other Chemaxon settings are. I will modify the code not to propagate the security exception. This file is not needed anyway if you are not want to read peptides with fancy amino acids.





Still, until the next release, is there a way to force the input to be as it is in the database? I mean the code is called because it is guessing the file format, but if you give it explicitly during import, it will skip the guessing phase (and the attempt to load this file).

we can carry on no problem with existing, if we do not wish to write or complete a full table query we do not wish to "round trip"...





Any ideas when this change set might be available?





Daniel.

Will be in the next release - that is a week or two. I will try to reproduce both exceptions here to be sure they are fixed.

Daniel,





Have you tried to override the "user.dir" system property to point to a location acceptable to Oracle?





Have you tried Szilva's suggestion: "if you give it explicitly during import, it will skip the guessing phase (and the attempt to load this file)"?





P.

pkovacs wrote:

Daniel, Have you tried Szilva's suggestion: "if you give it explicitly during import, it will skip the guessing phase (and the attempt to load this file)"?

This is the way to do it:

Code:

select JChemCartFncs.MolweightFnc('C{smiles}') from dual;

I have tried the suggestion and this error still prevails...





java.io.IOException: \chemaxon\custom_aminoacids.dict is not an acceptable relative path when user.dir is D:\ORACLE\ORA92\





I will try and set user.dir using somthing like


java.lang.System.setProperty("user.dir",X);





Should X be JCHEMHOME?


(custom_aminoacids.dict does not exist in Jchem (3.2) directory)





Thanks for all your help,


Daniel.

Code:

java.util.Properties p = new java.util.Properties(System.getProperties()); p.setProperty("user.dir","c:\\jchem3.2\\jchem"); System.setProperties(p);

good for changing user.dir, however with this change still get:





java.io.IOException: \chemaxon\custom_aminoacids.dict is not an acceptable relative path when user.dir is c:\jchem3.2\jchem\





java.io.IOException: \chemaxon\licenses.dat is not an acceptable relative path when user.dir is c:\jchem3.2\jchem\





what should user.dir be in either case (Second case is possibly obvious)





cheers,


db.

I have no experience with this feature of the Oracle JVM, but I think you can set it to any location on the file system accepted by Oracle.





The criteria "accepted by Oracle" needs a bit more research. I remember at least one Oracle (init) parameter that needs to be set for Oracle processes to be allowed (by Oracle's own security management) to access a particular location on the file system. Check the Oracle Database Reference documentation to find out which parameter needs to be set.





I assume that you also have to make sure that the operating system allows the Oracle session process to access this file system location. Check the identity of your Oracle processes and give the necessary (initially maybe all) privileges to this identity.





P.

I think the parameter to which you refer is probably UTL_FILE_DIR.


I will try this for the licenses.dat directory and see what happens...





Cheers,


Daniel.

I have added the chemaxon licenses directory to the utl_file_dir list and tried this select statement again





select JChemCartFncs.MolweightFnc('C{smiles}') from dual;





unfortunately, the issues remain the same...





*** 2006-12-04 11:06:43.000


*** SESSION ID:(9.4) 2006-12-04 11:06:43.000


java.io.IOException: \chemaxon\custom_aminoacids.dict is not an acceptable relative path when user.dir is c:\documents and settings\Daniel Butler\chemaxon\


at oracle.aurora.java.io.OracleIO.transformRelative(OracleIO.java)


at java.io.FileInputStream.open(FileInputStream.java)


at java.io.FileInputStream.<init>(FileInputStream.java)


at chemaxon.marvin.modules.AminoAcidSource.initAAdict(AminoAcidSource.java:112)


at chemaxon.marvin.modules.AminoAcidSource.getInstance(AminoAcidSource.java:66)


at chemaxon.formats.MFileFormatUtil.canBe3LetterPeptide(MFileFormatUtil.java:500)


at chemaxon.formats.MolInputStream.init(MolInputStream.java:380)


at chemaxon.formats.MolInputStream.<init>(MolInputStream.java:101)


at chemaxon.formats.MolInputStream.<init>(MolInputStream.java:43)


at chemaxon.util.MolHandler.importMol(MolHandler.java:680)


at chemaxon.util.MolHandler.setMolecule(MolHandler.java:159)


at chemaxon.util.MolHandler.<init>(MolHandler.java:94)


at JChemCartFncs.ibx_jc_molweight(JChemCartFncs.java:22)


Exception: java.io.IOException: \chemaxon\custom_aminoacids.dict is not an acceptable relative path when user.dir is c:\documents and settings\Daniel Butler\chemaxon\


java.lang.ExceptionInInitializerError:


java.io.IOException: \chemaxon\licenses.dat is not an acceptable relative path when user.dir is c:\documents and settings\Daniel Butler\chemaxon\


at oracle.aurora.java.io.OracleIO.transformRelative(OracleIO.java)


at java.io.FileInputStream.open(FileInputStream.java)


at java.io.FileInputStream.<init>(FileInputStream.java)


at java.io.FileInputStream.<init>(FileInputStream.java)


at chemaxon.marvin.modules.LicenseManager.loadKeys(LicenseManager.java:265)


at chemaxon.marvin.modules.LicenseManager.<init>(LicenseManager.java:232)


at chemaxon.marvin.modules.LicenseManager.get(LicenseManager.java:214)


at chemaxon.marvin.plugin.CalculatorPlugin.<clinit>(CalculatorPlugin.java:231)


at JChemCartFncs.ibx_jc_molweight(JChemCartFncs.java:30)





I would try and include this directory but don't know where it exists?


\chemaxon\custom_aminoacids.dict





I guess your next release won't be that far away by now?





Cheers,


Daniel.

If I were you, I would also try setting "user.home" to "c:\documents and settings\Daniel Butler\chemaxon".





We plan a new release for this week.





Cheers,


Peter

HI Peter,





The error above still stands with user.dir set in the java stored procedure...





Cheers,


Daniel.

Hi Daniel,





user.dir ("working directory where the JVM has been started") you have already tried. I suggested to try "user.home". "user.home" is the property used by Marvin/JChem internally to find the user's home directory and store its cuckoo's eggs there.





Unless the Oracle Java runtime completely defies convention and common sense, with "user.home" being set I would not expect you to receive errors like:

Quote:

java.io.IOException: \chemaxon\custom_aminoacids.dict is not an acceptable relative path when user.dir is D:\ORACLE\ORA92\

because there would be then a drive letter in front of the path and there is then no way for Oracle to interpret that path as relative. (I assume that the default "user.home" for the Oracle JVM is "\" which is not an absolute absolute path without a drive letter. [It is absolute only relative to the current drive.])





Cheers


Peter

sorry, it's been a long old week...and I need glasses...will try again and report back...

right then, that seems to have worked to an extent...no error but the function return 0?

First off, don't "swallow" the Java exceptions in your Java stored procedures. Do not catch them unless you have a very clear idea what to do with them. Either remove the catch blocks or rethrow the exceptions caught.





P.

Code:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "JChemCartFncs" AS public class JChemCartFncs { public static double ibx_jc_molweight (String pInputSmiles) throws chemaxon.formats.MolFormatException, chemaxon.marvin.plugin.PluginException {    chemaxon.struc.Molecule vMol = null;     double mass = 0.0;    java.util.Properties p = new java.util.Properties(System.getProperties());                 p.setProperty("user.dir","c:\\documents and settings\\Daniel Butler\\chemaxon");        p.setProperty("user.home","c:\\documents and settings\\Daniel Butler\\chemaxon");                System.setProperties(p);    chemaxon.util.MolHandler molhandler = new chemaxon.util.MolHandler (pInputSmiles);        vMol = molhandler.getMolecule();        chemaxon.marvin.calculations.ElementalAnalyserPlugin plugin = new chemaxon.marvin.calculations.ElementalAnalyserPlugin();         // set target molecule         plugin.setMolecule(vMol);         // run the calculation         plugin.run();                 mass = plugin.getMass();    return mass;         } } /

OK so the non-catch version yields the same error as before...





ERROR at line 1:


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


java.security.AccessControlException: the Permission (java.io.FilePermission


c:\documents and settings\Daniel


Butler\chemaxon\chemaxon\custom_aminoacids.dict read) has not been granted to


SANDPIT. The PL/SQL to grant this is dbms_java.grant_permission( 'SANDPIT',


'SYS:java.io.FilePermission', 'c:\documents and settings\Daniel


Butler\chemaxon\chemaxon\custom_aminoacids.dict', 'read' )

One "chemaxon" too many in the path. Try setting user.home to "c:\documents and settings\Daniel Butler"





And do not forget to check UTL_FILE_DIR and grant the Java file access privilege on this one.





P.

Hey Peter,





You are most certianly the chosen one, as this is now working!





Thanks for your fantastic help as always,





Daniel.