Use molfile without bidding - ChemAxon Forum Archive

User d8da4712c1

21-05-2013 14:25:00

Hello everyone,

I want to use an sql request with molfile without bidding like:

This is equivalent to : SELECT cd_id FROM kiet.sub_structure WHERE jc_compare(cd_smiles, 'COC1=CC=C(C(CC#N)=N1)[N+]([O-])=O','t:s') = 1;

////////////////////////////////////////////////// this is what I want to do :

Instead of (for example, didn't try if it works with bidding): SELECT cd_id FROM kiet.sub_structure WHERE jc_compare(cd_structure,:molfile,'t:s') = 1;

I want to (cause testing my queries on sqlplus before implementation on the code):

SELECT cd_id FROM kiet.sub_structure WHERE jc_compare(cd_structure, ' Mrv0541 05211312252D

14 14 0 0 0 0 999 V2000

0.0687 1.0863 0.0000 N 0 0 0 0 0 0 0 0 0 0 0 0

-0.6457 0.6738 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

-0.6457 -0.1513 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

0.0687 -0.5638 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

0.7832 -0.1513 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

0.7832 0.6738 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

-1.2291 1.2571 0.0000 O 0 0 0 0 0 0 0 0 0 0 0 0

-2.0260 1.0436 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

1.4977 1.0863 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

2.2946 0.8727 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

1.4977 -0.5638 0.0000 N 0 3 0 0 0 0 0 0 0 0 0 0

2.7071 0.1583 0.0000 N 0 0 0 0 0 0 0 0 0 0 0 0

2.3227 -0.5638 0.0000 O 0 0 0 0 0 0 0 0 0 0 0 0

1.4977 -1.3888 0.0000 O 0 5 0 0 0 0 0 0 0 0 0 0

1 2 1 0 0 0 0

1 6 2 0 0 0 0

2 3 2 0 0 0 0

3 4 1 0 0 0 0

4 5 2 0 0 0 0

5 6 1 0 0 0 0

2 7 1 0 0 0 0

7 8 1 0 0 0 0

6 9 1 0 0 0 0

9 10 1 0 0 0 0

5 11 1 0 0 0 0

10 12 3 0 0 0 0

11 13 2 0 0 0 0

11 14 1 0 0 0 0

M CHG 2 11 1 14 -1

M END','t:s') = 1;

////////////////////////////////

I'm getting this error when doing the last query:

Erreur SQL : ORA-29902:Error excuting the routine ODCIIndexStart()

ORA-20101: Cannot read molecule file.

ORA-06512: à "KIET.JCHEM_CORE_PKG", ligne 45

ORA-06512: à "KIET.JC_IDXTYPE_IM", ligne 335

29902. 00000 - "error in executing ODCIIndexStart() routine"

*Cause: The execution of ODCIIndexStart routine caused an error.

*Action: Examine the error messages produced by the indextype code and

take appropriate action.

ChemAxon aa7c50abf8

22-05-2013 08:22:57

Hello,

Assuming that the query structure is not longer than 4000 character you must check how it is specified to the database client you are working with. SDFile-s are "sensitive" to new line characters, spaces and empty lines or lines with just a couple spaces, for example. I suggest to try something like this:

select substr('adsfas', 1, 4000)from dual;

Do you get the original string back?

One trick SQL*Plus requires with SDFile-s is to mark the end of lines containing only spaces with comment sings ('--'), so as the line is preserved as is.

Peter

User d8da4712c1

22-05-2013 08:48:39

pkovacs wrote:

Hello,

Assuming that the query structure is not longer than 4000 character you must check how it is specified to the database client you are working with. SDFile-s are "sensitive" to new line characters, spaces and empty lines or lines with just a couple spaces, for example. I suggest to try something like this:

select substr('adsfas', 1, 4000)from dual;

Do you get the original string back?

One trick SQL*Plus requires with SDFile-s is to mark the end of lines containing only spaces with comment sings ('--'), so as the line is preserved as is.

Peter

Yes I get the original string:

SUBSTR('ADSFAS',1,4000)

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

adsfas

1 rows selected

//////////////////////////////////////////

I'm using sqldeveloper if that make any difference.

User d8da4712c1

22-05-2013 08:51:06

I'm thinking about putting it in a blob variable in declare section then use it, there is no "string to blob" convertion function so I think I have to pass by "string to clob to blob" or do you think it will just work with clob. thanks

ChemAxon aa7c50abf8

22-05-2013 08:52:34

SUBSTR('ADSFAS',1,4000)

Of course, I meant that you use the SDFıle instead of the garbage in there and see if you get it back correctly.

User d8da4712c1

22-05-2013 08:59:15

pkovacs wrote:

SUBSTR('ADSFAS',1,4000)

Of course, I meant that you use the SDFıle instead of the garbage in there and see if you get it back correctly.

Sorry didn't get it, can you write an example of what do you mean thanks.

ChemAxon aa7c50abf8

22-05-2013 09:03:20

select SUBSTR('<your-mol-file-comes-here>',1,4000) from dual;

User d8da4712c1

22-05-2013 09:09:13

pkovacs wrote:

select SUBSTR('<your-mol-file-comes-here>',1,4000) from dual;

Ok so here we go:

SUBSTR('ADSFAS',1,4000)

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

adsfas

1 rows selected

SUBSTR('MRV054105211312252D14140000999V20000.06871.08630.0000N000000000000-0.64570.67380.0000C000000000000-0.6457-0.15130.0000C0000000000000.0687-0.56380.0000C0000000000000.7832-0.15130.0000C0000000000000.78320.67380.0000C000000000000-1.22911.25710.0000O0

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

Mrv0541 05211312252D

14 14 0 0 0 0 999 V2000

0.0687 1.0863 0.0000 N 0 0 0 0 0 0 0 0 0 0 0 0

-0.6457 0.6738 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

-0.6457 -0.1513 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

0.0687 -0.5638 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

0.7832 -0.1513 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

0.7832 0.6738 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

-1.2291 1.2571 0.0000 O 0 0 0 0 0 0 0 0 0 0 0 0

-2.0260 1.0436 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

1.4977 1.0863 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

2.2946 0.8727 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

1.4977 -0.5638 0.0000 N 0 3 0 0 0 0 0 0 0 0 0 0

2.7071 0.1583 0.0000 N 0 0 0 0 0 0 0 0 0 0 0 0

2.3227 -0.5638 0.0000 O 0 0 0 0 0 0 0 0 0 0 0 0

1.4977 -1.3888 0.0000 O 0 5 0 0 0 0 0 0 0 0 0 0

1 2 1 0 0 0 0

1 6 2 0 0 0 0

2 3 2 0 0 0 0

3 4 1 0 0 0 0

4 5 2 0 0 0 0

5 6 1 0 0 0 0

2 7 1 0 0 0 0

7 8 1 0 0 0 0

6 9 1 0 0 0 0

9 10 1 0 0 0 0

5 11 1 0 0 0 0

10 12 3 0 0 0 0

11 13 2 0 0 0 0

11 14 1 0 0 0 0

M CHG 2 11 1 14 -1

M END

1 rows selected

ChemAxon aa7c50abf8

22-05-2013 09:14:05

This is clearly not a valid SDFile/Molfile, is it?

Peter

User d8da4712c1

22-05-2013 09:15:26

pkovacs wrote:

This is clearly not a valid SDFile/Molfile, is it?

Peter

it's copy/past from sdf exported using jchme manager

User d8da4712c1

22-05-2013 09:18:41

This is a screenshot

ChemAxon aa7c50abf8

22-05-2013 09:22:34

it's copy/past from sdf exported using jchme manager

You mean the input file to the SUBSTR SQL function, correct? I am talking about the output of the SUBSTR function. Are they the same character-by-character including spaces, new-lines and other white space characters?

Based on the screen-shot at least one line is missing...I'd say two is missing: the first one and the third one... The screen-shot shows the output of the SUBSTR function, correct?

Peter

User d8da4712c1

22-05-2013 09:27:50

As you may see between the first and second lignes there's only one break line, in the entry it was two

User d8da4712c1

22-05-2013 09:29:56

Yep it's the same as show in my earlier post ( the copy/past made that post very confusing) ,thanks

User d8da4712c1

22-05-2013 09:31:12

this is the query

ChemAxon aa7c50abf8

22-05-2013 09:38:21

Does the following structure accepted by JC_COMPARE as the query structure?

QQ1

  -ISIS-  08190516102D

  --

 10 11  1  0  0  0  0  0  0  0999 V2000

    1.8611   -2.4533    0.0000 N   0  0  0  0  0  0  0  0  0  0  0  0

    2.3481   -1.7870    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    1.8648   -1.1180    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    1.0792   -1.3709    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    1.0769   -2.1962    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -0.3473   -1.3750    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -0.3484   -2.2023    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    0.3664   -2.6152    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    0.3646   -0.9622    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    0.3621   -0.1372    0.0000 L   0  0  0  0  0  0  0  0  0  0  0  0

  5  1  1  0  0  0  0

  4  5  1  0  0  0  0

  6  7  2  0  0  0  0

  2  3  2  0  0  0  0

  7  8  1  0  0  0  0

  8  5  2  0  0  0  0

  1  2  1  0  0  0  0

  4  9  2  0  0  0  0

  9  6  1  0  0  0  0

  3  4  1  0  0  0  0

  9 10  1  0  0  0  0

 10 F    3  17  35  53

M  SUB  3   6  -2   7  -2   8  -2

M  ALS  10  3 F Cl  Br  I  

M  END

Peter

User d8da4712c1

22-05-2013 09:42:39

pkovacs wrote:

Does the following structure accepted by JC_COMPARE as the query structure?

QQ1

  -ISIS-  08190516102D

  --

 10 11  1  0  0  0  0  0  0  0999 V2000

    1.8611   -2.4533    0.0000 N   0  0  0  0  0  0  0  0  0  0  0  0

    2.3481   -1.7870    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    1.8648   -1.1180    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    1.0792   -1.3709    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    1.0769   -2.1962    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -0.3473   -1.3750    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -0.3484   -2.2023    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    0.3664   -2.6152    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    0.3646   -0.9622    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    0.3621   -0.1372    0.0000 L   0  0  0  0  0  0  0  0  0  0  0  0

  5  1  1  0  0  0  0

  4  5  1  0  0  0  0

  6  7  2  0  0  0  0

  2  3  2  0  0  0  0

  7  8  1  0  0  0  0

  8  5  2  0  0  0  0

  1  2  1  0  0  0  0

  4  9  2  0  0  0  0

  9  6  1  0  0  0  0

  3  4  1  0  0  0  0

  9 10  1  0  0  0  0

 10 F    3  17  35  53

M  SUB  3   6  -2   7  -2   8  -2

M  ALS  10  3 F Cl  Br  I  

M  END

Peter

Yes indeed it is

ChemAxon aa7c50abf8

22-05-2013 09:45:15

You either made an error when copy/pasting the output of the JChemManager export, or you have to do the '--' trick applied to the SDFile which just workd.

Peter

User d8da4712c1

22-05-2013 09:52:39

pkovacs wrote:

You either made an error when copy/pasting the output of the JChemManager export, or you have to do the '--' trick applied to the SDFile which just workd.

Peter

Thanks again Peter, no it's exactly the same one exported from JchemManager, I also did the import thing using JchemManager, and the molfile was in the same format, I'm wondering how could JchemManager import them without problems while I have to add something, this is an example of one structure from the imported file:

-IDBS- 043020131626

18 19 0 0 0 0 0 0 0 0999 V2000

3.5899 -4.2363 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

3.5887 -5.0591 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

4.3072 -5.4718 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

4.3054 -3.8239 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

5.0204 -4.2369 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

5.0169 -5.0586 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

5.8039 -5.3136 0.0000 N 0 0 0 0 0 0 0 0 0 0 0 0

6.2857 -4.6508 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

5.8003 -3.9814 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

6.0563 -3.1953 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

6.8600 -3.0202 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

7.1139 -2.2329 0.0000 N 0 0 0 0 0 0 0 0 0 0 0 0

7.9175 -2.0578 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

8.1714 -1.2749 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

2.8667 -3.8243 0.0000 O 0 0 0 0 0 0 0 0 0 0 0 0

2.1546 -4.2409 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

8.4744 -2.6705 0.0000 O 0 0 0 0 0 0 0 0 0 0 0 0

7.1101 -4.6571 0.0000 I 0 0 0 0 0 0 0 0 0 0 0 0

8 9 2 0 0 0

9 5 1 0 0 0

4 1 1 0 0 0

9 10 1 0 0 0

5 6 1 0 0 0

10 11 1 0 0 0

11 12 1 0 0 0

2 3 1 0 0 0

12 13 1 0 0 0

3 6 2 0 0 0

13 14 1 0 0 0

1 2 2 0 0 0

1 15 1 0 0 0

5 4 2 0 0 0

15 16 1 0 0 0

6 7 1 0 0 0

13 17 2 0 0 0

7 8 1 0 0 0

8 18 1 0 0 0

M END

> <subs_labo_id>

5

> <subs_laboratoire_substance_id>

49241722

> <subs_cond_id>

vrac

> <subs_national_id>

> <subs_stru_id>

> <subs_slot_lotnational_id>

> <subs_slot_lotlabo_id>

> <subs_quantite>

400

> <stru_logp_value>

2.181

> <observations>

> <methode_mesure_purete>

> <origine_substance>

synthese

> <purete1>

> <purete2>

$$$$

ChemAxon aa7c50abf8

22-05-2013 10:09:36

This one works for me:

select count(*) from nci_1k where jc_compare(structure, '

  -IDBS-

043020131626

 18 19  0  0  0  0  0  0  0  0999 V2000

    3.5899   -4.2363    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    3.5887   -5.0591    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    4.3072   -5.4718    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    4.3054   -3.8239    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    5.0204   -4.2369    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    5.0169   -5.0586    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    5.8039   -5.3136    0.0000 N   0  0  0  0  0  0  0  0  0  0  0  0

    6.2857   -4.6508    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    5.8003   -3.9814    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    6.0563   -3.1953    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    6.8600   -3.0202    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    7.1139   -2.2329    0.0000 N   0  0  0  0  0  0  0  0  0  0  0  0

    7.9175   -2.0578    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    8.1714   -1.2749    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    2.8667   -3.8243    0.0000 O   0  0  0  0  0  0  0  0  0  0  0  0

    2.1546   -4.2409    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    8.4744   -2.6705    0.0000 O   0  0  0  0  0  0  0  0  0  0  0  0

    7.1101   -4.6571    0.0000 I   0  0  0  0  0  0  0  0  0  0  0  0

  8  9  2  0     0  0

  9  5  1  0     0  0

  4  1  1  0     0  0

  9 10  1  0     0  0

  5  6  1  0     0  0

 10 11  1  0     0  0

 11 12  1  0     0  0

  2  3  1  0     0  0

 12 13  1  0     0  0

  3  6  2  0     0  0

 13 14  1  0     0  0

  1  2  2  0     0  0

  1 15  1  0     0  0

  5  4  2  0     0  0

 15 16  1  0     0  0

  6  7  1  0     0  0

 13 17  2  0     0  0

  7  8  1  0     0  0

  8 18  1  0     0  0

M  END


>  <subs_labo_id>

5


>  <subs_laboratoire_substance_id>

49241722

 

>  <subs_cond_id>

vrac

 

>  <subs_national_id>

 

>  <subs_stru_id>

 

>  <subs_slot_lotnational_id>

 

>  <subs_slot_lotlabo_id>

 

>  <subs_quantite>

400

 

>  <stru_logp_value>

2.181

 

>  <observations>

 

>  <methode_mesure_purete>

 

>  <origine_substance>

synthese

 

>  <purete1>

 

>  <purete2>

 

$$$$', 't:s') = 1;

P.

ChemAxon aa7c50abf8

22-05-2013 10:12:57

(Due to the highly incapable user interface of this forum, I suggest to communicate using attachements to avoid file format misunderstandings.)

User d8da4712c1

22-05-2013 10:18:34

Ok ,I have attached same structure from my sdf "imported file"

ChemAxon aa7c50abf8

22-05-2013 10:23:15

There are several files in the attachment. I tried the first one it works for me as a query structure.

User d8da4712c1

22-05-2013 13:03:46

pkovacs wrote:

There are several files in the attachment. I tried the first one it works for me as a query structure.

yes it work after doing some modification but not with default structure "mol without any changes in the imported file" , but I think you clarified it for me enough to help me figure out how it works, I'll be implementing it on php sql call, so I think with biding it will be bit more easier to adapt. thank you very much Peter cheers ;-)

ChemAxon aa7c50abf8

22-05-2013 13:33:05

The first structure in the file (the only one I tried), I could use it without any modification.

But I agree, binding data as SQL parameters is a more general, more robust solution than using literals and it may be better even performance-wise.

Peter