how to get back the molfile of the standardized structure?

Hi,

 

after insertion of structure with Jchem Manager (with a standardizer file where our own business rules have been described), I would like to get back the molfile of the standardizer structure. I try with the Cd_structure column but i have only the original structure. With the cd_smiles column, i can have the standardized structure. So in a sql query, how can I have the standardized molfile?

 

Thanks 

 

Kiet

Hi Kiet,

You can get the standardized structure from the cd_smiles column in 'smiles' format. Subsequently, you have to convert the smiles string to 'mol' format (or to any other supported format) by using our MolConverter tool. E.g.

molconvert mol 'CCC' -o molfile.mol

Another solution would be to execute the standardization directly on the original structures:

standardize -c mystandardizer_rules.xml original_structures.sdf -f sdf -o standardized_structures.sdf

Best regards,

Krisztina

Dear Kiet,

In case you have the Cartridge, you can do these transformations via SQL query as well, please see the following operators:

http://www.chemaxon.com/jchem/doc/dev/cartridge/cartapi.html#jc_standardize

http://www.chemaxon.com/jchem/doc/dev/cartridge/cartapi.html#jc_molconvert

Please note that in certain, rare cases the cd_smiles column might have some NULL values.

It is also rather intended to be used for internal usage.

So I would rather suggest to standardize the cd_structure with the same standardization as used for the table instead. (The default is aromatization and removal of explicit H atoms)

http://www.chemaxon.com/jchem/doc/user/query_standard.html#standardizationDB

As an additional benefit the original coordinates are also preserved in this case.

Best regards,

Szilard

Hi,

 

select jc_standardize(cd_structure, 'standardizer_CN.xml') from sub_structure....

 

this query doesn't run.

standarizer_CN.xml is the xml file for business rules.

Could you help me?

Thanks 

 

Kiet

Hi Tien,

Instead of

select jc_standardize(cd_structure, 'standardizer_CN.xml') from sub_structure....

run the following query

select jc_standardize(cd_structure, 'config:aromatize..removeexplicitH') from sub_structure....

or

select jc_standardize(cd_structure, 'config:<?xml ....... </StandardizerConfiguration>') from sub_structure....

Best regards,

Krisztina

hi,

 

select jc_standardize(cd_structure,'config:<?xml standarizer_CN.xml </StandardizerConfiguration>') from sub_structure

 

this query doesn't run...

could you give me the right query when we have a xml file?

 

Thanks 

Sorry for the inconvenience,  I thought it is clear, that in place of 

<?xml ....... </StandardizerConfiguration>

you have to insert the content of your standardizer_CN.xml (as it is written in our documentation linked above).

Best regards,

Krisztina

Hi,

 

I try with only one rule but still wrong query :

 

select STRU_ID, jc_standardize(cd_structure,'config:<?xml version="1.0" encoding="UTF-8"?> <StandardizerConfiguration> <Actions> <Transformation ID="nitro" Structure="*N(=O)=O&gt;&gt;[#8-]-[#7+](-*)=O"/> </Actions> </StandardizerConfiguration>') from sub_structure

 

Thanks for your help

Hi,

Please try the following command:

select STRU_ID, jc_standardize(cd_structure,'sep=! config:<?xml version="1.0" encoding="UTF-8"?> <StandardizerConfiguration> <Actions> <Transformation ID="nitro" Structure="*N(=O)=O>>[#8-]-[#7+](-*)=O"/> </Actions> </StandardizerConfiguration>') from sub_structure;

Krisztina

 

Hi,

 

select jc_standardize('CC1[C@H]2CCC3C4CC=C5C[C@H](CC[C@]5(C)C4CC[C@]23CN1C)OC(C)=O |r,t:8|', 'sep=! config:<?xml version="1.0" encoding="UTF-8"?> <StandardizerConfiguration> <Actions> <Transformation ID="nitro" Structure="*N(=O)=O>>-(-*)=O"/> </Actions> </StandardizerConfiguration>') from sub_structure where  STRU_ID='AB-00022779';

 

ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.rmi.ServerException: RemoteException occurred in server thread; nested
exception is:
java.rmi.RemoteException: Error recognizing the token: TransformAction. Invalid
parameters were provided!; nested exception is:
java.lang.IllegalArgumentException: Error recognizing the token:
TransformAction. Invalid parameters were provided!
ORA-06512: at "SYSADMIN.JCHEM_CORE_PKG", line 429
ORA-06512: at "SYSADMIN.STANDARDIZE_FUNC", line 12

Sorry,we can't reproduce the error. I'm afraid we don't have enough information about your system.

Our latest answers were based on the assumption that you have Oracle JChem Cartridge environment, your table was created with JChem Manager, then Cartridge index was created on the cd_structure column of that table. Is this assumption correct?

Best regards

Krisztina

you're right : we use jchem Cartridge and the table structure was created with JCM

 

in the log file of the jchem server , i have this error :

 

SEVERE: Error recognizing the token: TransformAction. Invalid parameters were provided!

 

Thank you.

Can you run this select?

select STRU_ID, jc_standardize(cd_structure,'sep=! config:<?xml version="1.0" 

encoding="UTF-8"?> <StandardizerConfiguration> <Actions> 

<Transformation ID="nitro" 

Structure="*N(=O)=O>>[#8-]-[#7+](-*)=O"/> </Actions> 

</StandardizerConfiguration>') from sub_structure;

In your last failing command the action string seems not to be valid: *N(=O)=O>>-(-*)=O

Best regards,

Krisztina

 

 

same error

Have you already tried this sql query without error?

Hi,

I could run without error the following command:

select jc_standardize(cd_structure,'sep=! config:<?xml version="1.0" encoding="UTF-8"?> <StandardizerConfiguration> <Actions> <Transformation ID="nitro" Structure="*N(=O)=O>>[#8-]-[#7+](-*)=O"/> </Actions> </StandardizerConfiguration>') from test where cd_id='1';

where test was the name of my JChem table where I imported the structure CC1[C@H]2CCC3C4CC=C5C[C@H](CC[C@]5(C)C4CC[C@]23CN1C)OC(C)=O |r,t:8|', and an index was created on test(cd_structure) .

An other successful run is the next command which doesn't use tables or indexes, only the standardization:

select jc_standardize('CC1[C@H]2CCC3C4CC=C5C[C@H](CC[C@]5(C)C4CC[C@]23CN1C)OC(C)=O |r,t:8|', 'sep=! config:<?xml version="1.0" encoding="UTF-8"?> <StandardizerConfiguration> <Actions> <Transformation ID="nitro" Structure="*N(=O)=O>>[#8-]-[#7+](-*)=O"/> </Actions> </StandardizerConfiguration>') from DUAL;

Best regards,
Krisztina

Ok it works :

 

select jc_standardize(cd_structure,'sep=! config:<?xml version="1.0" encoding="UTF-8"?> <StandardizerConfiguration> <Actions> <Transformation ID="nitro" Structure="*N(=O)=O>>[O-][N+](*)=O"/> <Transformation ID="azide" Structure="*[N-][N+]#N>>*N=[N+]=[N-]"/> <Transformation ID="diazoalkane" Structure="*[C-](*)[N+]#N>>*C(*)=[N+]=[N-]"/> <Transformation ID="aliph-N-oxide" Structure="*N(*)(*)=O>>[O-][N+](*)(*)*"/> <Transformation ID="Z-aro-N-oxide1" Structure="*N(=O)=*>>[O-][N+](*)=*"/> <Transformation ID="Z-aro-N-oxide2" Structure="*:n(:*)=O>>[O-][n+](:*):*"/> <Transformation ID="nitrate" Structure="*ON(=O)=O>>[O-][N+](=O)O*"/> <Transformation ID="sulfoxide" Structure="[O-](*)*>>*S(*)=O"/> <Transformation ID="sulfone" Structure="[O-](*)(*)=O>>*S(*)(=O)=O"/> <Transformation ID="phosphoryl" Structure="[O-][P+](*)(*)*>>*P(*)(*)=O"/> <Transformation ID="phosphorane1" Structure="*[C-](*)[P+](*)(*)*>>*C(*)=P(*)(*)*"/> <Transformation ID="phosphorane2" Structure="*[CH-][P+](*)(*)*>>*C=P(*)(*)*"/> <Transformation ID="phosphorane3" Structure="[CH2-][P+](*)(*)*>>*P(*)(*)=C"/> <Transformation ID="iodoxy" Structure="*O(*)(=O)O*>>[O-](*)(O*)O*"/> </Actions> </StandardizerConfiguration>') from sub_structure where cd_id='1';

 

thanks!

Hi, 

 

I try ton put this sql query in a php function but it doesn't work :

 

 function lire_structure($inlist_stru,$link) {

 

if ($inlist_stru=='') return '';

$stmt = OCIParse($link,"select STRU_ID,jc_standardize(cd_structure,'sep=! config:<?xml version="1.0" encoding="UTF-8"?> <StandardizerConfiguration> <Actions> <Transformation ID="nitro" Structure="*N(=O)=O>>[O-][N+](*)=O"/> <Transformation ID="azide" Structure="*[N-][N+]#N>>*N=[N+]=[N-]"/> <Transformation ID="diazoalkane" Structure="*[C-](*)[N+]#N>>*C(*)=[N+]=[N-]"/> <Transformation ID="aliph-N-oxide" Structure="*N(*)(*)=O>>[O-][N+](*)(*)*"/> <Transformation ID="Z-aro-N-oxide1" Structure="*N(=O)=*>>[O-][N+](*)=*"/> <Transformation ID="Z-aro-N-oxide2" Structure="*:n(:*)=O>>[O-][n+](:*):*"/> <Transformation ID="nitrate" Structure="*ON(=O)=O>>[O-][N+](=O)O*"/> <Transformation ID="sulfoxide" Structure="[O-](*)*>>*S(*)=O"/> <Transformation ID="sulfone" Structure="[O-](*)(*)=O>>*S(*)(=O)=O"/> <Transformation ID="phosphoryl" Structure="[O-][P+](*)(*)*>>*P(*)(*)=O"/> <Transformation ID="phosphorane1" Structure="*[C-](*)[P+](*)(*)*>>*C(*)=P(*)(*)*"/> <Transformation ID="phosphorane2" Structure="*[CH-][P+](*)(*)*>>*C=P(*)(*)*"/> <Transformation ID="phosphorane3" Structure="[CH2-][P+](*)(*)*>>*P(*)(*)=C"/> <Transformation ID="iodoxy" Structure="*O(*)(=O)O*>>[O-](*)(O*)O*"/> </Actions> </StandardizerConfiguration>') from sub_structure where stru_id in(".$inlist_stru.")");

OCIExecute($stmt);

while (OCIFetchInto ($stmt, $row, OCI_RETURN_LOBS)) {

$i++;

$tabstru[$row[0]]=$row[1];

//echo "$i<br>";

}

return $tabstru;

}

 

I have an error : 

Parse error: syntax error, unexpected T_STRING in /www-data-dev/cn/fonction.php on line 444

any idea?

Hi Kiet,

If you want to use your select in a php, you have to modify it in accordance with the rule of the language.
E.g. your select statement should be enclosed by single quotes, or you should escape double qoutes (\" instead of ").

You can find examples here: http://www.php.net/manual/en/language.types.string.php

Regards,
Tamás

Thanks!

hi,

I would like to create trigger to insert in the column "cd_molfile",  the standardized molfile format :

 

CREATE OR REPLACE TRIGGER "TRG_CD_MOLFILE" 

  BEFORE INSERT ON SUB_STRUCTURE

  FOR EACH ROW

  DECLARE

BEGIN

 SELECT jc_standardize(:NEW.CD_STRUCTURE,'sep=! config:<?xml version="1.0" encoding="UTF-8"?> <StandardizerConfiguration> <Actions> <Aromatize ID="Aromatize" Type="general"/> <Transformation ID="nitro" Structure="*N(=O)=O>>[O-][N+](*)=O"/> <Transformation ID="azide" Structure="*[N-][N+]#N>>*N=[N+]=[N-]"/> <Transformation ID="diazoalkane" Structure="*[C-](*)[N+]#N>>*C(*)=[N+]=[N-]"/> <Transformation ID="aliph-N-oxide" Structure="*N(*)(*)=O>>[O-][N+](*)(*)*"/> <Transformation ID="Z-aro-N-oxide1" Structure="*N(=O)=*>>[O-][N+](*)=*"/> <Transformation ID="Z-aro-N-oxide2" Structure="*:n(:*)=O>>[O-][n+](:*):*"/> <Transformation ID="nitrate" Structure="*ON(=O)=O>>[O-][N+](=O)O*"/> <Transformation ID="sulfoxide" Structure="[O-](*)*>>*S(*)=O"/> <Transformation ID="sulfone" Structure="[O-](*)(*)=O>>*S(*)(=O)=O"/> <Transformation ID="phosphoryl" Structure="[O-][P+](*)(*)*>>*P(*)(*)=O"/> <Transformation ID="phosphorane1" Structure="*[C-](*)[P+](*)(*)*>>*C(*)=P(*)(*)*"/> <Transformation ID="phosphorane2" Structure="*[CH-][P+](*)(*)*>>*C=P(*)(*)*"/> <Transformation ID="phosphorane3" Structure="[CH2-][P+](*)(*)*>>*P(*)(*)=C"/> <Transformation ID="iodoxy" Structure="*O(*)(=O)O*>>[O-](*)(O*)O*"/> </Actions> </StandardizerConfiguration>') INTO :NEW.CD_MOLFILE FROM dual;

END ;

/  2    3    4    5    6    7    8  

 

Warning: Trigger created with compilation errors.

 

SQL> show errors;

Errors for TRIGGER "TRG_CD_MOLFILE":

 

LINE/COL ERROR

-------- -----------------------------------------------------------------

3/2      PL/SQL: SQL Statement ignored

3/9      PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got

         BLOB

if I replace NEW.CD_STRUCTURE by NEW.CD_SMILES, it works.

could you help me?

Hi Kiet,

What is the datatype of the column "cd_molfile"?

Krisztina

Hi Krisztina,

 

cd_molfile : CLOB

 Using NEW.CD_SMILES and outFormat:mol, the trigger is created with success but some of your colleagues Balázs, told me that the cd_smiles field is an internal use only column, that should not be used but the cd_structure column.

 

  CREATE OR REPLACE TRIGGER "TRG_CD_MOLFILE" 

  BEFORE INSERT ON SUB_STRUCTURE

  FOR EACH ROW

  DECLARE

BEGIN

 SELECT  jc_standardize(:NEW.CD_SMILES,'sep=! config:<?xml version="1.0" encoding="UTF-8"?> <StandardizerConfiguration> <Actions> <Aromatize ID="Aromatize" Type="general"/> <Transformation ID="nitro" Structure="*N(=O)=O>>[O-][N+](*)=O"/> <Transformation ID="azide" Structure="*[N-][N+]#N>>*N=[N+]=[N-]"/> <Transformation ID="diazoalkane" Structure="*[C-](*)[N+]#N>>*C(*)=[N+]=[N-]"/> <Transformation ID="aliph-N-oxide" Structure="*N(*)(*)=O>>[O-][N+](*)(*)*"/> <Transformation ID="Z-aro-N-oxide1" Structure="*N(=O)=*>>[O-][N+](*)=*"/> <Transformation ID="Z-aro-N-oxide2" Structure="*:n(:*)=O>>[O-][n+](:*):*"/> <Transformation ID="nitrate" Structure="*ON(=O)=O>>[O-][N+](=O)O*"/> <Transformation ID="sulfoxide" Structure="[O-](*)*>>*S(*)=O"/> <Transformation ID="sulfone" Structure="[O-](*)(*)=O>>*S(*)(=O)=O"/> <Transformation ID="phosphoryl" Structure="[O-][P+](*)(*)*>>*P(*)(*)=O"/> <Transformation ID="phosphorane1" Structure="*[C-](*)[P+](*)(*)*>>*C(*)=P(*)(*)*"/> <Transformation ID="phosphorane2" Structure="*[CH-][P+](*)(*)*>>*C=P(*)(*)*"/> <Transformation ID="phosphorane3" Structure="[CH2-][P+](*)(*)*>>*P(*)(*)=C"/> <Transformation ID="iodoxy" Structure="*O(*)(=O)O*>>[O-](*)(O*)O*"/> </Actions> </StandardizerConfiguration>!outFormat:mol') INTO :NEW.CD_MOLFILE FROM dual;

END ;

/

 

I try to insert structures with this last trigger but with big structures i have this error : 

ORA-06502 and ORA-065212

I attach the smiles of the big structure

 

Kiet

Hi Kiet,

jc_standardize returns the standardized srtucture in the same datatype as the original structure had. In Oracle database, the datatype of cd_structure field is BLOB, so you have to use this datatype for cd_molfile column, too, (or convert the returned BLOB to the present format of your cd_molfile column:CLOB).

cd_smiles column of JChem tables contains the standardized form of the originally imported structures in SMILES format, but there are cases when the standardized structure cannot be represented in SMILES because of some specific features of the structure, and in case of these records, cd_smiles field has NULL value. This is the main reason why we do not recommend our user to run applications based on cd_smiles field.

Best regards,

Krisztina

Hi,

 

So how can I create the trigger with the column cd_structure  to insert in the cd_molfile column the standarized molfile of structure?:

I try :

CREATE OR REPLACE TRIGGER "TRG_CD_MOLFILE"
  BEFORE INSERT ON SUB_STRUCTURE
  FOR EACH ROW
  DECLARE
BEGIN
 SELECT  jc_standardize(:NEW.CD_STRUCTURE,'sep=! config:<?xml version="1.0" encoding="UTF-8"?> <StandardizerConfiguration> <Actions> <Aromatize ID="Aromatize" Type="general"/> <Transformation ID="nitro" Structure="*N(=O)=O>>[O-][N+](*)=O"/> <Transformation ID="azide" Structure="*[N-][N+]#N>>*N=[N+]=[N-]"/> <Transformation ID="diazoalkane" Structure="*[C-](*)[N+]#N>>*C(*)=[N+]=[N-]"/> <Transformation ID="aliph-N-oxide" Structure="*N(*)(*)=O>>[O-][N+](*)(*)*"/> <Transformation ID="Z-aro-N-oxide1" Structure="*N(=O)=*>>[O-][N+](*)=*"/> <Transformation ID="Z-aro-N-oxide2" Structure="*:n(:*)=O>>[O-][n+](:*):*"/> <Transformation ID="nitrate" Structure="*ON(=O)=O>>[O-][N+](=O)O*"/> <Transformation ID="sulfoxide" Structure="[O-](*)*>>*S(*)=O"/> <Transformation ID="sulfone" Structure="[O-](*)(*)=O>>*S(*)(=O)=O"/> <Transformation ID="phosphoryl" Structure="[O-][P+](*)(*)*>>*P(*)(*)=O"/> <Transformation ID="phosphorane1" Structure="*[C-](*)[P+](*)(*)*>>*C(*)=P(*)(*)*"/> <Transformation ID="phosphorane2" Structure="*[CH-][P+](*)(*)*>>*C=P(*)(*)*"/> <Transformation ID="phosphorane3" Structure="[CH2-][P+](*)(*)*>>*P(*)(*)=C"/> <Transformation ID="iodoxy" Structure="*O(*)(=O)O*>>[O-](*)(O*)O*"/> </Actions> </StandardizerConfiguration>!outFormat:mol') INTO :NEW.CD_MOLFILE FROM dual;
END ;
/

 

Warning: Trigger created with compilation errors.

SQL> show errors;
Errors for TRIGGER "TRG_CD_MOLFILE":

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/2      PL/SQL: SQL Statement ignored
3/10     PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got
         BLOB

thanks

Hi Kiet,

 

You are getting the error, as Krisztina already answered (Posted: Fri Jan 10, 2014 9:57 am) to your post (Posted: Thu Jan 09, 2014 4:28 pm), because the datatype returned by the function is not compatible with the one defined for the field you want to insert the data in. This seems to be an SQL coding/DB problem to which we can not provide support as it is not a Chemaxon product error. Issues alike are handled by Chemaxon as part of our consultancy services. If you are interested in such cooperation, just let me know, and I can link you with the appropriate people.

 

As a hint we can tell, that we have the feeling that the error returned by Oracle might not be accurate, so we would start the investigation checking the correctness of the error message first, and modifying the code just after getting more information about the error itself. However, please note, that we are not familiar with your data structure and your application, and we derive conclusions based solely on the data provided by you in this forum. We would advise you at this point to get in touch with your DB administrator in order to harmonize your code with the existing infrastructure. 

 

Best regards,

Norbert

Hi Norbert,

 

I would like to create this trigger because I cannot use the cd_smiles column to display the standardized structure.

To solve this problem and display the standardized structure, I have to use the cd_structure column with jc_standardize function. This last option cost many time when I would like to create a sdfile of 50.000 standardized structures....(about 1h).

So what option do you propose me to display the standardized without using jc_standardize function.

 

Kiet

Hi Kiet, 

 

The  jc_standardize function posted on Fri Jan 10, 2014 4:07 pm does exactly what you want. You are getting the error (or at least the Oracle complains so) because the data type returned by the function does not match the one you have defined for your DB field. So practically you have the standardized structure in mol format, only the insertion of it in the DB throws the error (you are missing the very last step). In order to be able to help you further we would need to know your DB structure better, but that is far beyond the scope of the present support. I am sorry to say but we can not help you further with this issue as Chemaxon limits its standard support solely to the Chemaxon products and that seems to be working correctly on your side. 

 

Best regards,

Norbert

 

P.S. above we gave you some hints where would we start the investigation, but that will be on the DB/oracle side and definitely not on the cartrige side.