Regenerating tables

I am updating from JChem 3.0.5 to 5.3.1 and the in the process wanted to regenerate my structure tables. Initially the regeneration did not work as it gave an error for each row stating that the column - CD_SORTABLE_FORMULA - does not exist. When I added the column it said that regeneration was successful, but the console still came up with this error as pasted below. Is there anything I need to do? 

java.sql.SQLException: ORA-00942: table or view does not exist

        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:204)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:942)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
        at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1770)
        at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1739)
        at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:299)
        at chemaxon.jchem.db.UpdateHandler.storeUpdate(UpdateHandler.java:2445)
        at chemaxon.jchem.db.UpdateHandler.saveUpdateAndGetUpdateCounter(UpdateHandler.java:2434)
        at chemaxon.jchem.db.UpdateHandler.close(UpdateHandler.java:2403)
        at chemaxon.jchem.db.Regenerator$RegenThread.run(Regenerator.java:130)
java.sql.SQLException: ORA-00942: table or view does not exist

        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:204)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:942)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
        at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1770)
        at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1739)
        at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:299)
        at chemaxon.jchem.db.UpdateHandler.storeUpdate(UpdateHandler.java:2445)
        at chemaxon.jchem.db.UpdateHandler.saveUpdateAndGetUpdateCounter(UpdateHandler.java:2434)
        at chemaxon.jchem.db.UpdateHandler.close(UpdateHandler.java:2403)
        at chemaxon.jchem.db.Regenerator$RegenThread.run(Regenerator.java:130)

Hi,

When you update JChem version, you have to run "jcman u" in command line or should use the GUI. This will do all changes are needed, including database updates and regeneration tasks. Now that you added CD_SORTABLE_FORMULA manually, the update will be a bit complicated. Here are the steps I suggest you to follow:

1. Remove the CD_SORTABLE_COLUMN field you created. (If you've made other changes, please, tell me.)

2. Check your table.<YourTableName>.version property (prop_name column) in your JChem property table (jchemproperties by default). Its prop_value should be 5030100 or something like this. Set this value to 1.

3. Run "jcman u" in command line.

Does it work? Maybe this "regeneration" message itself was a little bit ambigous, so we are considering to have a better message or solution for this case in the future. Thanks for reporting the problem, and please tell us, if you have further issues.

Regards,

Roland

Thanks for your reply, Roland. Is there a way to update only one of the structure tables (e.g. jcman u - TEST_TABLE) . I just want to update one of the test tables and see how it goes before I update the production tables.

On the other hand, I was considering exporting (my existing production table) and importing to new tables (e.g. COMPOUNDS_NEW) and then delete the old table and rename the new one to COMPOUNDS. Is there a way in JChem to rename tables? Or do I have to get them done manually by changing the trigger, sequence and jchem properties tables.

 

Hi,

Thanks for your reply, Roland. Is there a way to update only one of the structure tables (e.g. jcman u - TEST_TABLE) . I just want to update one of the test tables and see how it goes before I update the production tables.

Since the update also affects the structure of the property table and other "helper" table as well, one cannot do it separately.

For testing migration it's best to use a clone of the database.

Using an other property table (e.g. "JChemPropertiesTest45") with new tables created in that environment is also a great way to handle different versions in the same DB. (each property table is a separate JChem environment)

 

On the other hand, I was considering exporting (my existing production table) and importing to new tables (e.g. COMPOUNDS_NEW) and then delete the old table and rename the new one to COMPOUNDS. Is there a way in JChem to rename tables? Or do I have to get them done manually by changing the trigger, sequence and jchem properties tables.

There is no method to rename JChem tables and we do not advise manually altering JChem database objects.

For huge table to minimize downtime during migration pre-calculation is also available now:

http://www.chemaxon.com/jchem/doc/admin/#precalc

Best regards,

Szilard