Jchemstreams and configuring

From Dr. Don Parkin





Hi,





1. I think I have set everything up for Jchem and the cartridge with Oracle 9i


(9.2.0.10) and Tomcat 6.0 as I can start JChemManager and create a


table and even import an sdf file.


However, when I try and 'Configure Jchem Cartridge for Oracle…' using


localhost/jchemstreams/ and fill in the connection properties the same as


for jcman then I get an error message when I 'test the connection'.


Connection failed:


java.sql.SQLException: invalid arguments in call at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java) at oracle.jdbc.dbaccess.DBError.check_error(DBError.java) at oracle.jdbc.ttc7.TTC7Protocol.logon(TTC7Protocol.java) at oracle.jdbc.driver.OracleConnection.(OracleConnection.java) at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java) at java.sql.DriverManager.getConnection(DriverManager.java:582) at…... etc





I'm using:


Driver oracle.jdbc.driver.OracleDriver


URL: jdbc:oracle:thin:@localhost:1521:mydb





2. When I try the examples, I can search a simple_db search database that I have


set up but it only displays the number of hits and pages - no structures.


The source code at the end says:


<p>SQL error


<p>SQLState: 42000<br>


Message: ORA-00936: missing expression


<br>


Vendor: 936<br>





This error message is not displayed.





For the db_search example, the initsearch page displays the error message:


SQL error


SQLState: null


Message: Cannot handle type in field "cd_structure": oracle.sql.BLOB


Vendor: 0





What am I doing wrong?

Donald,





regarding 2. question:


could you send me your JChem version number, and your Oracle driver name?





Edvard

Regarding the first question:





The "test connection" step is not strictly necessary. The problem you are experiencing is probably due to a difficult-to-reproduce bug in the code of the WEB-based Administration GUI. Just save your JDBC settings and go on to perform the remaining steps of the installation.





P.

I am using Jchem version 3.2.7 and in my classpath I have C:\oracle9i\jdbc\lib\ojdbc14.jar;C:\oracle9i\jdbc\lib\classes12.jar








The test script works OK. i.e.





SQL*Plus: Release 9.2.0.1.0 - Production on Tue Jul 24 09:24:00 2007





Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.





Connected to:


Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production


With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options


JServer Release 9.2.0.1.0 - Production





JCHEM_CORE_PKG.GETENVIRONMENT()


--------------------------------------------------------------------------------





Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production


PL/SQL Release 9.2.0.1.0 - Production


CORE 9.2.0.1.0 Production


TNS for 32-bit Windows: Version 9.2.0.1.0 - Production


NLSRTL Version 9.2.0.1.0 - Production


NLSRTL Version 9.2.0.1.0 - Production


JChem version in the database: 3.2.7


JChem version in the Tomcat server: 3.2.7


java.vm.version: 1.6.0_02-b06


java.vm.vendor: Sun Microsystems Inc.


Apache Tomcat/6.0.10





JCHEM_CORE_PKG.GETENVIRONMENT()


--------------------------------------------------------------------------------





Major JDBC version in Tomcat: 1


Minor JDBC version in Tomcat: 0

Quote:

I am using Jchem version 3.2.7 and in my classpath I have C:\oracle9i\jdbc\lib\ojdbc14.jar;C:\oracle9i\jdbc\lib\classes12.jar

The classpath is irrelevant. The relevant jar files are those in $CATALINA_HOME/lib. Please, could you list the files found in that directory? (Only the ojdbc14.jar is required, classes12.jar shouldn't be there.)

Quote:

The test script works OK.

Great! Then you can try and execute the following in sqlplus:

Code:

select jchem_core_pkg.set_password(<current-user's-password>) from dual;

If it returns 0, jchemstreams can connect back to Oracle and you can start using JChem Cartridge.





Ooops, I see that you have Oracle 9.2.0.1.0. This Oracle version is very old and buggy. I strongly recommend to download and apply the latest Oracle 9i patch set. (But upgrade at least to patchlevel 9.2.0.6.)





Thanks


P.

The select jchem_core_pkg.set_password(<current-user's-password>) from dual; statement returns 0.





My Tomcat 6.0/lib directory contains all the files that were in the JChem/lib directory.


The documentation says that they should be copied into the shared/lib directory but there is only the Tomcat 6.0/lib directory.





I still can't see any hits from the simple_db_search and the db_search at


http://localhost/jchem/examples/db_search/initsearch.jsp?uid=8006208535548219392


gives the error message:


SQL error


SQLState: null


Message: Cannot handle type in field "cd_structure": oracle.sql.BLOB


Vendor: 0





I am looking into the upgrade of oracle but it seems I need to register with metalink for which I need a Support Identifier!

Quote:

I am looking into the upgrade of oracle but it seems I need to register with metalink for which I need a Support Identifier!

If you can't afford buying support from Oracle, I see two possibilities: (a) evaluate JChem Cartridge with Oracle 10g (the 10g version freely download-able from Oracle work quite well with JChem Cartridge) (b) evaluate JChem Base alone to have freedom in your choice of the database. JChem Base supports among others MS SQL Server, MySQL and PostgreSQL as well. (PostgreSQL is a free database, while MySQL also has a free edition.)