Programmatically updating tables - JChem 5.2.6 to 5.4.1.1

User a200fcbc5e

21-04-2011 17:13:22

Hi,


I have some HSQLDB database tables which were created with JChem 5.2.6


When I try to programaticallly update these tables to 5.4.1.1 using the 'chemaxon.jchem.db.Updater' class, it manages 2 update steps, but fails on the 3rd, throwing an SQL exception.


It's failing when it's trying to add the cd_pre_calculated column to the table.


The SQL instruction in the stack trace doesn't make sense, namely the "null DEFAULT 0 NOT NULL" part:


 The full SQL instruction in the stack trace  is ALTER TABLE ONTOLOGY_TABLE ADD cd_pre_calculated null DEFAULT 0 NOT NULL


 


My code is:


package toyJChem;

import java.sql.Connection;
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;
    
    public static void main (String[] args) throws Exception
    {
        init();
        
        try
        {
            String message = "";
            Updater ud = new Updater( conn_handler );
            Updater.UpdateInfo ui = null;
            ui = ud.getNextUpdateInfo();
            while (ui != null)
            {
                System.out.println("========================");
                System.out.println("Update:before");
                System.out.println ("");
                System.out.println (ui.message);
                System.out.println("========================");


                message = ud.performCurrentUpdate();


                System.out.println("Update:after");
                System.out.println ("");
                System.out.println(message);


                ui = ud.getNextUpdateInfo();
            }
            shutdownDatabase();    
        }
        catch (SQLException sqle)
        {
            System.out.println("Problem updating : " );
            System.out.println("toString() = : " + sqle);
            sqle.printStackTrace();
        }
    }
    
    private static void init() throws Exception
    {
        connection = ConnectionFactory.getConnection();
        conn_handler = new ConnectionHandler();
        conn_handler.setConnection( connection  );
    }
    
    public static void shutdownDatabase() throws SQLException
    {
        Statement s =connection.createStatement();
        s.execute("SHUTDOWN");
        s.close();
    }
}


==============


The update messages and subsequent stacktrace:


 


========================
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:after

Properties have been added successfully
========================
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:after

Cache tables have been created successfully
========================
Update:before


JChem has detected that some tables were created with an older version of JChem, and the column "cd_pre_calculated" should be added to be usable with this version.
Do you want JChem to upgrade these tables now?
(this may take several minutes for large tables)
Before you select YES, please stop all other JChem applications.
NOTE: You must have the sufficient rights to ALTER these tables.
========================
Problem updating :
toString() = : java.sql.SQLException: Attempt to insert null into a non-nullable column: column: CD_PRE_CALCULATED table: ONTOLOGY_TABLE in statement [ALTER TABLE ONTOLOGY_TABLE ADD cd_pre_calculated null DEFAULT 0 NOT NULL]
java.sql.SQLException: Attempt to insert null into a non-nullable column: column: CD_PRE_CALCULATED table: ONTOLOGY_TABLE in statement [ALTER TABLE ONTOLOGY_TABLE ADD cd_pre_calculated null DEFAULT 0 NOT NULL]
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.jdbcStatement.executeUpdate(Unknown Source)
    at chemaxon.jchem.db.Updater.addPreCalculatedColumn(Updater.java:865)
    at chemaxon.jchem.db.Updater.performCurrentUpdate(Updater.java:214)
    at chemaxon.jchem.db.Updater.performCurrentUpdate(Updater.java:197)
    at toyJChem.JChemDemo_upgrade.main(JChemDemo_upgrade.java:36)


 


Thanks,
Barry


 

User c1ce6b3d19

26-04-2011 09:43:49

Barry,


Thanks for sending us this issue. 


Although manifested in a different way, it looks like this is the same root issue (the null in the SQL statement) found in your previous topic.


https://www.chemaxon.com/forum/ftopic7442.html


 


The solution is the same and should be released in 5.5.1.


 


Thanks,


Jonathan Lee

User a200fcbc5e

04-05-2011 10:10:05

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();
    }
}


 


 


 


 


 


 

ChemAxon 9c0afc9aaf

04-05-2011 23:06:06

Just a quick comment:


 


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)


The code doing the recalculation takes advantage of multiple CPUs by using multiple DB connections for multiple threads (we are planning to rewrite this in the long term).


The ConnectionHanlder should have all the properties set (driver, url, login, password) so it can be "cloned".


I suspect you pass an externally created connection to ConnectionHandler ?


If possible please 


1. set all parameters to connectionHandler


2 .Use ConnectionHandler.connect() to initiate the connection


3. Use thsi connection created in ConnectionHandler.


If this is not all feasible, please still set the parameters (#1) to make this error disappear.


 


Best,


 


Szilard