The example can be found in the <JChem home>/examples/cartridge/user_def_func/molconvert
directory.
Consider the MolConvert
Java class that converts structures between various file types according to the parameters. We would like to covert structures comming from a database table into aromatized SMILES format using the MolConvert
Java class.
First of all we have to create a PL/SQL operator and function that can be used in SQL select
statements. The operator gets the structure from a table as a parameter and gets the format also as a parameter. For example aromatize structures:
Now create the PL/SQL function and operator (molconvert_sql.sql):SQL> select molconverter(cd_smiles, 'smiles:a') from jchemtable;
SQL> CREATE FUNCTION molconverter_func(query VARCHAR2, type VARCHAR2) RETURN VARCHAR2 AS BEGIN RETURN jchem_core_pkg.send_user_func('MolConvert', '{delim}', query || '{delim}' || type); END; / show errors; SQL> CREATE OPERATOR molconverter BINDING(VARCHAR2, VARCHAR2) RETURN VARCHAR2 USING molconverter_func;
The molconverter_func
function sends its query
and type
parameters to the MolConvert
external Java class using the send_user_func
function.
The MolConvert
class has to implement the JChemCartModul interface. It's doFunc
function gets the parameters sent by the molconverter_func
PL/SQL function (query
and type
).
ChemAxon's MolConvert tool is ideal to convert structures between various types thus we can use it. Let's see the MolConvert
Java class (MolConvert.java):
import chemaxon.formats.MolConverter; import java.io.*; public class MolConvert implements JChemCartModul { public Object doFunc(String[] args) throws Exception { ByteArrayInputStream bis = new ByteArrayInputStream(args[0].getBytes()); ByteArrayOutputStream bout = new ByteArrayOutputStream(); MolConverter mc = new MolConverter(bis, bout, args[1], false); mc.convert(); return new String(bout.toByteArray()); } }
Compile the java file and make sure that the JCART_XCLASSPATH variable includes the parent directory of the MolConvert.class
file before starting JChem Server. Now our new function is ready to call from PL/SQL, for example:
SQL> select molconverter(cd_smiles, 'smiles:a') from jchemtable;