Create JChemProperties without using JCMAN?

Hi,

I wanted to know if there is any method in the api which can be used to create the Jchemproperties table within a schema. The create_table method cannot be used since there is no prop table.

I wish to have JChem cartridge as the backend in an application, so i do not want to use jcman. Let me know if there is an alternative.

Thanks,
Nisarg 

Hi Nisarg,

The best way to avoid using jcman (and JChem Base tools in general) with JChem Cartridge is to use regular structure tables. You can manage/update regular structure table with regular SQL statements such as CREATE TABLE, CREATE INDEX, ALTER INDEX, INSERT, UPDATE, DELETE, etc. CREATE INDEX ... indextype is jchem.jc_idxtype will create the necessary metadata for you -- including the JChemProperties table.

Let me know if you need further help on this.

Peter

Hi Peter,

Thanks for the reply. I have ran into another problem.

I created a new schema 'Trial' and am calling - jchem_core_pkg.use_password('trail','Manager1');

I get the following message: ----------------

Error starting at line 1 in command:

call jchem_core_pkg.use_password('trail','Manager1')

Error report:

SQL Error: ORA-29532: Java call terminated by uncaught Java exception: java.lang.Exception: Problem connecting to JChemServer: rmi://localhost:1099: the Permission (java.net.SocketPermission localhost resolve) has not been granted to TRIAL. The PL/SQL to grant this is dbms_java.grant_permission( 'TRIAL', 'SYS:java.net.SocketPermission', 'localhost', 'resolve' )

ORA-06512: at "PKI.JCHEM_CORE_PKG", line 66

ORA-06512: at "PKI.JCHEM_CORE_PKG", line 66

29532. 00000 -  "Java call terminated by uncaught Java exception: %s"

*Cause:    A Java exception or error was signaled and could not be

           resolved by the Java code.

*Action:   Modify Java code, if this behavior is not intended.

-----------

Also calling the dbms_java.grant_permission method from SYS has no effect. 

- Nisarg

Hi Nisarg,

What if you execute

jchem_core_pkg.use_password('trial','Manager1');

instead of

jchem_core_pkg.use_password('trail','Manager1');

. Does it make any difference?

The purpose of the two parameter version of use_password is to execute JChem Cartridge code with the privileges of a user different from the current user. Are you connected to Oracle as TRIAL and trying to set the privileges for TRAIL? Wouldn't jchem_core_pkg.use_password('Manager1') (i.e setting the password for the current user) be enough for your purpose?

Peter

Hi Peter,

I rebooted by machine and restarted the jchem server.
This time i called dbms_java.grant_permission( 'TRIAL', 'SYS:java.net.SocketPermission', '127.0.0.1:1099', 'connect,resolve' );
and then   jchem_core_pkg.use_password('Manager1')

It solved the problem. 

After that i created a table as you said using CREATE TABLE statement.

And then :
create index jc_idx on myjctable(cd_structure) indextype is jchem.jc_idxtype;

It created all the required prop tables like magic.
Can you provide some explaination for that?

Thanks for your help. 

- Nisarg 

Hi Nisarg,

I am not entirely sure of what you would like me to give an explanation about... If you suspect a bug somewhere (either in use_password or in CREATE INDEX...), I'd need a more complete description of the steps you were executing. I was, so to say, just shooting in the dark to help you move past the problems you ran into.

Thanks

Peter

Hi Peter,

I was asking about the part where i just create an index and all prop tables are created. I wanted to understand that process.

Thanks,
Nisarg

When i use jcman i see these indexes being created: <table name>_CX, <table name>_FX .. etc, also a table named <table name>_UL is created.

but creating a table using SQL and then creating an index of type jc_idxtype on the table doesnt create these indexes and the table.

Should i create them manually?

what effect will it have on performance if i do not have these indexes and the table? 

The same objects are created when you create a JChem index on a regular structure table (create with CREATE TABLE) for the index. Only the pattern is <index_name>_JCX_UL instead of <table name>_UL, etc.

Peter

I was asking about the part where i just create an index and all prop tables are created.

An index table is created (along with a jchemproperties table, if not yet available). (The index table is currently called <index-name>_jcx, but this name pattern is not "public interface", it may change in the future.) Index tables are structurally very similar to the JCB tables created by JCMAN -- including the supporting database objects (indices, sequences) and metadata, things which you normally shouldn't be concerned with.

It may be useful for you to go through the Technical Presentation about JChem Cartridge. Slide #20, in particular, gives a visual overview about the two structure table types, followed on slide #21 with a broad (not exhaustive) comparison.

Peter

Thanks Peter.

Now there is no entry for myjctable in the jchemproperties table and hence the use of jc_insert is failing:

declare
    a cd_id_array;
begin
    a := jchem_table_pkg.jc_insert('Brc1ccccc1','trial.myjctable','trial.jchemproperties');
end; 

I got the following error report:

Error report:

ORA-29532: Java call terminated by uncaught Java exception: java.rmi.ServerException: RemoteException occurred in server thread; nested exception is: 

java.rmi.RemoteException: java.sql.SQLException: Table 'TRIAL.MYJCTABLE' does not exist

ORA-06512: at "PKI.JCHEM_TABLE_PKG", line 30

ORA-06512: at "PKI.JCHEM_TABLE_PKG", line 20

ORA-06512: at line 4

29532. 00000 -  "Java call terminated by uncaught Java exception: %s"

*Cause:    A Java exception or error was signaled and could not be

           resolved by the Java code.

*Action:   Modify Java code, if this behavior is not intended.

Is there a way by which i can use jc_insert?

 

What about this:

insert into trial.myjctable values('Brc1ccccc1')

?

Peter

The error for this statement : 
insert into myjctable(cd_structure) values('Brc1ccccc1');

Error starting at line 9 in command:

insert into myjctable(cd_structure) values('Brc1ccccc1')

Error report:

SQL Error: ORA-01465: invalid hex number

01465. 00000 -  "invalid hex number"

*Cause:    

*Action: 

How did you create this table?

As you had mentioned before, i created a structure table using normal sql:

CREATE TABLE myjctable(
cd_id NUMBER(10,0)  NOT NULL PRIMARY KEY,
cd_structure BLOB NOT NULL,
cd_smiles VARCHAR2(4000),
cd_formula VARCHAR2(100),
cd_sortable_formula VARCHAR2(500),
cd_molweight FLOAT,
cd_hash NUMBER(10,0) NOT NULL,
cd_flags VARCHAR2(20),
cd_timestamp DATE NOT NULL,
cd_pre_calculated NUMBER(1,0) DEFAULT 0 NOT NULL,
cd_fp1 NUMBER(10,0) NOT NULL,
cd_fp2 NUMBER(10,0) NOT NULL,
cd_fp3 NUMBER(10,0) NOT NULL,
cd_fp4 NUMBER(10,0) NOT NULL,
cd_fp5 NUMBER(10,0) NOT NULL,
cd_fp6 NUMBER(10,0) NOT NULL,
cd_fp7 NUMBER(10,0) NOT NULL,
cd_fp8 NUMBER(10,0) NOT NULL
) 

after that i created an index:

create index jc_idx on myjctable(cd_structure) indextype is pki.jc_idxtype;
(pki is the default jchem schema) 

Now i am trying to insert to myjctable and getting those errors.

Thanks,
Nisarg 

Nisarg,

What about starting with a simple example like this:

create table wombat (

  id number primary key,

  structure varchar2(4000)

  );

insert into wombat (id, structure) values(1, 'Brc1ccccc1');

create index jcxwombat on wombat(structure) indextype is jchem.jc_idxtype;

select id from wombat where jc_compare(structure, 'c1ccccc1', 't:s') = 1;

?

Peter

 

Hi Peter,

Yes I can do this.

What i really wanted to do is :
1. create a jchem structure table without using jcman 
2. insert and search structures using the jchem API.

Now i have a few questions:

1. the table wombat in your example will not have an entry in jchemproperties table, hence jc_insert cannot be used on the table. Right?

2. jcman creates a table with minimum 18 columns, and with indexes on a few columns. Your example here has 2 columns and 1 index. What will be the performance comparison?

Thanks,
Nisarg

Nisarg,

What i really wanted to do is : 
1. create a jchem structure table without using jcman 
2. insert and search structures using the jchem API.

You can do 1. with JChem API as well without using jcman: UpdateHandler.html#createStructureTable() . (You can also add the jchemproperties table through JChem API without using jcman:

if (!DatabaseProperties.propertyTableExists(connectionHandler)) {
    DatabaseProperties.createPropertyTable(connectionHandler);
} 

)

1. the table wombat in your example will not have an entry in jchemproperties table, hence jc_insert cannot be used on the table. Right?

It will have an entry in the jchemproperties table. (Did you actually try the simple example I have just suggested?)

2. jcman creates a table with minimum 18 columns, and with indexes on a few columns. Your example here has 2 columns and 1 index. What will be the performance comparison?

The index table will have a very similar structure (one and a half a dozen columns etc.)

Again, I suggest to try the simple example I proposed in an empty schema and the answers to these questions will be more obvious by looking at the resulting database objects. (Along with the slide I recommended to look at in the technical presentation.)

Peter

Peter,

Methods you suggested are using the Java API which we do not want to use.

I did exactly what you had asked earlier:

call jchem_core_pkg.use_password('Manager1');
create table wombat (
  id number primary key,
  structure varchar2(4000)
  );

insert into wombat (id, structure) values(1, 'Brc1ccccc1');
create index jcxwombat on wombat(structure) indextype is pki.jc_idxtype;
select id from wombat where jc_compare(structure, 'c1ccccc1', 't:s') = 1; 

and after this when i try to use jc_insert:

declare
    a cd_id_array;
begin
    a := jchem_table_pkg.jc_insert('c1ccccc1','wombat','jchemproperties');
end; 

I get an error:

Error report:

ORA-29532: Java call terminated by uncaught Java exception: java.rmi.ServerException: RemoteException occurred in server thread; nested exception is: 

java.rmi.RemoteException: java.sql.SQLException: Table 'JCHEM.WOMBAT' does not exist

ORA-06512: at "PKI.JCHEM_TABLE_PKG", line 30

ORA-06512: at "PKI.JCHEM_TABLE_PKG", line 20

ORA-06512: at line 4

29532. 00000 -  "Java call terminated by uncaught Java exception: %s"

*Cause:    A Java exception or error was signaled and could not be 

           resolved by the Java code.

 

*Action:   Modify Java code, if this behavior is not intended.

Attached is the screenshot for prop table.

Nisarg,

If you want to add a benzene to your searchable structure set, why don't you simply call

INSERT INTO wombat (id, structure) values(1, 'c1ccccc1');
COMMIT;

? JChem Cartridge search operators (such as JC_COMPARE) will be able to retrieve both the originally indexed bromo-benzene and the newly inserted benzene.

Peter

Hi Peter,

Thanks for your help.

I had a question regarding Jc_compare.

can you give me an example of jc_compare where the query_structure is a SQL query?

Thanks,
Nisarg 

Hi Nisarg,

Do you mean something like this:

select id from wombat

  where jc_compare(

    structure,

    (select query_structure from my_query_structures where myid = 'ajvar'),

    't:s') = 1;

?

Peter

 

Peter,

I am trying something similar:

select primary_key from chemical_structures where pki.jcf.compare(cdxml_index,(select cdxml from chemical_query_cells where key=4),'t:s')=1; 

I get the following error:

Error report:

SQL Error: ORA-20101: Invalid target: Molecule cannot be imported

ORA-06512: at "PKI.JCHEM_CORE_PKG", line 36

ORA-06512: at "PKI.JCF", line 301

cdxml_index is of data type clob and the format is cdxml.

Thanks,
Nisarg 

Nisarg,

First off: are you sure you want to use function (jcf.compare()) instead of operator (jc_compare)? I'd never use jcf.compare() except for assignments in PL/SQL programs. The operator will be almost always much faster.

The error message says that one of the targets cannot be imported. I'd try to identify which target shows the problem -- for example by selectively executing the search on the table.

Peter