Hi, We have a database with 8 miilion structures and indexed with JChem cartridge 9.0. The indexed field is a smiles field in a compound property table, what we call a standard table instead of jchem base table. It has been running fine with different version of the client such as 9.2, 9.3 IJC. Recently our webstart for downaloding the IJC client has point to the IJC5.10. All the sudden the IJC/JChem connection acts up. We are getting an error complaninging the incompatibility of the JChem with IJC as in attached image. We do not have plan to upgrade the JChem server/cartridge/index as we are in a process of moving the database server, IJC server to a different machine. My question is there a way we can solve the issue without having to upgrade the JChem server/cartridge.
This is most likely due to a JChemProperties table being a different version than IJC. The error message you're seeing is because you don't have the privileges to update the schema tables - otherwise you'd be asked if you want to update the tables.
The other possibility (albeit slim), is that there is a JchemBase table in the schema (or another schema that is being accessed) that is out of version sync. This option needs to be considered becaue if there are any of these tables, and they get upgraded, they will no longer be accessibly to IJC 5.9.x.
Make sure this isn't the case by going into the schemas that are being accessed in SQL Developer or similar. If you're comfortable that only the JChemProperties tables are in need of upgrading, log on with sufficient privileges and then you will be prompted to walk through the upgrade steps.
Feel free to double check with the tables in the schema(s) with us first if you'd like to be sure.
Here is what I did:
1. Login IJC as admin
2. When IJC prompt me for upgrade, I reponse OK
3. IJC does not like what it sees and stoped with upgrading process with following key error message:
Caused: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT count(*) FROM IP.STRUCTURE_DATA]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
Indeed the IP.STRUCTURE_DATA is a temprary test table, was removed a while ago. In order not let IJC think this is still a useful JChem table I removed all the entries starting with 'IP.STRUCTURE_DATA' from jchemproperties table in the database. But somehow IJC still sees this as a jchem table. What else I should do in order for IJC disregard the IP.STRUCTURE_DATA table. I remember Szilard has warned me before it is not a good idea to manually mess around with jchemproperties table.
I did not remove all the references in jchemproperties table to the removed jchem table structure_data. Besides the IP.STRUCTURE_DATA entry in PROP_NAME column in jchemproperties table there are also entries start with TABLE.IP.STRUCTURE_DATA, once I removed those entries, the foot prints of the JChem table are wiped out clean. IJC 5.10 did no have any issue to open the database with a standard structure table that has cartridge index from version 5.9.
Thanks for clarification. I was trying to reproduce it but unsuccessfully. I am glad that it works now and considering this issue solved.
Yes, if you are deleting tables manually you must also remove all references to thoses tables in the JCHEMPROPERTIES table, otherwise JChem will be in an inconsistent state.
As you state though, this is not a recommended approach. Tables should really be created and deleted through the API, of through applications that use the API. To do it manually is risky.