A way to detect Markush strcuture to avoid cartridge error

Hi,

I have a standard structure table and have created a cartridge index for it. The structureType is defined as molecules. However during the semi automated process of updating the structure table  there are bad Markush structures mixed with molecules. These Markush structures will disrupt the insertion process or cartridge index updating to be precise. I am getting following error from java code for mol insertion

Inserting a query or Markush structure is not allowed for table: "IP.MOL_IBM_IDX_JCX"

ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 255

ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 650

Is there a function I can use to test if a molecule is a Markush or Query before the insertion? Eventually the insertion will run in automated mode. We definitely need to screen out the Markkush stratures before it gets inserted.

Thanks

DOng

I found function isQuery() to detect the query mol. Do not know this will filter out the Query and  Markush type all together. Following is a sample of list, cartridge would treat them as either Query or Markush, I have to remove them in order to build the molecule cartridge index:

CC1=CCnC1
C=C(Cc1CCC2=C(C1)SN=C2)C1CN2CCC1CC2
Nc1Cc(Cnc1=O)C1=CC=CC(NC(=O)C2cCCC2)=C1
Nc1CcCc2n(cnc12)C1OC(O)C=C1
Cc1CCCc(C1)[N+]#[C-]
CC(O)(c1CcCcC1)C(F)(F)F
NC1=NC(=O)C([2CH2]1)c1CCCCC1

Hi Dong,

I think the JCF.INSERTABLE function could do the job: http://www.chemaxon.com/jchem/doc/dev/cartridge/cartapi.html#jcf_insertable .

Peter

Hi Peter,

It is a good solution. I have changed my code a bit. Currently I am using jdbc SQL batch  insert preparedStatement. With your PLSQL function I will call a stored procedure for insertion, before the insertion calling function isinsertable to check ...

Thanks

Dong

Hi Peter,

I wrote following insert procedure but get an PLS error : component INSERTABLE must be declared,

CREATE OR REPLACE PROCEDURE insertIntoSmiles( smiles in varchar2 )

 IS

test number;

BEGIN 

test := jcf.insertable( smiles, 'IP', 'smiles', 'smiles' );

if test = 1 then

insert into smiles ( id, smiles ) values ( chemip_id.nextval, smiles );

end if;

 

ENDinsertIntoSmiles;

 

Dong,

This function is available starting with JChem version 5.9.0. You have perhaps an earlier version?

Peter

Hi Peter,

I suppose I need to upgrade the JChem Cartridge to 5.9.0 in order to take advantage of the INSERTABLE.

To upgrade JChem Cart, I suppose I need to ask DBA to create JCC_UPGR_STAGE schema, and do following granting: please confirm

grant unlimited tablespace to JCC_UPGR_STAGE;
grant create session to JCC_UPGR_STAGE;
grant create table to JCC_UPGR_STAGE;
grant create sequence to JCC_UPGR_STAGE;
grant create procedure to JCC_UPGR_STAGE;
grant create trigger to JCC_UPGR_STAGE;
grant create type to JCC_UPGR_STAGE;
grant create operator to JCC_UPGR_STAGE;
grant create indextype to JCC_UPGR_STAGE;

Dong,

Since you seem to be able to use Java, you may use he JChem API directly as well.

You may call this method even without cartridge upgrade, and it can even be more efficient (1 less call to the DB/Cartridge):

http://www.chemaxon.com/jchem/doc/dev/java/api/chemaxon/jchem/db/UpdateHandler.html#isInsertable(chemaxon.struc.Molecule, int, boolean)

Please let us know if this helps.

Best,

Szilard

 

Hi Szilard,

I did following:

import chemaxon.jchem.db.*

...... 

 

if ( isInsertable( m, TableTypeConstants.TABLE_TYPE_MOLECULES, true ) ) {}

however I am getting error "method isInsertable  is undefined ...". Do I have jchem version issue here? I am using jchem 5.8 API

Thanks much

You saved my day!!!!!!!

Dong

Hi,

You forgot the class name, please try this way:

if ( UpdateHandler.isInsertable( m, TableTypeConstants.TABLE_TYPE_MOLECULES, true ) ) {}

Best,

Szilard

 

 

The method works out great, so I do not have baby set the inserting process. All the bad players quoted early in the message got tossed!!!

Thanks again.

Dong