I tried to put in workarounds to see if it would then complete the updates.
I added in the cd_pre_calculated column myself instead of waiting for the update to do it.
Also modified the is_protected column in the JCHEMPROPERTIES_CR table.
Doing this gets rid of the errors pertaining to those columns.
However, it's now failing when JChem tries to recalculate some of the tables.
The status of the database (i.e the .script file) just before the error shows that the two columns above are of the correct type as they were fixed:
====================
.script file contents (start)
VARCHAR(20),CD_TIMESTAMP TIMESTAMP NOT NULL,CD_FP1 INTEGER NOT NULL,CD_FP2 INTEGER NOT NULL,CD_FP3 INTEGER NOT NULL,CD_FP4 INTEGER NOT NULL,CD_FP5 INTEGER NOT NULL,CD_FP6 INTEGER NOT NULL,CD_FP7 INTEGER NOT NULL,CD_FP8 INTEGER NOT NULL,CD_FP9 INTEGER NOT NULL,CD_FP10 INTEGER NOT NULL,CD_FP11 INTEGER NOT NULL,CD_FP12 INTEGER NOT NULL,CD_FP13 INTEGER NOT NULL,CD_FP14 INTEGER NOT NULL,CD_FP15 INTEGER NOT NULL,CD_FP16 INTEGER NOT NULL,CD_PRE_CALCULATED TINYINT DEFAULT 0 NOT NULL)
CREATE INDEX ONTOLOGY_TABLE_HX ON ONTOLOGY_TABLE(CD_HASH)
CREATE INDEX ONTOLOGY_TABLE_FX ON ONTOLOGY_TABLE(CD_SORTABLE_FORMULA)
CREATE CACHED TABLE ONTOLOGY_TABLE_UL(UPDATE_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,UPDATE_INFO VARCHAR(120) NOT NULL,CACHE_ID VARCHAR(32) NOT NULL)
CREATE INDEX ONTOLOGY_TABLE_CX ON ONTOLOGY_TABLE_UL(CACHE_ID)
CREATE CACHED TABLE JCHEMPROPERTIES_CR(CACHE_ID VARCHAR(32) NOT NULL,REGISTRATION_TIME VARCHAR(30) NOT NULL,IS_PROTECTED TINYINT DEFAULT 0 NOT NULL,CONSTRAINT CACHE_30129113_PK PRIMARY KEY(CACHE_ID))
SET TABLE ONTOLOGY_TABLE INDEX'272 272 272 2'
ALTER TABLE ONTOLOGY_TABLE ALTER COLUMN CD_ID RESTART WITH 2
ALTER TABLE ONTOLOGY_TABLE_UL ALTER COLUMN UPDATE_ID RESTART WITH 1
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10
SET SCHEMA PUBLIC
INSERT INTO JCHEMPROPERTIES VALUES('cache.registration_table','JCHEMPROPERTIES_CR',NULL)
INSERT INTO JCHEMPROPERTIES VALUES('option.commitInterval','50',NULL)
INSERT INTO JCHEMPROPERTIES VALUES('option.structureCompressionDisabled','true',NULL)
INSERT INTO JCHEMPROPERTIES VALUES('propertytable.identifier','PT_ID_d6c1d5a2a811432c9015ef723ea3dd15',NULL)
INSERT INTO JCHEMPROPERTIES VALUES('table.ONTOLOGY_TABLE.JChemVersion','5.2.6',NULL)
INSERT INTO JCHEMPROPERTIES VALUES('table.ONTOLOGY_TABLE.absoluteStereo','true',NULL)
INSERT INTO JCHEMPROPERTIES VALUES('table.ONTOLOGY_TABLE.creationTime','2011-05-04 10:51:31.049',NULL)
INSERT INTO JCHEMPROPERTIES VALUES('table.ONTOLOGY_TABLE.fingerprint.numberOfBits','512',NULL)
INSERT INTO JCHEMPROPERTIES VALUES('table.ONTOLOGY_TABLE.fingerprint.numberOfEdges','6',NULL)
INSERT INTO JCHEMPROPERTIES VALUES('table.ONTOLOGY_TABLE.fingerprint.numberOfOnes','2',NULL)
INSERT INTO JCHEMPROPERTIES VALUES('table.ONTOLOGY_TABLE.fingerprint.numberOfStrucFPCols','0',NULL)
INSERT INTO JCHEMPROPERTIES VALUES('table.ONTOLOGY_TABLE.tableType','2',NULL)
INSERT INTO JCHEMPROPERTIES VALUES('table.ONTOLOGY_TABLE.tautomerDuplicateFiltering','false',NULL)
INSERT INTO JCHEMPROPERTIES VALUES('table.ONTOLOGY_TABLE.validityTimestamp','2011-05-04 10:51:31.05',NULL)
INSERT INTO JCHEMPROPERTIES VALUES('table.ONTOLOGY_TABLE.version','5020400',NULL)
.script file contents (end)
=====================
stacktrace and code is below:
=======Start upgrade==============
Attempt: add cd_pre_calculated column
Success: add cd_pre_calculated column
Attempt: alter is_protected column
Fail: alter is_protected column: Table not found: JCHEMPROPERTIES_CR in statement [ALTER TABLE JCHEMPROPERTIES_CR]
========================
Update:before
JChem has detected that some important properties are missing from the property table.
Do you want JChem to add these properties now?
NOTE: You must have the sufficient rights to UPDATE the property table.
Update:result = Properties have been added successfully
========================
Attempt: alter is_protected column
Fail: alter is_protected column: Table not found: JCHEMPROPERTIES_CR in statement [ALTER TABLE JCHEMPROPERTIES_CR]
========================
Update:before
JChem has detected that some tables were created with an older version of JChem, and cache tables should be created for them to be usable with this version.
Do you want JChem to create these cache tables now?
Before you select YES, please stop all JChem operations and searches.
NOTE: You must have the CREATE TABLE right for this.
Update:result = Cache tables have been created successfully
========================
Attempt: alter is_protected column
Success: alter is_protected column
========================
Update:before
JChem has detected that some tables were created with an older version of JChem,
and may contain obsolete calculated values or Chemical Terms data that need
full recalculation.
Do you want JChem to recalculate these tables ?
(this may take some time, possibly hours, depending on the size of your database)
If you select NO, search results will be inaccurate for these tables.
java.lang.NullPointerException
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:169)
at chemaxon.util.ConnectionHandler.connectToDatabase(ConnectionHandler.java:321)
at chemaxon.util.ConnectionHandler.connect(ConnectionHandler.java:312)
at chemaxon.jchem.db.Regenerator.run(Regenerator.java:352)
at chemaxon.jchem.db.UpdateHandler.recalculateTable(UpdateHandler.java:3104)
at chemaxon.jchem.db.UpdateHandler.recalculateTable(UpdateHandler.java:3067)
at chemaxon.jchem.db.Updater.regenerateTables(Updater.java:1007)
at chemaxon.jchem.db.Updater.performCurrentUpdate(Updater.java:219)
at chemaxon.jchem.db.Updater.performCurrentUpdate(Updater.java:197)
at toyJChem.JChemDemo_upgrade.main(JChemDemo_upgrade.java:45)
java.sql.SQLException
at chemaxon.jchem.db.Regenerator.run(Regenerator.java:358)
at chemaxon.jchem.db.UpdateHandler.recalculateTable(UpdateHandler.java:3104)
at chemaxon.jchem.db.UpdateHandler.recalculateTable(UpdateHandler.java:3067)
at chemaxon.jchem.db.Updater.regenerateTables(Updater.java:1007)
at chemaxon.jchem.db.Updater.performCurrentUpdate(Updater.java:219)
at chemaxon.jchem.db.Updater.performCurrentUpdate(Updater.java:197)
at toyJChem.JChemDemo_upgrade.main(JChemDemo_upgrade.java:45)
chemaxon.jchem.db.UpdateHandlerException: java.sql.SQLException
Caused by:
java.sql.SQLException
at chemaxon.jchem.db.UpdateHandler.recalculateTable(UpdateHandler.java:3108)
at chemaxon.jchem.db.UpdateHandler.recalculateTable(UpdateHandler.java:3067)
at chemaxon.jchem.db.Updater.regenerateTables(Updater.java:1007)
at chemaxon.jchem.db.Updater.performCurrentUpdate(Updater.java:219)
at chemaxon.jchem.db.Updater.performCurrentUpdate(Updater.java:197)
at toyJChem.JChemDemo_upgrade.main(JChemDemo_upgrade.java:45)
Caused by: java.sql.SQLException
at chemaxon.jchem.db.Regenerator.run(Regenerator.java:358)
at chemaxon.jchem.db.UpdateHandler.recalculateTable(UpdateHandler.java:3104)
... 5 more
java.sql.SQLException: java.sql.SQLException
Caused by:
java.sql.SQLException
at chemaxon.jchem.db.Updater.regenerateTables(Updater.java:1022)
at chemaxon.jchem.db.Updater.performCurrentUpdate(Updater.java:219)
at chemaxon.jchem.db.Updater.performCurrentUpdate(Updater.java:197)
at toyJChem.JChemDemo_upgrade.main(JChemDemo_upgrade.java:45)
Problem updating :
toString() = : java.sql.SQLException: java.sql.SQLException
Caused by:
java.sql.SQLException
----
package toyJChem;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import chemaxon.jchem.db.Updater;
import chemaxon.util.ConnectionHandler;
public class JChemDemo_upgrade
{
public static final String TABLE_NAME = "ONTOLOGY_TABLE";
public static final String NODE_ID_COLUMN_NAME = "node_id";
public static final String ORIGINAL_SMILES_COLUMN_NAME = "original_smiles";
private static Connection connection;
private static ConnectionHandler conn_handler;
private static boolean preCalculatedColumnSuccess = false;
private static boolean isProtectedColumnSuccess = false;
public static void main (String[] args) throws Exception
{
init();
try
{
System.out.println("=======Start upgrade==============");
String message = "";
Updater ud = new Updater( conn_handler );
Updater.UpdateInfo ui = null;
ui = ud.getNextUpdateInfo();
while (ui != null)
{
fixNewColumns();
System.out.println ("");
System.out.println("========================");
System.out.println("Update:before");
System.out.println (ui.message);
checkpointDatabase();
message = ud.performCurrentUpdate();
System.out.println("Update:result = " + message);
System.out.println("========================");
System.out.println ("");
ui = ud.getNextUpdateInfo();
}
shutdownDatabase();
}
catch (SQLException sqle)
{
System.out.println("Problem updating : " );
System.out.println("toString() = : " + sqle);
sqle.printStackTrace();
}
}
private static void fixNewColumns()
{
//try to add or alter any newly-introduced columns (if they exist yet)
try
{
if (!preCalculatedColumnSuccess)
{
System.out.println("Attempt: add cd_pre_calculated column ");
addColumnInStructureTable();
System.out.println ("Success: add cd_pre_calculated column" );
preCalculatedColumnSuccess = true;
}
}
catch (SQLException e)
{
System.out.println ("Fail: add cd_pre_calculated column: " + e.getMessage());
}
try
{
if (!isProtectedColumnSuccess)
{
System.out.println("Attempt: alter is_protected column ");
alterColumnInPropertiesTable();
System.out.println ("Success: alter is_protected column" );
isProtectedColumnSuccess = true;
}
}
catch (SQLException e)
{
System.out.println ("Fail: alter is_protected column: " + e.getMessage());
}
}
private static void init() throws Exception
{
connection = ConnectionFactory.getConnection();
conn_handler = new ConnectionHandler();
conn_handler.setConnection( connection );
}
public static void checkpointDatabase() throws SQLException
{
Statement s =connection.createStatement();
s.execute("CHECKPOINT");
s.close();
}
public static void shutdownDatabase() throws SQLException
{
Statement s =connection.createStatement();
s.execute("SHUTDOWN");
s.close();
}
public static void addColumnInStructureTable() throws SQLException
{
Statement s = connection.createStatement();
String alterSql = buildAddColumn(TABLE_NAME, "cd_pre_calculated");
ResultSet rs = s.executeQuery(alterSql);
rs.close();
}
public static void alterColumnInPropertiesTable() throws SQLException
{
Statement s = connection.createStatement();
String alterSql = buildAlterColumn("JCHEMPROPERTIES_CR", "IS_PROTECTED");
ResultSet rs = s.executeQuery(alterSql);
rs.close();
}
public static String buildAddColumn( String tableName, String columnName ) throws IllegalStateException
{
StringBuffer buffer = new StringBuffer("ALTER TABLE ");
buffer.append( tableName );
buffer.append (" ADD COLUMN " + columnName + " TINYINT DEFAULT 0 NOT NULL");
return buffer.toString();
}
public static String buildAlterColumn( String tableName, String columnName ) throws IllegalStateException
{
StringBuffer buffer = new StringBuffer("ALTER TABLE ");
buffer.append( tableName );
buffer.append (" ALTER COLUMN " + columnName + " TINYINT DEFAULT 0 NOT NULL");
return buffer.toString();
}
}