JChem and Postgresql Question

To whom it concerns,

I hope to carry out similarity searching using JChem. JChem will be running in a Linux environment. I will be using Postgresql database system. My question is as follows;

when I type ./jcman a dialog box appears called 'Connecting to a Database'.

What should I put in the various boxes? For example, in the first box should I put the address where the JDBC Driver is stored on my own system?

 

Kind Regards,

Miriam

 

 

Hi Miriam,

You can find info about connecting to a database in our FAQ, see question 25:

http://chemaxon.com/jchem/FAQ.html#dburl

Regards,

Tamas

Hi Tamas,

Thanks for your help.

Regards,

Miriam

Hi Tamas,

I downloade Postgresql-8.4.1 and saved it in /home/miriam/network on my machine.

I saved postgresql-8.4-701.jdbc3.jar in the directory /home/miriam/network/postgresql-8.4.1 and then I typed

jar -xvf postgresql-8.4.701.jdbc3.jar

When I fill in the details into the dialog box 'Connecting to a Database' I followed the recommendations as outlined in question 25 of the FAQ, i.e,

JDBC Driver: org.postgresql.Driver

URL of database: jdbc:postgreqsl://usr/local/pgsql/bin

etc.,

I get the following error messages:

java.lang.ClassNotFoundException: org.postgresql.Driver
        at java.net.URLClassLoader$1.run(URLClassLoader.java:221)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:209)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:324)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:294)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:269)
        at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:337)
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Class.java:187)
        at chemaxon.util.ConnectionHandler.setConnected(ConnectionHandler.java:269)
        at chemaxon.util.ConnectionHandler.connect(ConnectionHandler.java:307)
        at chemaxon.jchem.gui.Connector.go(Connector.java:64)
        at chemaxon.jchem.gui.JChemFrame.connect(JChemFrame.java:612)
        at chemaxon.jchem.JChemManager.init(JChemManager.java:89)
        at chemaxon.jchem.JChemManager.main(JChemManager.java:106)

 

Do you think  the URL of the database is correct?

Thanks,

Miriam

Hi Miriam,

Have you set your environment variables? Your CLASSPATH variable should contain the path of your postgresql.jar file (for checking type 'set' at your command prompt and look for 'CLASSPATH=...').
About setting environment variables see http://chemaxon.com/jchem/doc/admin/prepscripts.html

Your URL should rather contain a logical reference of your database than the path of the executable database server, e.g. "jdbc:postgresql://localhost/test", where 'test' is your database name (see http://jdbc.postgresql.org/documentation/80/connect.html ).
Another problem can be that there is a typo in your URL: jdbc:postgreqsl:...

And just a last remark: unzipping the jar file is not needed, Java handles the jar format.

Let me know if this helps.

Regards,
Tamas

Miriam,

Which JChem version are you working with? If you have a recent JChem version (5.2+), you shouldn't be worried about the postgresqlXXX.jar file -- the PostgreSQL jdbc driver is included in, and automatically retrieved by, JChem.

Peter

Hi Tamas and Peter,

Thank you both for your help. Peter, the version of JChem that I'm using is 5.2.5.1

Tamas,

I'll follow your advice and let you know how I get on.

 

thanks again,

Miriam

 

Hi Tamas,

The following is what I entered into the dialog box which popped up when I typed 'jcman'

JDBC Driver:org.postgresql.Driver

URL of database:jdbc:postgresql.//localhost/Mydb

Property table: JChemProperties

logon name: miriam

Password: ******

I get the following error message:

 

S!L Warning

SQL State:28000

Message:FATAL: role 'miriam' does not exist

Vendor:0.

 

I downloaded and setup the postgresql database system myself.

When I checked /.chemaxon/.jchem

the entry for connection login was empty. When I added 'miriam' to this line it didn't make a difference.

Also, in the URL section of the dialog box I typed the address of my machine instead of localhost and again it didn't make a difference.

 

Thanks,

Miriam

 

Hi Miriam,

Your settings are probably good, since you don't get a Chemaxon-generated error but an SQL error. I'm not familiar with administering postgres, but it seems that you have not created a role (or maybe a user account?) in your database. Have a look at this page: http://www.postgresql.org/docs/8.4/static/user-manag.html , it may help you setting the missing role/user.

Tamás

Hi Tamas,

I took your advice and I'm now able to make a connection to the database.

Thanks again,

Miriam

Hi Tamas,

I have created a Table using the command line. This table is called bdzstructures. I have 5 SMILES strings listed in a file called BDZ.sdf. I successfully imported this file into the table using the command jcman a bdzstructures BDZ.sdf. But, when I tried to look at a specific row in the Table  I get the following errors. I would appreciate any comments/advice that you could offer.

Thanks,

Miriam

 

[root@rusty tmp]# jcman t
    Table name                                    Version          Rows
  1 public.bdzstructures                          5020400             5
[root@rusty tmp]# jcman t bdzstructures 1
Cannot get row information:
org.postgresql.util.PSQLException: ERROR: LIMIT #,# syntax is not supported
  Hint: Use separate LIMIT and OFFSET clauses.
  Position: 29
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:252)
        at chemaxon.jchem.Command.viewRow(Command.java:920)
        at chemaxon.jchem.Command.run(Command.java:596)
        at chemaxon.jchem.Command.main(Command.java:207)
[root@rusty tmp]#

Hi,

 

Thank you for reporting this.

Strange we have not noticed this for a long time, maybe because everyone uses Instant JChem :) :

http://www.chemaxon.com/product/ijc.html

We have fixed the error.

We are very close to releasing 5.2.6, we will let you know if we could include this fix into 5.2.6 or into 5.2.7.

 

Best regards,

 

Szilard

 

Hi Szilard,

Am I right in assuming that it would be better for me to use Instant JChem? I have downloaded it now to my machine.  Could you please tell if it is possible to use InstantJChem via the command line.  I would like to be able to do a structural similarity search from within my own code and therefore I'd like to include a call to IJChem from within my own code.

Thanks,

Miriam

Hi,

Good news: the fix mentioned above will be included in 5.2.6 which is due to be released very soon.

Am I right in assuming that it would be better

for me to use Instant JChem? I have downloaded it now to my machine. 

Could you please tell if it is possible to use InstantJChem via the

command line.  I would like to be able to do a structural similarity

search from within my own code and therefore I'd like to include a call

to IJChem from within my own code.

If you want to call command-line probably not.

The virtue of Instant JChem is the GUI and easy installation (one can start working in minutes), but database operations are not supported from command-line.

 

- Command line:

Please note that calling command-line is not very effective in some cases because each time a new Java Virtual Machine (JVM) has to be started.

When using jcsearch for searching this problem is further compounded by the structure cache being loaded and then lost at each search. Therefore we do not recommend it for more than a few 10 thousand structures.

Some other ways of integration:

- Direct Java API access : the best solution if you are OK with using Java (it is a quite frienldy language). You can find some code examples in the JChem Developer's Guide:

http://www.chemaxon.com/jchem/doc/guide/

- JChem Web Services: we provide SOAP interface (language independent) for the most important parts of the JChem API.

http://www.chemaxon.com/webservices/developersGuide.html

These are probably not relevant for you, just to make the list complete:

- JChem Cartridge for Oracle : accessing JChem function trough plain SQL statements from any language. Oracle only.

http://www.chemaxon.com/product/jc_cart.html

- .NET integration:

http://www.chemaxon.com/NET_support_land.html

- We also integrate with various pipelining tools (Pipeline Pilot, KNIME, InforSense KDE).

Hi Szllard,

Thanks for all your advice. While I'm waiting for the next version of JChem I'll have a look at using the Java APIs.

all the best,

Miriam

Hi Miriam,

JChem 5.2.6 has been released.

Best Regards, Edvard

Hi Edward,

That's great news. I'll download it now. As an aside I am currently having difficulties launching msketch. The problem only started late yesterday afternoon. If the situation doesn't rectify itself I will let you know.

Thanks,

Miriam

Hi Edward,

I removed both Instant JChem and the previous version of JChem  from my machine. I rebooted my computer and msketch worked fine. I downloaded the latest version of JChem and I ran a standard experiment on my computer but I received the following error message:

Error: Could not find the main class.
Error: A JNI error has occurred, please check your installation and try again
Exception in thread "main" java.lang.NoClassDefFoundError: chemaxon/marvin/calculations/My_MolCalculator_Two
Caused by: java.lang.ClassNotFoundException: chemaxon.marvin.calculations.My_MolCalculator_Two
        at java.net.URLClassLoader$1.run(URLClassLoader.java:221)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:209)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:324)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:294)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:269)
        at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:337)

I have made any changes to my code so I assume that the error message is related to the new version of JChem. Even though JChem is currently not involved in my code.

I would appreciate any advice or help which you could give me.

Thanks,

Miriam

 

 

 

Hi Miriam,

it seems your settings vanished when you had removed old version of JChem.
Please try to add your classes and jars (e.g. that contains the My_MolCalculator_Two class) to the classpath.

Regards, Edvard

Hi Edward,

Thansk for your advice. I  added the path for My_MolCalculator_Two.class to the CLASSPATH on my .bashrc script. When I reran my experiment all the previous errors were gone but, I now get the following error message:

Exception in thread "main" java.lang.NullPointerException
        at chemaxon.marvin.calculations.My_MolCalculator_Two.main(My_MolCalculator_Two.java:71)

 

Thanks,

Miriam

Hi Miriam,

Probably you have a reference in your class at line 71 that has not initialized. Maybe there are other missing classes you have to refer to in your classpath. If you cannot solve the problem, please attach your source.

Regards,
Tamás

Hi Tamas,

The problem was in my CLASSPATH.

Thanks for all your help,

Miriam

Hello,

I have problems with JChemManager and don't have idea how to solve it. I have Windows XP, the latest Java instal and PostgreSQL 8.4.

In "Conecting to a Database" window I have:

JDBC driver: org.postgresql.Driver


URL of database: jdbc:postgresql://localhost:5432/leki


Property table: JChemProperties


Login name: postgres


Password: ******

When I click "OK" button this communicate appears:

An error occured


java.lang.ClassNotFoundException






org.postgresql.Driver 

I hope someone will be able to help me. I'm not good at computers and related things, so if someone could explain this to me in easy way, I would be very greatful...

Thanks,

Doxepine

 

PS Sorry for my English; it's not my native tongue.

Hi Irek,

You could consider Instant JChem, it is very easy to set up and start:

http://www.chemaxon.com/product/ijc.html

 

If you still want to use JChem Base with PostgreSQL, my colleagues will help with setting up the JDBC driver.

BR,

Szabolcs

Szabolcs wrote:

Hi Irek, You could consider Instant JChem, it is very easy to set up and start: http://www.chemaxon.com/product/ijc.html   If you still want to use JChem Base with PostgreSQL, my colleagues will help with setting up the JDBC driver. BR, Szabolcs

Hi Szabolcs,

Thank you so much for your replay. I've just downloaded and installed Instant JChem and tried to create a database, but it's not as easy as I would like it to be :) I have to read more informations about creating databases in this software, but also want to give JChem a try, if it's no bother. Again, thank you very much for your help! :)

 

Best Regards,

Irek

Irek,

The PostgreSQL driver was mistakenly not bundled with JChem 5.2.6. If you have JChem version 5.2.6, you most likely got hit by this problem. In order to fix it, please, download the attached jar file and drop it in the jchem/lib directory (where db2jcc.jar, ojdbc5.jar and others can be found). And that should be it.

Peter

Hi Peter,

Thank you very much for your help! Now everything works just great!

Thank you and best regards,

Irek