PubChem Compound Database into Oracle

Hello to everybody,

i am searching for a way to import the pubchem compound database into my oracle 11g db. I found many post that it worked with jchem but the posts are very old and i didn't found any tutorial how i can do this with an newer version of jchem.

Has anyone an solution for that?

Wit best

Hi Jochan,

Thank you for your inquiry.

Could you tell us which platform of JChem are you planning to use? Our API, command line interface or GUI applications (JChem Manager, Instant JChem, JChem for Excel, JChem Web Services, JChem for Sharepoint)?

You can find information about our products here: http://www.chemaxon.com/products/

First of all you have to decide whether you want to use our JChem Cartridge which is specialized to Oracle database, or Oracle as JChem database without Cartridge.

See JChem Cartridge documentations here: http://www.chemaxon.com/products/jchem-cartridge/

See JChem Base documentation here: http://www.chemaxon.com/products/jchem-base/

We are waiting for your further questions.

Kind regards,

Krisztina

 

 

 

Hello,

ok i will need your help for answering these questions. 

I downloaded the GUI applications.

Now i want only to import the complete pubchem database into my oracle schema which i created. Thats my task.

I have read in an old thread that JChem can do this. But there stand not the specific application. 

My question is now which program can solve my problem or is jchem not suitable for this task?

I hope this will answer your question a little bit

With best

Hi Jochan,

You can use GUI application JChem Manager: http://www.chemaxon.com/jchem/doc/admin/

Much information about Connecting to databases, Creating Structure Tables, and Importing Structure Files can be read in these sections or can be reached from there.

Please ask if you have further questions.

Best regards,

Krisztina

 

One more comment. The huge files of PubChem should be imported in batch mode of our command line tool 'jcman' or by programming using our API.

Usage of jcman is described here http://www.chemaxon.com/jchem/doc/admin/ as well. Additionally you can find information about how to run batch files: http://www.chemaxon.com/jchem/doc/admin/prepscripts.html

Java code examples can be seen here:  https://www.chemaxon.com/jchem/examples/java/html/index.html

See  JChem API: https://www.chemaxon.com/jchem/doc/dev/java/api/index.html

Best regards,

Krisztina

Thank you very much. I tried it with the gui and i see that is isn't really clever.

With the command line tool it works better and i can run this in a thread on my server.

But i have another question. This will import only the structures or?

I have an old import from pubchem into mysql and there are tables like:

db1_moldata where:

mol_id           int(11) PK

pubchem_compound_cid int(11)

pubchem_compound_canonicalized int(11)

pubchem_cactvs_complexity int(11)

pubchem_cactvs_hbond_acceptor int(11)

pubchem_cactvs_hbond_donor int(11)

pubchem_cactvs_rotatable_bond int(11)

pubchem_cactvs_subskeys varchar(255)

pubchem_iupac_openeye_name double

pubchem_iupac_cas_name text

inside. This are the fields in need to search for in my new application.

Did you know anything about that? 

The Tool i used to was moldb5.

Hi,

 

With JChem Manager, you can import structures and associated data fields as well.

Please see the documentation: http://www.chemaxon.com/jchem/doc/admin/ :

Usage of command line program:

        jcman <command> [options]


...



Commands:

...

a <table> <file>           import (add molecules) from a file into a table


...



...
Options for import:

   --connect <connections> assign custom table fields to SDFile tags

                           Note: If not given, only those SDFile tags will be imported

                           which have identical field names in the structure table

                           Example: --connect "dbfield1=sdfield1;dbfield2=sdfield2"

...


Examples:


 ...



$ jcman a str3d str3d.sdf.gz --connect "MOLNAME=NAME;PH=PH_VAL"

 

Best regards,

Szabolcs

 

Hi~

Now I want to upload the pubChem compound database to my database...

I could get the data like xml format or other.. But I couldn't find any table information...

Could I ask where did you get the tables information of the PubChem?

 

 

Hi Ahn,

 

I am not sure whether I understand the problem correctly; if not please correct me.

 

You want to import structures from pubChem to your own local database. Is that correct?

As I understand you have the pubChem molecules in files (they come usually as .sdf files). If it is correct then all you need to do is to import these files into your database. For this I would recommend you using the JChem Manager tool which has a graphical interface too. You can find a detailed description here: https://www.chemaxon.com/jchem/doc/admin/

 

I am not sure what table information do you need. Can you describe step-by-step what do you want to do?

 

Best regards,

Norbert

Dear Norbert

Thank you for your reply. 

So actually I have seen the JChem Manager tool.

But even though I could get all the data from pubChem, but I 

couldn't find any table schema information, so I couldn't load data...

So do you have any idea about that? 

 

Best wishes,

Ahn.

Hi Ahn,

 

I am sorry I still don't understand your problem exactly.

 

Pubchem is a public database, where you can download the molecules from in form of structure files (usually .sdf). It has nothing to do with Chemaxon.

You can - of course - import these molecules using JChem Manager. I suppose this is what you have done, and you have a DB with that molecules. Is it correct? This is what you call Pubchem?

 

If you want to find schemas, data tables and the other Db related details for the DB created with JChem Manager, then you might want to use a regular SQL editor (SQLdeveloper, squirrel-sql, etc). JChem Manager is a manager with highly reduced DB functionality focusing on managing the chemical databases, it is not capable (or not by default) to create schemas, to switch between them, manage indices etc. 

All the connection parameters are listed here: https://www.chemaxon.com/jchem/doc/admin/#connect including the schema too which is usually at the end of the URL in format of jdbc:<RDBMS><DBserver location>/<schema>

Using them you can connect to the database with any other SQL editor program and manage your tables. 

 

JChem Manager is able to manage tables created with Chemaxon tools only. You can list these tables using "jcman -t" command.

 

I hope I could help you!

 

Best regards,

Norbert