Error on import of sd file

User 55174beb01

22-12-2005 21:08:02

While importing an sd file into jchem table I get the following error on a particular record. Then the records following that record are not processed. The error is


java.sql.sqlException:Invalid column index.





What does this mean? Only thing I could see was that the structure where the import failed was really large. Could this be the reason?

ChemAxon 9c0afc9aaf

23-12-2005 09:44:35

Hi,





Yes, it happens when the source of some structures is very large, and the RDBMS is Oracle.


This problem has already been fixed in the latest JChem version (3.1.4).


Please upgrade.





Best regards,





Szilard

User 55174beb01

23-12-2005 15:54:50

We have jchem version of 3.1.13. You have mentioned 3.1.4. Is this not older than what we have? If not do you have patches to upgrade?

ChemAxon 9c0afc9aaf

23-12-2005 16:17:09

Dear Usha,





There is no 3.1.13 yet, probably you use 3.0.13.


3.1.4 is our latest release.





Please see the following page for version history:





http://www.chemaxon.com/jchem/changes.html





We do not have a separate patch for 3.0.13. regarding this issue.





Kind regards,





Szilard

User 55174beb01

23-12-2005 16:34:42

We recently migrated to production with version 3.1.3. What is it that we need to do to migrate to 3.1.4 with minimal interruption? Is it possible to apply some patches without dropping all the tables and re installing?

ChemAxon 9c0afc9aaf

23-12-2005 17:11:06

You do not have to drop tables.





1. Uninstall the existing version of JChem Cartridge first.


(the cartridge has to be reinstalled at every upgrade)


Please see the instructions at :





http://www.chemaxon.com/jchem/doc/admin/cartridge.html#uiandup





2. Regenerate your JChem tables.


(This means the recalculation of some columns of existing JChem tables. Thsi can take some time depending on the size of the tables)


The JChemManager (jcman) GUI automatically offers this.


Alternatively you can use "jcman u" from command-line.





3. After this you can install the new version of the Cartridge as usual.





Best regards,





Szilard

User 55174beb01

27-12-2005 21:05:18

As per the instructions we tried to uninstall the old version. While running the uninstall script we had some warning and error messages. But we tried to go ahead and install the new version. Then when we created tables and tried to import, structures were not imported and we were continuously getting this following warning message.


Warning: Partial parity information is applied.





Then we went ahead and dropped the user jchem, uninstalled old version and then created user jchem and installed the new version of jchem.


Created all the tables and started import. Then we get the following error:





Error in molecule 42497


java.lang.ArrayIndexOutOfBoundsException: -1


at chemaxon.marvin.modules.Parity.putToTheTop(Parity.java:2383)


at chemaxon.marvin.modules.Parity.setupStereoBonds(Parity.java:796)


at chemaxon.marvin.modules.Parity.setParity(Parity.java:427)


at chemaxon.marvin.modules.Parity.modfunc(Parity.java:167)


at chemaxon.struc.MoleculeGraph.setParity(MoleculeGraph.java:1442)


at chemaxon.struc.MoleculeGraph.setParity(MoleculeGraph.java:1411)


at chemaxon.marvin.modules.Hydrogenize.implicitizeHydrogens(Hydrogenize.java:265)


at chemaxon.marvin.modules.Hydrogenize.callback(Hydrogenize.java:51)


at chemaxon.struc.MoleculeGraph.callHydrogenize(MoleculeGraph.java:545)


at chemaxon.struc.MoleculeGraph.hydrogenize(MoleculeGraph.java:483)


at chemaxon.reaction.Standardizer.performAction(Standardizer.java:615)


at chemaxon.reaction.Standardizer.standardizeComponent(Standardizer.java:1296)


at chemaxon.reaction.Standardizer.standardize(Standardizer.java:1371)


at chemaxon.jchem.db.TableInfo.standardize(TableInfo.java:1092)


at chemaxon.jchem.db.UpdateHandler.init(UpdateHandler.java:762)


at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:1409)


at chemaxon.jchem.db.FileToSQLHandler.importFile(FileToSQLHandler.java:256)


at chemaxon.jchem.gui.Importer.run(Importer.java:137)


at java.lang.Thread.run(Thread.java:595)


chemaxon.jchem.db.TransferException: -1


at chemaxon.jchem.db.FileToSQLHandler.importFile(FileToSQLHandler.java:307)


at chemaxon.jchem.gui.Importer.run(Importer.java:137)


at java.lang.Thread.run(Thread.java:595)





We also notice that all the common functions for structure search like jc_compare,jc_compareb have all been changed to jcf_compare,jcf_compareb.


We recently went to production with the earlier version of jchem. If we have to upgrade to this new version 3.1.4, we need to change all our packages, recompile and test which is a major task. Is there any other simpler way?





Thanks


Usha

ChemAxon 9c0afc9aaf

28-12-2005 11:56:54

Dear Usha,
Quote:
While running the uninstall script we had some warning and error messages. But we tried to go ahead and install the new version.
Please next time record the first error messages during a procedure, so we can better understand later problems that may be derive from this.


A common mistake is forgetting to delete all indexes with the type of jc_idxtype.


(You have created these indexes for each structure table manually after the cartridge installation)


Without removing these the uninstall script cannot complete successfully.


Did you drop these indexes before running the script ?
Quote:
Then when we created tables and tried to import, structures were not imported
I suspect the structures were not imported due to installing the new version on the partially uninstalled old version.
Quote:
and we were continuously getting this following warning message.


Warning: Partial parity information is applied.
You can ignore this warning.
Quote:
Created all the tables and started import. Then we get the following error:
We have already identified this bug.


We will fix this in the following JChem release (3.1.5).


Quote:
We also notice that all the common functions for structure search like jc_compare,jc_compareb have all been changed to jcf_compare,jcf_compareb.
No, there was no such change.


According to the documentation:
Quote:
Each operator supported by the jc_idxtype index type have a functional equivalent. The name of the functional equivalents have an 'f' inserted as the third character. E.g. the functional equivalent of jc_molconvert is jcf_molconvert.


One possible use of functional equivalents is in SQL constructs where operators cannot be used such as embedded in another operator


http://www.chemaxon.com/jchem/doc/guide/cartridge/index.html#func





I suspect that you simply cannot access the mentioned operators because of a messed up upgrade.


I guess this was before re-creating the user ?


(You should not have this problem in a new installation.)
Quote:
We recently went to production with the earlier version of JChem. If we have to upgrade to this new version 3.1.4, we need to change all our packages, recompile and test which is a major task. Is there any other simpler way?
There was no change in the cartridge API, so you do not have to change your code at all.





However, due to the newly reported bug I have prepared a modified version, which is completely identical to 3.1.3 except the bug fix for large structures.


(The newly reported exception is not present in this version.)


Upgrading to this version does not require table regeneration with jcman either, but otherwise you should follow the normal upgrade procedure including the reinstallation of the cartridge (do not forget to remove the jc_idxtype indexes first).





http://www.chemaxon.com/shared/jchem3_1_3_mod2/jchem3.1.3mod2.zip








Best regards,





Szilard

User 55174beb01

28-12-2005 15:00:34

We did drop the indexes before doing an uninstall. But when we run your uninstall script we always get the error that jc_idxtype_im type cannot be dropped due to dependencies. Every time you fix some bugs it would be greatly appreciated if you can just give us patches. If you give us new release for every bug fix, it will be too much work for us since our system is in production. Every time we install new release we need to do a complete testing before we give our system to our users. This means everyone loses a lot of time in the process.





Thanks


Usha

User 55174beb01

28-12-2005 19:37:44

We corrected the sd file format and imported into the new version 3.1.4. It was succesful with some warnings. Then we went back to 3.1.3 and tried import again. We got the following error on the screen and halted as halt if error occurs was checked.


Error in molecule 2784


java.sql.SQLException: Invalid column index


at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java :124)


at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java :161)


at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java :226)


at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:4582)


at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:7225)


at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedSt atement.java:7547)


at chemaxon.jchem.db.UpdateHandler.setValue(UpdateHandler.java:1247)


at chemaxon.jchem.db.UpdateHandler.setStmt(UpdateHandler.java:1282)


at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:1511)


at chemaxon.jchem.db.FileToSQLHandler.importFile(FileToSQLHandler.java:2 56)


at chemaxon.jchem.gui.Importer.run(Importer.java:137)


at java.lang.Thread.run(Thread.java:595)





Also a small screen appears with the following message


SQL Warning.


SQL State null


Message: Invalid column index


Vendor :17003.





We do not want to install the new release due to all the problems mentioned in my previous post. Do we have a specific solution to address this problem in particular like increasing some column width or something.





Thanks


Usha

ChemAxon 9c0afc9aaf

29-12-2005 11:54:36

Quote:
We corrected the sd file format and imported into the new version 3.1.4. It was succesful with some warnings. Then we went back to 3.1.3 and tried import again. We got the following error on the screen and halted as halt if error occurs was checked.
I assume you did not use the modified version I have linked in my previous post (3.1.3mod2) ?


In that case this exception is not a surprise, this is what you have originally reported in your first post.


(we are back where we started)





Please use the mentioned modified version.





Best regards,





Szilard

ChemAxon 9c0afc9aaf

29-12-2005 13:08:58

Quote:
But when we run your uninstall script we always get the error that jc_idxtype_im type cannot be dropped due to dependencies.
In this case you should remove those dependencies too and run the uninstall script again.


Oracle can show you the list of dependent objects.


Quote:
Every time you fix some bugs it would be greatly appreciated if you can just give us patches. If you give us new release for every bug fix, it will be too much work for us since our system is in production. Every time we install new release we need to do a complete testing before we give our system to our users. This means everyone loses a lot of time in the process.
In general, there are no incremental patches for JChem.


We always build a new minor (bugfix) release.


In these releases we usually only fix bugs, the introduction of bugs is unlikely, although as we recently saw in 3.1.4 it is not impossible.


Incremental patches wouldn't help this: they could also contain new bugs.





The upgrade of JChem Base is relatively painless, the only thing that may take some time is the regeneration of the tables (but this is automatic).





Upgrading JChem Cartridge is a bit more complicated process. We will think about making the process easier, but this is not a trivial problem due to the integration with Oracle.





Kind regards,





Szilard

User 55174beb01

30-12-2005 14:32:23

Is there any value for molecular weight or any other property which would determine that the molecule is large? In other words do you know the minimum molecular weight for which the import fails on version 3.1.3?





Thanks


Usha

ChemAxon 9c0afc9aaf

30-12-2005 14:59:53

Dear Usha,





This is not connected with molecular weight at all.





There is a bug in Oracle's JDBC driver for inserting larger than 4KB data.


As a workaround structures with larger source must be inserted in a different way.


The problem was with the implementation of this workaround in our code.





So it's the size of the structure source that counts.


During import we remove some data fields and compress some formats, so you cannot accurately detect those in the input file.





In short, you should change the version to get rid of this problem.


(as usually is the case with other fixes and improvements)


If it's urgent I recommend the modified version of 3.1.3 that I have linked in this topic. Otherwise you may wait for the next minor JChem release which we plan to release soon.





Best regards,





Szilard

ChemAxon aa7c50abf8

02-01-2006 10:49:41

Quote:
We did drop the indexes before doing an uninstall. But when we run your uninstall script we always get the error that jc_idxtype_im type cannot be dropped due to dependencies.
In order to upgrade JChem Cartridge, please, do the following:





1.) Uncompress the installation package for the new JChem version (the JChem 3.1.4 installation package if you want to upgrade to JChem 3.1.4).


2.) Make sure that no user can access JChem Cartridge. (See "Restricting Access to an Open Database" of the "Starting Up and Shutting Down" section in the Oracle "Database Administrator's Guide".)


3.) If your Tomcat is running, shut it down.


4.) Delete the "jchemstreams" directory in the "webapps" directory of Tomcat.


5.) Copy the jchemstreams.war file from the "cartridge" subdirectory of the new uncompressed JChem installation package to the "webapps" directory of Tomcat. (Overwrite the old version.)


6.) Copy the jar files from the "lib" subdirectory of the new uncompressed JChem installation package to the "shared/lib" directory of Tomcat. If you plan to use INCHI format, copy the inchi shared library for your platform (e.g. inchi.dll for Windows or libinchi.so for Linux) along with the jar files to the same directory. (Overwrite the old version.)


7.) Make sure you have write permissions on subdirectories of the new uncompressed JChem installation package.


8.) Copy the attached file called jchem-upgrade.jar into the "lib" subdirectory of the new uncompressed JChem installation package.


9.) Copy the attached file called update.sh into the "cartridge" subdirectory of the new uncompressed JChem installation package.


10.) Change your current working directory into the "cartridge" subdirectory of the new uncompressed JChem installation package.


11.) Execute the shell script upgrade.sh passing the script the SQLPlus connection string as the first (and sole) parameter. For example:


Code:
bash upgrade.sh jchem/tiger@mydb



if the schema where JChem Cartridge is installed is "jchem", the password is "tiger" and the TNS name of the database is "mydb".


12.) Re-create synonyms (public or private depending on your environment) for objects in the JChem Cartridge schema ("jchem" in the above example) as you had them with the previous JChem Cartridge version.


13.) Re-grant necessary privileges to users on objects in the JChem Cartridge schema ("jchem" in the above example). (See http://www.chemaxon.com/forum/ftopic243.html for examples)


14.) Restart Tomcat.





Please, note that you may still need to "regenerate" your JChem tables (with JChemManager) or reindex jc_idxtype indexes on regular tables (depending on which table type [JChem or regular] you use to store structures). (With JChem tables you do not have to regenerate jc_idxtype indexes, you only need to regenerate the structure tables themselves.) You have to regenerate JChem tables or structural indexes each time the table version changes and the table version almost always changes with each new JChem release. JChem gives you a warning (in the form of an error message), if you need take action to this end.





I expect this to be the standard upgrade procedure for JChem Cartridge in future JChem versions -- with the exception that the upgrade.sh shell script and the Java classes necessary for the upgrade will be included in the installation package.





Peter

ChemAxon aa7c50abf8

04-01-2006 14:15:07

The upgrade procedure described above changes the JChem Cartridge implementation without requiring you to drop your dependent objects. (Some of the dependent procedures will be marked INVALID during the upgrade, but they will be automatically recompiled by Oracle as needed ("lazily") after you start using the upgraded JChem Cartridge.)





Peter