db_example with postgresql

Your db_example (included int the JChem/examples) looks very useful to me.  I am trying to enable it on my tomcat server.  I am unable to get the server to connect. If I try to connect the database through my tomcat server, I get the message:  The connection attempt failed.

If I try to connect via my apache server, I get the following stack trace:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "WHERE"
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:336)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:235)
	at chemaxon.jchem.db.DatabaseProperties.getStructureTableNames(DatabaseProperties.java:706)
	at chemaxon.jchem.db.DatabaseProperties.getStructureTableNames(DatabaseProperties.java:676)
	at org.apache.jsp.db_005fsearch.seltable_jsp._jspService(seltable_jsp.java:299)
	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:377)
	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
	at java.lang.Thread.run(Thread.java:636)

The java program connects and gives results.

Am I buying a lot of trouble by using postgresql at this point?  How difficult is my task going to be?  Should I just
go to mysql?  

CDD

Hi,

- Please specify your exact JChem version - we need this for all support requests (see text left of the text area when posting)

- In this case your PostgreSQL version may also be relevant, please let us know about it.

- Make sure you only use the same JDBC driver in Tomcat that was supplied with JChem.

We can examine the issue after the above information is present.

 

  When compiled, this program works (have to add 
the path for the postgesql driver, although it is in the jchem /lib).

I assume you mean CLASSPATH, not PATH.

If the jchem.jar is present, there is no need to enumerate other jars in the classpath (provided they can be found at the same relative position).

Best regards,

Szilard

 

- Please specify your exact JChem version - we need this for all support requests (see text left of the text area when posting)

version.properties says - v5.5.0.1

- In this case your PostgreSQL version may also be relevant, please let us know about it.

psql is version 8.4.8

I copied all the JChem/lib files to /usr/share/tomcat6/lib (per the instructions).  I also copied

postgresql-8.3-603.jdbc3.jar from the jdbc subdirectory of JChem/lib.

Hi,

Most likely the property table name is not specified properly, e.g. an empty String can produce the same trace.

You should specify the same property table you have used in JChemManager to connect.

The default is "JChemProperties".

Please see step 7 :

http://www.chemaxon.com/jchem/examples/db_search/setup.html

Please let us know if this helps.

Best regards,

Szilard

Thanks for the help.  I can connect to a database using jchem manager.  I create a new database.  I open it using jchem manager with the property table name 'example'.  That appears to work, and indeed if I open the database there are now two tables:  example and examples_cr.  That is well.  I then try to 'create a table' - the dialog box appears, and I give it the name 'structure' and accept all of the defaults.  This does not seem to work.  If I then click modify it says 'there are no jchem tables in the database'. 

I appreciate all your help.  Sorry if I am being dense.

cdd

 

 I then try to 'create a table' - the dialog box appears, and I give it the name 'structure' and accept all of the defaults.  This does not seem to work.

What happens ? Any (error) message  ?

No error (or any other) message.  It seems to think it completed its task successfully.

Cannot reproduce : the same thing, same versions works great for us.

-Please let us knlow your Java version by pasting the output of:

java -version

- Please also see the same in JChemManager : Help -> About

- Please paste here your JChemManager connection parameters you see when starting the GUI (driver, url, property table, user)

 I open it using jchem manager with the property table name 'example'. 

This is fine, but unless you want to use several property tables for some special reason, I suggest to use the default "JChemProperties" - this more explicit name can prevent some confusion later on.  

rwharper@RWH-netbook:~$ java -version
java version "1.6.0_20"
OpenJDK Runtime Environment (IcedTea6 1.9.9) (6b20-1.9.9-0ubuntu1~10.04.2)
OpenJDK Client VM (build 19.0-b09, mixed mode, sharing)

JChem manager: 5.5.1.0 (I just upgraded to the most recent version - did not help)

os: i386 Linux 2.6.32-33-generic

Ubuntu 10.045

JVM:  Sun Microsystems Inc.  1.6.0_20

Driver:  org.postgresql.Driver

url: jdbc:postgresql://localhost/jchem_example  (db is named jchem_example)

property table:  example

username, pass

localhost:8000/jchem/jchemexample/index.jsp - gives login page appropriately filled in logs me in when I click login - shows a form with selections for structure table; and form - neither of which has any options available (which is probably correct since no tables have been created).

Note that localhost is my apache server;  localhost:8000 is the tomcat server.  Subsituting localhost8000 in the JChem manager url leads to a failure to connect.

I really appreciate your effort.

cdd (AKA Dick)

 

rwharper@RWH-netbook:~$ java -version
java version "1.6.0_20"
OpenJDK Runtime Environment (IcedTea6 1.9.9) (6b20-1.9.9-0ubuntu1~10.04.2)
OpenJDK Client VM (build 19.0-b09, mixed mode, sharing)

Using a non-supported Java is often a cause of strange behavior and problems.

Especially on Linux sytem, which often have such a non-standard Java by default. 

Please obtain the latest JRE or SDK from here, set you PATH that the new Java would be the default:

http://www.oracle.com/technetwork/java/javase/downloads/index.html

To tell the truth he 

JVM:  Sun Microsystems Inc.  1.6.0_20

is a bit confusing -  did you use the "Linux installer with Java" installation package ?

(On one hand it's Sun, on the other hand we state that we include the _18 version ...)

Anyhow, you may want your "java" command to point to a standard Java implementation, it can save you a lot of problems.

A java -version outtput should look similar to this :

$ java -version


java version "1.6.0_25"


Java(TM) SE Runtime Environment (build 1.6.0_25-b06)


Java HotSpot(TM) 64-Bit Server VM (build 20.0-b11, mixed mode)

I hope this helps with the table creation dialog.

Best,

Szilard

Thanks, Szilard!  I appreciate all your effort.  All I had to do was remove openjdk.  I presume there is help in forms.html, etc that will help me muddle through.

 

Best regards,

Dick

Hi Szilard,

I hope I can bother you with a few more questions.  When I load setup.jsp, change the values and 'submit', I get the message:

Error:

/usr/share/tomcat6/.chemaxon/.jchemsite (No such file or directory)

How can I save the new values?

Dick

Do you get a stack trace ?

There might be a button titled "View stack trace".

If you can obtain the trace please paste it here.

In general you should make sure that the user that is running the Tomcat process

- has a user home

- has a .chemaxon directory in the user home

- has write access to this directory (seen in the exception)

In some cases it's simpler to use a regular user than a "system" user.

the same also applies for deploying your license file, it should reside under <user_home>/.chemaxon as well.

See #2 here:

http://www.chemaxon.com/marvin/help/licensedoc/installToServer.html

Hi Szilard,

Apparently, I do not know who is the process owner (perhaps it is tomcat?).

Dick

Stack trace:

JChem version : 5.5.0.1
JVM : Sun Microsystems Inc. 1.6.0_26
OS  : i386 Linux 2.6.32-33-generic


Stack trace:
------------


java.io.FileNotFoundException: /usr/share/tomcat6/.chemaxon/.jchemsite (No such file or directory)
	at java.io.FileOutputStream.open(Native Method)
	at java.io.FileOutputStream.(FileOutputStream.java:194)
	at java.io.FileOutputStream.(FileOutputStream.java:84)
	at org.apache.jsp.db_005fsearch.saveproperties_jsp._jspService(saveproperties_jsp.java:120)
	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:377)
	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
	at java.lang.Thread.run(Thread.java:662)

 

Stack trace is just like suspected.

Apparently, I do not know who is the process owner (perhaps it is tomcat?). 

No worries - just make sure the mentioned

/usr/share/tomcat6/.chemaxon/

directory exists, give it all permissions (chmod 777), drop in your license file and enjoy.

Szilard

Hi Szilard,

One more error message, I am almost there.  The message:

The structure table contains newer data version that this program version can handle. Please use a later version.

This appears when I do a search.  No results.

Does this refer to the version of postgresql I am using, or the version of JChem?

Also, I assume that I can do database management with scripts, etc. as always, as long a table and column modification is done through jchemmanager.  Is that correct?

 

Dick

Stack trace:

JChem version : 5.5.0.1
JVM : Sun Microsystems Inc. 1.6.0_26
OS  : i386 Linux 2.6.32-33-generic


Stack trace:
------------


chemaxon.jchem.db.DatabaseSearchException: The structure table contains newer data version that this program version can handle. Please use a later version.
	at chemaxon.jchem.db.JChemSearch.initTable(JChemSearch.java:2620)
	at chemaxon.jchem.db.JChemSearch.init(JChemSearch.java:3477)
	at chemaxon.jchem.db.JChemSearch.search1(JChemSearch.java:1979)
	at chemaxon.jchem.db.JChemSearch.search(JChemSearch.java:1879)
	at chemaxon.jchem.db.JChemSearch.access$2800(JChemSearch.java:121)
	at chemaxon.jchem.db.JChemSearch$SearchThread.run(JChemSearch.java:853)

Could it have something to do with an incorrect Form definition?  The Form applies only to mview right/

Dick

This is because you have created the table with a newer JChem Manager version than the JChem library version you have installed for the JSP application.

Please upgrade the JChem library copied into Tomcat to the same version :

http://www.chemaxon.com/jchem/doc/admin/tomcat.html

Copy all files from <JChem home>/lib/ and the jar or zip file(s) containing the JDBC driver of your choosed database (and all other custom libraries) into Tomcat's lib subdirectory:
Tomcat 4.0: $CATALINA_HOME/lib
Tomcat 4.1 to 5.x: $CATALINA_HOME/shared/lib
Tomcat 6.0 and above: $CATALINA_HOME/lib
Restart Tomcat, so changes can take effect. 

Note: Please always update these jar files when upgrading JChem.

Hi Szilard,

I really appreciate your help and patience.  Obviously a better reading and understanding of the setup.html would have been useful.

I will try to add the 'powered by chemaxon' logo.

Thanks again,

Dick