The example can be found in the <JChem home>/examples/cartridge/user_def_func/getatomcount directory.
Consider the GetAtomCount
Java class that returns the atom count of a structure. We would like to calculate the atom count of structures comming from a database table using the GetAtomCount
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. For example:
Now create the PL/SQL function and operator (getatomcount_sql.sql):SQL> select getatomcount(cd_smiles) from jchemtable;
SQL> CREATE FUNCTION getatomcount_func(query VARCHAR2) RETURN NUMBER AS BEGIN return to_number(jchem_core_pkg.send_user_func('GetAtomCount', '', query)); END; / show errors; CREATE OPERATOR getatomcount BINDING(VARCHAR2) RETURN NUMBER USING getatomcount_func;
The getatomcount_func
function sends the query
parameter to the GetAtomCount
external Java class using the send_user_func
function.
The GetAtomCount
class has to implement the JChemCartModul interface. It's doFunc
function gets the parameters sent by the getatomcount_func
PL/SQL function (query
).
Using ChemAxon's Molecule and MolHandler classes we can count the number of atoms in a structure. Let's see the GetAtomCount
Java class (GetAtomCount.java):
import chemaxon.util.MolHandler; import chemaxon.struc.Molecule; import chemaxon.jchem.cartridge.JChemCartModule; public class GetAtomCount implements JChemCartModule { public Object doFunc(String[] args) throws Exception { MolHandler mh = new MolHandler(args[0]); return new Integer(mh.getMolecule().getAtomCount()); } }
Compile the java file and include the parent directory of the the GetAtomCount.class
file in the JCART_XCLASSPATH environment variable before starting JChem Server. Now our new function is ready to call from PL/SQL, for example:
SQL> select getatomcount(cd_smiles) from jchemtable;
Now makes the evaluation of the operator appearing in the where
cluse faster.
Open the reg_user_def_ops.sql
file from the cartridge
directory of the JChem package with a text editor. Insert the name of the getatomcount
operator:
CREATE OR REPLACE INDEXTYPE jc_idxtype FOR ----------------------------------------- -- START LISTING OPERATORS ----------------------------------------- getatomcount(VARCHAR2), ----------------------------------------- -- STOP LISTING OPERATORS ----------------------------------------- jc_contains(VARCHAR2, VARCHAR2), jc_equals(VARCHAR2, VARCHAR2), jc_matchcount(VARCHAR2, VARCHAR2), jc_dissimilarity(VARCHAR2, VARCHAR2), jc_tanimoto(VARCHAR2, VARCHAR2), jc_logp(VARCHAR2), jc_logd(VARCHAR2, NUMBER), jc_pka(VARCHAR2, VARCHAR2, NUMBER), jc_tpsa(VARCHAR2), jc_molweight(VARCHAR2), jc_formula(VARCHAR2), jc_formula_eq(VARCHAR2, VARCHAR2) USING jc_idxtype_im;
Save the file and close it. Drop all indexes (drop index idx_name
) you've created before with the jc_idxtype
indextype. Open the edited reg_user_def_ops.sql
file in a PL/SQL worksheet and execute it. Now create your indexes again. An index has to be created on the table on which you want to use the getatomcount operator.
SQL> create index my_ind on jchemtable(cd_smiles) indextype is jc_idxtype;
If you've created the index on the table execute the following insert statement:
SQL> insert into jc_idx_udop values('getatomcount', 'GetAtomCount', '', '$1');
Finally another Java class is needed to evaluate the relational operator in the where
clause. This class has to implement the JChemCartEvalModul
interface. The name of this class has to be: GetAtomCount_eval
.
The eval
function of the class gets the value calculated by GetAtomCount
and gets the parameters of the relational condition in the "opStr
" String. The function has to decide whether the calculated value is appropiate to the relational condition or not. The GetAtomCount_eval class:
import java.util.StringTokenizer; import chemaxon.jchem.cartridge.JChemCartEvalModule; public class GetAtomCount_eval implements JChemCartEvalModule { public boolean eval(Object o, String opStr) throws Exception { int result = ((Integer)o).intValue(); boolean isOk = false; StringTokenizer st = new StringTokenizer(opStr, "/"); String start = st.nextToken(); String stop = st.nextToken(); int op = Integer.parseInt(st.nextToken()); if(stop.equals("null")) { isOk = op == 4 ? result >= Integer.parseInt(start) : result > Integer.parseInt(start); } else if(start.equals("null")) { isOk = op == 8 ? result <= Integer.parseInt(stop) : result < Integer.parseInt(stop); } else { isOk = op == 13 ? result == Integer.parseInt(start) : op == 12 ? result <= Integer.parseInt(stop) && result >= Integer.parseInt(start) : op == 8 ? result <= Integer.parseInt(stop) && result > Integer.parseInt(start) : op == 4 ? result < Integer.parseInt(stop) && result >= Integer.parseInt(start) : result < Integer.parseInt(stop) && result > Integer.parseInt(start); } return isOk; } }
Compile the java file and include the parent directory of the
GetAtomCount.class
file in the JCART_XCLASSPATH environment
variable before starting JChem Server. The getatomcount
operator
is ready to evaluate faster then before if it is in the where
clause.
SQL> select count(*) from jchemtable where getatomcount(cd_smiles) < 14;