writing script to extract a molecule from ijc

User 8fbca62edf

26-12-2011 14:46:54

please, i need help, i want to extract data from a ijc local data base derby by scripting.


e.g.


 import java.sql.*
            import org.apache.derby.jdbc.EmbeddedDataSource40


            EmbeddedDataSource40 ds = new EmbeddedDataSource40();
            ds.databaseName = 'C:/Users/MANIF/Documents/IJCProjects/cancer projet/.config/molecule cancer/db'
            Connection conn = ds.getConnection()

ChemAxon a3d59b832c

27-12-2011 14:43:53

Hi,


I have moved your quesion to the Instant JChem forum where my colleagues will check and respond.


 


Best regards,


Szabolcs

ChemAxon e189db4705

27-12-2011 18:25:47

There is a script example called SimpleSDFExporter. It is exporting data from localdb to file.


Create a new sample data project, in project explorer expand Pubchem demo -> Scripts and open SimpleSDFExporter script to editor.


It's also possible to use direct connection to database, but then you will by-pass all IJC APIs. And for local DB it's impossible to run such a script while schema is connected in project explorer in IJC (as local DB doesn't support more than one connection in the same time).

ChemAxon fa971619eb

27-12-2011 18:49:28

In fact it is possible to access the local databse as well. Multiple connections are allowed, but only from the same class loader, and this is what happens when you run the script inside IJC. So a simple script like this can be used:


import groovy.sql.*
import java.sql.*
import org.apache.derby.jdbc.EmbeddedDataSource40

EmbeddedDataSource40 ds = new EmbeddedDataSource40();
ds.databaseName = '/Users/timbo/IJCProjects/ijc-project4/.config/localdb/db'
Connection conn = ds.getConnection()
println "connected"

Sql db = new Sql(conn)

int count = db.firstRow('select count(*) c from IJC_SCHEMA')[0]
println "count: $count"

conn.close()
println "finished"

However, its certainly best to be cautious when doing this. Access through the IJC API is is a better approach, though in some cases the IJC API may not support what you are wanting to do.


Tim

User 8fbca62edf

28-12-2011 13:58:45

Much thanks to all of you who have been interested to my difficulties.


But, please i would like to have more examples of queries that extract for instance the formula or the smiles code of a molecule at a given row.


e.g a query like  this: select *  from IJC_SCHEMA where IJC_SCHEMA.Formula="C20H26O6"

ChemAxon e189db4705

28-12-2011 15:19:04

Hello,


The basic question is whether you want to run this search using SQL or you want to use IJC API for this? The code will look completely different way depending on way you want to go. SQL might be easier for simple cases, but IJC API would give you more functionality.

User 8fbca62edf

28-12-2011 15:41:46

Please, i am interested to both methods.


May be, the two will help me.


thanks once again.

ChemAxon 8da0306fb7

31-12-2011 03:54:23

Hi!


You could do this in IJC with a Groovy script that checks each row for your condition, and then writes it out to an SDF if it matches.  The exampleSDFexporter script that was referenced above is a good place to see some fundamental scripting in action. 


I've used some of these peices of code, and you'll have to fill in the blanks for your own chemical terms, but you could probably shape the following code to your needs in IJC.


import com.im.df.api.support.*
import com.im.df.api.ddl.*
import com.im.df.api.util.DIFUtilities
import com.im.df.api.util.DIFUtilities.*
import javax.swing.filechooser.*
import com.im.df.api.dml.*
import com.im.commons.progress.*
import chemaxon.formats.MolExporter
import javax.swing.*
import javax.swing.SwingUtilities

def ety = dataTree.rootVertex.entity
def edp = ety.schema.dataProvider.getEntityDataProvider(ety)
def rs = ety.schema.dataProvider.getDefaultResultSet(dataTree, false, DFEnvironmentRO.DEV_NULL)
def parentVS = rs.getVertexState(dataTree.rootVertex)
def molIDs = parentVS.getIds()
def molFld = ety.fields.items.find { it.name == 'Structure' }
def formulaFld = ety.fields.items.find { it.name == 'Formula' }

// Prompt for save file location

def chooser = new JFileChooser()
if (chooser.showSaveDialog(null)==JFileChooser.APPROVE_OPTION) {
File fileName = chooser.getSelectedFile()
name = fileName.getCanonicalPath()

// Check to see if file has the correct extension
if (!name.endsWith('.sdf')){
SAVE_NAME = name + '.sdf'
} else {
SAVE_NAME = fileName.getCanonicalPath()
}

// See if file already exists
File existFile = new File(SAVE_NAME)
if (existFile.exists () ) {
def response = JOptionPane.showConfirmDialog (null, "$existFile exists \nOverwrite existing file?", "Confirm Overwrite", JOptionPane.OK_CANCEL_OPTION, JOptionPane.QUESTION_MESSAGE)
if (response == JOptionPane.CANCEL_OPTION) {
return}
}
}

def exporter = new MolExporter(SAVE_NAME, 'sdf')

// Lock the table
def lock = rs.lockable.obtainLock('exporting')

try {
def envRW = new DFEnvironmentRW() {
public DFLock getLock() {
return lock}
public DFFeedback getFeedback() {
return DFEnvironmentRO.DEV_NULL.feedback}
}

molIDs.each { id ->

//Get the molecule from the table
def molData = parentVS.getData([id], DFEnvironmentRO.DEV_NULL)
getMol = molData[id][molFld.id]
formulaMol = molData[id][formulaFld.id]
nativeMol = getMol.getNative()

//Set the conditions for the IF loop, and write to the SDF if it matches
if (formulaMol == 'C36H44N2O3') {
expMol = getMol.native.cloneMolecule()
exporter.write(expMol)
print "Molecule $id written to file \n"
}
}
} finally {
lock?.release()
exporter.flush()
exporter.close()
}

This script uses conditions that are built into the table based on the column name. If you want to use chemical terms to evaluate more advanced functions, you'll need to use an Evaluator. Below is the snippet you would put in the if loop to check the condition, and the additional imports needed.


import chemaxon.jep.Evaluator
import chemaxon.jep.ChemJEP
import chemaxon.jep.context.MolContext

Evaluator evaluator = new Evaluator()
chemJEP = evaluator.compile("pKa('acidic','1')", MolContext.class)
MolContext context = new MolContext()
context.setMolecule(nativeMol)
aresult = chemJEP.evaluate(context)

if (aresult == 'Whatever you're looking for') {
.....


Let me know how this works for you. Good luck!


Erin

User 8fbca62edf

31-12-2011 10:01:51

Thank you very much for your help. But, please the code does'nt work well as you can see the error message in the attached file.

ChemAxon 8da0306fb7

31-12-2011 11:03:39

Hi!


You need to install the script to the table you will be searching. Right click on the table itself and select 'new Script'. Then dataTree will be defined.


Cheers!


Erin

User 8fbca62edf

31-12-2011 11:57:07

Thanks a lot for your help. I have done what you said and the code is running but still send an error message


as you can see in the attached file.

ChemAxon 8da0306fb7

31-12-2011 18:28:26

That's a strange error, as the code works on this side. Let's follow up by email (I will email you)  to see if there is something different about your set up or dataTree that is preventing the code form working. 

ChemAxon 99d87cf303

03-01-2012 21:44:52

groovy.lang.MissingPropertyException: No such property: SAVE_NAME for class...

This could possibly happen if you would cancel the dialog which asks you for the target file. Is this the case? If yes then it could be tuned by adding else clause to:


if (chooser.showSaveDialog(null)==JFileChooser.APPROVE_OPTION) {
...
} else {
  return
}  

Cheers,


- Martin

ChemAxon 8da0306fb7

03-01-2012 21:51:23

Thanks for the extra tip! Yes, this would work as well.


We've got it working over email, looks like it was a data loading problem.


Cheers!


Erin

User 8fbca62edf

06-01-2012 13:49:01

Thanks for all your helps, now it seem to be an alternative method using sql to extract data.


Could someone   help me to use sql to extract data in an IJC local db.

ChemAxon 60ee1f1328

06-01-2012 14:57:08

Hi "ndom" - I think I can provide some example that can help you with what you need - that is run SQL in groovy and deal with columns "separately". Investigations I have completed thus far might need some slight adaption to what you want to do. Perhaps you might share with us exactly what functionality you are aiming to achieve ?

User 8fbca62edf

06-01-2012 15:47:12

Please, i would like to do research in a data base using sql groovy script. 


In fact, i would need to use the sql "select" compound to extract molecule and i also need to


test if a specific molecule can react with those of the data base.


please i need your help.

ChemAxon 60ee1f1328

06-01-2012 16:49:13


"In fact, i would need to use the sql "select" compound to extract molecule"


I can provide the groovy script that runs an sql select statement (with some search criterion specified) and can then browse the results set in a swing window. Lets call this "simple table browser / query builder".


"and i also need to test if a specific molecule can react with those of the data base."



This sounds more like a direct substructure search in which you search for the functional group that takes part in your reaction - this might be better achieved directly in IJC ?


If I have understood your need, then your table of interest is a list of possible reactants ?

User 8fbca62edf

09-01-2012 14:33:05

Ok, please, i need you to help to export a molecule from a local data base while using the


sql methods. something like this:


 


import groovy.sql.*


import java.sql.*


import org.apache.derby.jdbc.EmbeddedDataSource40


EmbeddedDataSource40 ds = new EmbeddedDataSource40();


ds.databaseName ='C:/Users/MANIF/Documents/IJCProjects/cancer projet/.config/molecule cancer/db'


Connection conn = ds.getConnection()


println "connected"


Sql db = new Sql(conn)


int count = db.firstRow('select count(*) c  from IJC_SCHEMA')[0]


println "count: $count"


conn.close()

ChemAxon 60ee1f1328

10-01-2012 09:39:52

Right you are, your 'Groovelet' will connect & count the rows in your table.


But you wish to export all rows ?


To what format do you wish to export ?


Presumably based on the fact that you have chosen this approach - some CSV including SMILES ?


And you want to be able to filter your export based on SQL criterion ?

User 8fbca62edf

13-01-2012 08:20:23

Hello,


the answer is:


Yes, i would like to filter my export using the sql criterion. In any form like sdf. 

ChemAxon 60ee1f1328

13-01-2012 11:13:41

Hi NDom,


Please find attached a schema level script which should meet the need you have stated.


It is prefer method not to do somthing like this, rather to use the DIF API but since you have asked, we have shown it possible anyway. It does rely upon a very handy method found here:


http://www.chemaxon.com/jchem/doc/dev/java/api/chemaxon/jchem/db/Exporter.html#setSelectStatement%28java.lang.String%29


Please try it out with any reasonable SQL statement and see how it does.


Thanks,


Daniel.

ChemAxon 60ee1f1328

13-01-2012 11:15:53

 


import chemaxon.jchem.db.Exporter
import org.apache.derby.jdbc.EmbeddedDataSource40
import chemaxon.util.ConnectionHandler
// dbutler _at_ chemaxon.com
// Jan 2012
// SQL & Export
EmbeddedDataSource40 ds = new EmbeddedDataSource40();
// This is schema level, so must edit the target database (example is derby):
ds.databaseName = 'C:\\Users\\daniel\\Documents\\IJCProjects\\RelationalFormTutorial\\.config\\RelationalFormExample\\db'
// Edit the file name as required
String FILE_NAME = "c:\\Temp\\yours.sdf"
// Edit the SQL statement as required
String sql = "SELECT STRUCTURES.CD_STRUCTURE, STRUCTURES.CD_ID FROM STRUCTURES WHERE cd_id < 10"
def exporter = new Exporter()
def os = new FileOutputStream (FILE_NAME)
ConnectionHandler ch = new ConnectionHandler(ds.getConnection());
exporter.setConnectionHandler (ch)
exporter.setFormat (1) // SDF
try {
exporter.setOutputStream(os)
println 'What a handy method in the Exporter class!'
exporter.setSelectStatement(sql)
exporter.writeAll()
} finally {
os.close()
}

User 8fbca62edf

17-01-2012 16:07:54

Thanks please, for this help and support you bring to me. now i would like to calculate the energy of some molecules with IJC of chemaxon. How could i  proceed  ?

ChemAxon 60ee1f1328

17-01-2012 17:28:53

I suspect such an energy calculation would require a license?


I would suggest you contact http://www.chemaxon.com/contact-us/ to discuss your exact needs.

ChemAxon 60ee1f1328

17-01-2012 17:51:12

Suggested : Geometry Plugin group

User 8fbca62edf

18-01-2012 09:36:58

Good Morning, i also give thanks for your answer to my preoccupation.


But, i have some worried about this code which did not work:


'SELECT Molecules.CD_STRUCTURE , Molecules.CD_ID, Molecules.CD_AtomCount FROM Molecules  WHERE Atom_count < 50'


while this one do well:


SELECT Molecules.CD_STRUCTURE , Molecules.CD_ID, Molecules.CD_AtomCount FROM Molecules  WHERE Atom_count < 50


'SELECT Molecules.CD_STRUCTURE , Molecules.CD_ID  FROM Molecules  WHERE cd_id < 5'

User 8fbca62edf

18-01-2012 09:42:07








Sorry, this is  what i wanted to say:


I have some worried about this code which did not work:


'SELECT Molecules.CD_STRUCTURE , Molecules.CD_ID, Molecules.CD_AtomCount FROM Molecules  WHERE Atom_count < 50'


while this one do well:


'SELECT Molecules.CD_STRUCTURE , Molecules.CD_ID  FROM Molecules  WHERE cd_id < 5'


ChemAxon 60ee1f1328

18-01-2012 17:25:50

Hi NDOM,


What error message do you see ?


My guess is you have referenced a column "cd_atomcount" which does not exist ?


If you added the chemical term "atom count" the default column name is "atom_count"


You should confirm your SQL statements run without error prior to embed them in the script.


Perhaps SQLdeveloper is a simple tool for this purpose.


if you only have sqlplus try a DESC molecules to see what columns you can use in your argument list.


Thanks,


Daniel.


 

User 8fbca62edf

27-01-2012 10:43:06

Hi Daniel,


I have meet a serious problem with the sql script in IJC 5.7.0 version


that is i can not export molecules which reference cd_id > 9.


in fact, this following code  dont export anything:


String sql = 'SELECT Molecules.CD_STRUCTURE , Molecules.CD_ID FROM Molecules  WHERE Molecules.CD_ID > 10 '
def exporter = new Exporter()
def os = new FileOutputStream (FILE_NAME)
ConnectionHandler ch = new ConnectionHandler(ds.getConnection());
exporter.setConnectionHandler (ch)
exporter.setFormat (1) // SDF
try {
exporter.setOutputStream(os)
println "What a handy method in the Exporter class!"
exporter.setSelectStatement(sql)
exporter.writeAll()
} finally {
os.close()
}


while this following  code do export molecules:


String sql = 'SELECT Molecules.CD_STRUCTURE , Molecules.CD_ID FROM Molecules  WHERE Molecules.CD_ID < 10 '
def exporter = new Exporter()
def os = new FileOutputStream (FILE_NAME)
ConnectionHandler ch = new ConnectionHandler(ds.getConnection());
exporter.setConnectionHandler (ch)
exporter.setFormat (1) // SDF
try {
exporter.setOutputStream(os)
println "What a handy method in the Exporter class!"
exporter.setSelectStatement(sql)
exporter.writeAll()
} finally {
os.close()
}


please, what can be the problem?

ChemAxon 60ee1f1328

29-01-2012 17:31:45

Please check the data in the table directly.


You can run the SQL:


select count (*) from molecules


and find out how many records you have in that table?


if you have more that 10 records in your table then we will need to look closely at your statement that there is an issue (cd_id should not be -ve).

User 8fbca62edf

31-01-2012 07:20:42

please thank you for your answer but i dont understand


what you are saying in the last sentence of your message. 

User 8fbca62edf

31-01-2012 07:53:00

Please, i would also like to know how to use the IJC script command:


jc_compare() 


in other to compare a substructure with different superstructure in a local data base. 

User 8fbca62edf

31-01-2012 08:09:15

Please, i had also an error message with the "jc_evaluate" syntax.


i need your help.

ChemAxon 60ee1f1328

31-01-2012 08:20:09

What I saying is please check the data in your table to be sure the records with cd_id greater than 10 actually exist.


As I mention above, you can confirm this independently of your script.


As for JC_COMPARE it is a cartridge operator for use in SQL - please read the cartridge documentation.


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

ChemAxon 60ee1f1328

31-01-2012 10:55:41

From your screenshot, it looks like your trying to run an SQL statement directly in the groovy console as if it were TOAD or somthing?


This requires a little more client side code to achieve (as the example I posted above shows).


Honestly, I would suggest you take a step back and look at the product over view and determine how things fit together and what technology is required (Oracle) in order to run the cartridge for example.


http://www.chemaxon.com/products/

ChemAxon 8da0306fb7

07-02-2012 23:28:38

Hi Ndom,


It sounds like you could use some help with some fundamental coding.
There are several excellent resources available for free on the web.
Here are a few that might help you out:



Getting Started with Groovy


Derby SQL primers


IJC Scripting Documentation




We are always happy to help with guidance, errors, and scripts. However, we can't do extended support at the script level without moving into a consultancy setting. If you're interested in this option, please email me and we can discuss this. I would also see if there is anyone within your business with scripting or SQL experience. Fortunately these kinds of scripts don't require in-depth knowledge.


Best of luck!


Erin