Error 22001 - truncating to VARCHAR length 1000

Hello,

I have a column for InChI, and it uses the VARCHAR length 1000,
and although I had created a database of about 200 structures without problems, now every time I try to add a new structure, of very similar size to the existing ones, I get Error 22001:

ERROR 22001: A truncation error was encountered trying to shrink VARCHAR 'InChI=1S/C34H30O8S/c1-22-17-19-26(20-18-22)43-34-30(42-33(38&' to length 1000.
    at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
    at org.apache.derby.iapi.types.SQLChar.hasNonBlankChars(Unknown Source)
    at org.apache.derby.iapi.types.SQLVarchar.normalize(Unknown Source)
    at org.apache.derby.iapi.types.SQLVarchar.normalize(Unknown Source)
    at org.apache.derby.iapi.types.DataTypeDescriptor.normalize(Unknown Source)
    at org.apache.derby.impl.sql.execute.NormalizeResultSet.normalizeColumn(Unknown Source)
    at org.apache.derby.impl.sql.execute.NormalizeResultSet.normalizeRow(Unknown Source)
    at org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(Unknown Source)
    at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(Unknown Source)
    at org.apache.derby.impl.sql.execute.InsertResultSet.open(Unknown Source)
    at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
    at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
Caused: java.sql.SQLException: A truncation error was encountered trying to shrink VARCHAR 'InChI=1S/C34H30O8S/c1-22-17-19-26(20-18-22)43-34-30(42-33(38&' to length 1000.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
Caused: java.sql.SQLDataException: A truncation error was encountered trying to shrink VARCHAR 'InChI=1S/C34H30O8S/c1-22-17-19-26(20-18-22)43-34-30(42-33(38&' to length 1000.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
    at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:2248)
    at com.im.df.impl.db.dao.jchem.AbstractJChemDao$UpdateHandlerImpl.insert(AbstractJChemDao.java:303)
Caused: org.springframework.dao.DataIntegrityViolationException: A truncation error was encountered trying to shrink VARCHAR 'InChI=1S/C34H30O8S/c1-22-17-19-26(20-18-22)43-34-30(42-33(38&' to length 1000.; SQL []; A truncation error was encountered trying to shrink VARCHAR 'InChI=1S/C34H30O8S/c1-22-17-19-26(20-18-22)43-34-30(42-33(38&' to length 1000.; nested exception is java.sql.SQLDataException: A truncation error was encountered trying to shrink VARCHAR 'InChI=1S/C34H30O8S/c1-22-17-19-26(20-18-22)43-34-30(42-33(38&' to length 1000.
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:228)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at com.im.df.impl.db.dao.jchem.AbstractJChemDao$UpdateHandlerImpl.insert(AbstractJChemDao.java:313)
    at com.im.df.impl.db.dao.DFDaoSupport.insert(DFDaoSupport.java:118)
    at com.im.df.impl.db.dao.DFDaoWrapper.insert(DFDaoWrapper.java:66)
    at com.im.df.impl.db.DBEntityDataProviderImpl.doInsertRow(DBEntityDataProviderImpl.java:354)
    at com.im.df.impl.db.DBEntityDataProviderImpl.access$5(DBEntityDataProviderImpl.java:294)
    at com.im.df.impl.db.DBEntityDataProviderImpl$3.doInTransaction(DBEntityDataProviderImpl.java:285)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128)
    at com.im.df.impl.db.DBEntityDataProviderImpl.insertOrFindRow(DBEntityDataProviderImpl.java:281)
    at com.im.df.impl.db.InsertionHandler.doInsertData(InsertionHandler.java:307)
    at com.im.df.impl.db.InsertionHandler.doInTransaction(InsertionHandler.java:267)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128)
    at com.im.df.impl.db.DBSchemaDataProviderImpl.insert(DBSchemaDataProviderImpl.java:204)
    at com.im.ijc.core.api.actions.InsertRowAction$3.phase1InRequestProcessor(InsertRowAction.java:420)
    at com.im.commons.progress.BackgroundRunner.processTheTask(BackgroundRunner.java:126)
    at com.im.commons.progress.BackgroundRunner$1.run(BackgroundRunner.java:90)
    at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:577)
[catch] at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:1030)

Sometimes I can avoid the error by removing a few elements from the molecule, so it's not generated 100% of the time.  However, I essentially can't add the structures I need to anymore.  These are all "small molecules" of FW 500 or less.

When I hover over the InChI column entries in grid view, it shows a lot of "AuxInfo=" in addition to just the InChI= data, which I don't think I'd noticed before - is that what is making the entries too long?

Thank you,

Barbara

Hi, you are using the default column length of 1000 characters. Usually this is OK for simple data, but in your case its not.

You can change the column length in the database to a higher value. 10,000 should be fine for InChi.

To do this:

1. open the schema editor
http://www.chemaxon.com/instantjchem/ijc_latest/docs/user/help/htmlfiles/editing_database/schema_editor.html />Do this by double clicking on the schema node in the projects window.

2. locate the appropriate field under the entities section.

3. editt the 'Length' property and increase it.

4. click the 'Apply' button.

Note that as you are using a Derby (local) database the value can only be increased, not decreased.

Tim

Hi Tim,

Thanks, I had actually already tried to do this (just by going to Change Widget Settings), but either that way or throught the Schema Editor, the Apply button is blank, and when I close the window, the new length reverts to 1000.

I am attaching a screen shot of the Schema Editor.

Thanks!!

Barbara

You need to make a change to the length property, and then click on the apply button.

Until a change is made the apply button is not enabled.

Let me know if this helps.
Tim

Thanks, but as you can see in my screen shot, I have changed the Length field to 10000, but the Apply button is still greyed out.

Barbara

I tested it and I'm afraid there is one minor bug in this dialog. When you modify just lenght then apply button is not enabled. As workaround please change also field name e.g. to "Smiles 2" (you can rename it later back). Then maybe you'll need to press Apply button twice. We'll investigate this issue and fix in next release.

Petr

Thanks, Petr!  I love work-arounds

Changing the name enables the Apply button.  It's accepting my new InChI's now.

Thank you!

Barbara

IJC 5.6.0.2 is released. This bug should be fixed in current version.

Filip