JChem Base and relational database - chemolibrary

I would like to administer an in site chemolibrary with JChem.


So far, we use IsisBase as a management tool, together with an Oracle export and a Webdb interface.


I would like to implement an interface entirely based on JChem, but we have not only structural information, but also biological tests, inventory etc....


I tried the ChemicalInventory software with no success. Furthermore, it has both too much and too less functionality for what I want.


In short, I would like to have an interface similar to the one already in the examples/jsp, but where I could insert/modify structures with information in several tables in a relational database: structures, biological tests, location etc...


Is it in anyway possible? Sorry if it is a candid question, I am to no extent a professional programmer...


Architecture:

RHEL WS3.0


Apache/2.0.55 (Unix) mod_ssl/2.0.55 OpenSSL/0.9.7a PHP/5.0.5 mod_jk/1.2.15


Apache Tomcat/5.5.12


MySQL - 5.0.18-standard


mysql-connector-java-3.1.11


JChem 3.1.5

Thank you in advance,


Nicolas


---


Nicolas Saettel, Ph.D., Assistant Professor


http://www.cermn.unicaen.fr

Hi,








We have a product under development that should be suitable for your needs. It provides the ability to easily create and manage databases for compounds and their associated data (e.g. assay and inventory data). You will be able to enter or import new data, run queries (including structure searches) and view the results of your queries.


We're expecting the initial version to be ready around the middle of this year, but it is probably more realistic to think of the end of the year for all the functionality you would require to be ready. More information will be available at the User Group Meeting in June.





Best regards,





Szilard

That sounds really great, I am eager to test this!


In the meantime (we would really like to migrate now), I was thinking I would do the following:

- import our sdf file with all the records together in separate tables with new columns corresponding to the data we need


- delete the JChem-generated cd_* values in the other tables except one structure table used as the main query/insert/modify, keeping the relevant information as the previously created columns (batch, biological tests etc...) and using cd_id as a foreign key


- in addition to the jsp interface of query, insert, etc... create new php/mysql forms that could insert data in the tables - the only thing I am not sure of would be how to retreive the cd_id from the structure table, together with a marvinview sketch

Could you tell me if it is unreasonable/dangerous, for the sake of further using the upcoming product?


Thank you,


Nicolas

Hi,





If you plan to delete columns from a JChem table (for keeping only the data), you should take care to remove all references to this table from the property table (default name is "JChemProperties").





To manage the inserts / updates I recommend using our API directly from Java. With UpdateHandler you can insert an individual structure into a JChem table:





http://www.chemaxon.com/jchem/doc/api/chemaxon/jchem/db/UpdateHandler.html





If you insert the related data at the same time time into the related table(s) in the same Java code (using JDBC) then you have achieved your goal. (UpdateHandler can give back the cd_id of the inserted structure)





Although you may need some basic Java and JDBC knowledge for this (and JSP for a web application), this is the most effective and therefore the recommended solution.





Please see the following live JSP example:


http://www.chemaxon.com/jchem/examples/jsp1_x/index.jsp





The source of this example is available in the jchem package.


It can help the learning curve and can be modified for individual needs.





If are not willing to wonder into the Java / JDBC / JSP territory (although these technologies are pretty easy to use), you can still use our JChem Cartridge for Oracle.


The cartridge provides access to our tools via standard SQL statements in Oracle, so you can work in any language that provides database access (including PHP).


The possible drawbacks are:


- you need an Oracle database for this


- only a selected set of things are accessible this way


- the performance is somewhat slower than with direct Java access





For more information about the cartridge please see:





http://www.chemaxon.com/jchem/doc/guide/cartridge/index.html width="90%" cellspacing="0" cellpadding="3" border="0" align="center"> Quote:


the only thing I am not sure of would be how to retrieve the cd_id from the structure table, together with a marvinview sketch I'm not sure what you mean.


If you mean performing substructure search, it is also available in our API (class JChemSearch), or from the cartridge.


Once you have the cd_id, you should read the message source from the cd_structure column, and pass it on to the Marvin applet as a parameter in the generated page.


For information on how to include an applet in your web page please see these examples:





http://www.chemaxon.com/marvin/doc/dev/index.html#examples





Best regards,





Szilard

Thank you so much for all the wonderful advice and pointers, although it may take some work I think I am going to try to go knee-deep into java / jdbc / jsp... maybe with the help of what Dann Vestergaard & Claus Stie Kallesøe did in their ChemicalInventory package...