User 677b9c22ff
17-08-2007 22:22:05
Hi,
Would it be possible to escape the 8-bit era and increase
the column count to somewhat around 10k?
I know its Derby, but I had problems to import some
of my old annotated SD files. I got an IndexArray out of bound after 500 DB entries, and 500 in a standard DB.
Thank you
Tobias
ChemAxon fa971619eb
18-08-2007 18:08:15
The number of fields is limited by the number of columns that are allowed in the underlying database table. This limit will be different for different database types, and there is nothing we can do to change this.
Could you send us the error you get so that we can confim that the error comes from the database, not IJC.
Tim
User 677b9c22ff
20-08-2007 20:49:31
Hi,
two errors from two different days. First error message is during import.
The second error message happens after stopping the import and opening the DB (Index out of bound 512).
I would like to send you the file (2000x1800), but its 17MB packed,
performance during import is not that good, the field creation
takes quite some time, considering that 17 MB main memory
are available since some time (lets say 17MB x 3 for the overhead)
it should be possible to handle such a small table for the Apache Derby DB.
I remember my first 386 had 64 MB RAM.
However handling large
DBs even with 1 or 2 Mio compounds is not a problem, handling is very quick and convenient.
Tobias
org.springframework.dao.DataAccessResourceFailureException: StatementCallback; SQL [ALTER TABLE X1800X2000_SMI_MATRIX ADD COLUMN FIELD_989 REAL]; Too many columns (1013) were specified for table or view X1800X2000_SMI_MATRIX. The limit is 1012.; nested exception is java.sql.SQLException: Too many columns (1013) were specified for table or view X1800X2000_SMI_MATRIX. The limit is 1012.
Caused by: ERROR 54011: Too many columns (1013) were specified for table or view X1800X2000_SMI_MATRIX. The limit is 1012.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.sql.compile.AlterTableNode.bind(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:364)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:338)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:371)
at com.im.df.impl.db.dao.DFDaoSupport.addColumn(DFDaoSupport.java:232)
at com.im.df.impl.db.DBSchemaManager.createNewColumnsForField(DBSchemaManager.java:354)
at com.im.df.impl.db.DBSchemaManager.access$100(DBSchemaManager.java:97)
at com.im.df.impl.db.DBSchemaManager$2.doInTransactionWithoutResult(DBSchemaManager.java:289)
at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:33)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:134)
at com.im.df.impl.db.DBSchemaManager.addField(DBSchemaManager.java:285)
at com.im.df.impl.db.persist.DBSchemaPersistenceImpl$ClientServerDBFieldAbstractPersistence$2.doInTransactionWithoutResult(DBSchemaPersistenceImpl.java:1094)
at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:33)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:134)
at com.im.df.impl.db.persist.DBSchemaPersistenceImpl$ClientServerDBFieldAbstractPersistence.addNew(DBSchemaPersistenceImpl.java:1083)
at com.im.df.impl.db.persist.DBSchemaPersistenceImpl$ClientServerDBFieldAbstractPersistence.addNew(DBSchemaPersistenceImpl.java:1019)
[catch] at com.im.df.impl.db.field.DBFieldAbstractImpl.createNew(DBFieldAbstractImpl.java:103)
at com.im.df.impl.db.field.DBNewStdFieldNT.create(DBNewStdFieldNT.java:118)
at com.im.df.impl.db.field.DBNewStdFieldNT.create(DBNewStdFieldNT.java:31)
at com.im.workbench.ijc.io.quickimport.QuickImportWizardIterator.createFields(QuickImportWizardIterator.java:608)
at com.im.workbench.ijc.io.quickimport.QuickImportWizardIterator.access$000(QuickImportWizardIterator.java:83)
at com.im.workbench.ijc.io.quickimport.QuickImportWizardIterator$2.run(QuickImportWizardIterator.java:559)
at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:499)
at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:932)
---------------
OLD ERROR
--------------
INFO: Added column FIELD_511 VARCHAR(1000) to table NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:49 AM com.im.df.core.DatabasePlatform clearTableInfo
INFO: Cleared table info from cache for NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:49 AM com.im.df.core.DatabasePlatform buildTableInfo
INFO: Building TableInfo for table NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:50 AM com.im.df.impl.db.dao.DFDaoSupport addColumn
INFO: Added column FIELD_512 VARCHAR(1000) to table NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:50 AM com.im.df.core.DatabasePlatform clearTableInfo
INFO: Cleared table info from cache for NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:50 AM com.im.df.core.DatabasePlatform buildTableInfo
INFO: Building TableInfo for table NEW_STANDARD_ENTITY
*********** Exception occurred ************ at 11:40 AM on Aug 7, 2007
java.lang.ArrayIndexOutOfBoundsException: 512
at java.awt.GridBagLayout.GetLayoutInfo(Unknown Source)
at java.awt.GridBagLayout.getLayoutInfo(Unknown Source)
at java.awt.GridBagLayout.preferredLayoutSize(Unknown Source)
at java.awt.Container.preferredSize(Unknown Source)
at java.awt.Container.getPreferredSize(Unknown Source)
at javax.swing.JComponent.getPreferredSize(Unknown Source)
at java.awt.BorderLayout.preferredLayoutSize(Unknown Source)
at java.awt.Container.preferredSize(Unknown Source)
at java.awt.Container.getPreferredSize(Unknown Source)
at javax.swing.JComponent.getPreferredSize(Unknown Source)
at javax.swing.ScrollPaneLayout.layoutContainer(Unknown Source)
at java.awt.Container.layout(Unknown Source)
at java.awt.Container.doLayout(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validate(Unknown Source)
at javax.swing.RepaintManager.validateInvalidComponents(Unknown Source)
at javax.swing.SystemEventQueueUtilities$ComponentWorkRequest.run(Unknown Source)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
[catch] at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.Dialog$1.run(Unknown Source)
at java.awt.Dialog$2.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.awt.Dialog.show(Unknown Source)
at org.netbeans.core.windows.services.NbPresenter.superShow(NbPresenter.java:816)
at org.netbeans.core.windows.services.NbPresenter.doShow(NbPresenter.java:859)
at org.netbeans.core.windows.services.NbPresenter.run(NbPresenter.java:847)
at org.openide.util.Mutex.doEventAccess(Mutex.java:1178)
at org.openide.util.Mutex.readAccess(Mutex.java:215)
at org.netbeans.core.windows.services.NbPresenter.show(NbPresenter.java:832)
at java.awt.Component.show(Unknown Source)
at java.awt.Component.setVisible(Unknown Source)
at com.im.workbench.ijc.io.quickimport.QuickImportWizardAction.performAction(QuickImportWizardAction.java:118)
at org.openide.util.actions.CallableSystemAction$1.run(CallableSystemAction.java:100)
at org.netbeans.modules.openide.util.ActionsBridge.doPerformAction(ActionsBridge.java:53)
at org.openide.util.actions.CallableSystemAction.actionPerformed(CallableSystemAction.java:96)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.AbstractButton.doClick(Unknown Source)
at javax.swing.plaf.basic.BasicMenuItemUI.doClick(Unknown Source)
at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Aug 7, 2007 11:40:51 AM com.im.df.impl.db.dao.DFDaoSupport addColumn
INFO: Added column FIELD_513 VARCHAR(1000) to table NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:51 AM com.im.df.core.DatabasePlatform clearTableInfo
INFO: Cleared table info from cache for NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:51 AM com.im.df.core.DatabasePlatform buildTableInfo
INFO: Building TableInfo for table NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:51 AM com.im.df.impl.db.dao.DFDaoSupport addColumn
INFO: Added column FIELD_514 VARCHAR(1000) to table NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:51 AM com.im.df.core.DatabasePlatform clearTableInfo
INFO: Cleared table info from cache for NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:52 AM com.im.df.core.DatabasePlatform buildTableInfo
INFO: Building TableInfo for table NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:52 AM com.im.df.impl.db.dao.DFDaoSupport addColumn
INFO: Added column FIELD_515 VARCHAR(1000) to table NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:52 AM com.im.df.core.DatabasePlatform clearTableInfo
INFO: Cleared table info from cache for NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:52 AM com.im.df.core.DatabasePlatform buildTableInfo
INFO: Building TableInfo for table NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:53 AM com.im.df.impl.db.dao.DFDaoSupport addColumn
INFO: Added column FIELD_516 VARCHAR(1000) to table NEW_STANDARD_ENTITY
*********** Exception occurred ************ at 11:40 AM on Aug 7, 2007
java.lang.ArrayIndexOutOfBoundsException
Aug 7, 2007 11:40:53 AM com.im.df.core.DatabasePlatform clearTableInfo
INFO: Cleared table info from cache for NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:53 AM com.im.df.core.DatabasePlatform buildTableInfo
INFO: Building TableInfo for table NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:55 AM com.im.df.impl.db.dao.DFDaoSupport addColumn
INFO: Added column FIELD_517 VARCHAR(1000) to table NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:55 AM com.im.df.core.DatabasePlatform clearTableInfo
INFO: Cleared table info from cache for NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:55 AM com.im.df.core.DatabasePlatform buildTableInfo
INFO: Building TableInfo for table NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:56 AM com.im.df.impl.db.dao.DFDaoSupport addColumn
INFO: Added column FIELD_518 VARCHAR(1000) to table NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:56 AM com.im.df.core.DatabasePlatform clearTableInfo
INFO: Cleared table info from cache for NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:56 AM com.im.df.core.DatabasePlatform buildTableInfo
INFO: Building TableInfo for table NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:57 AM com.im.df.impl.db.dao.DFDaoSupport addColumn
INFO: Added column FIELD_519 VARCHAR(1000) to table NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:57 AM com.im.df.core.DatabasePlatform clearTableInfo
INFO: Cleared table info from cache for NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:57 AM com.im.df.core.DatabasePlatform buildTableInfo
INFO: Building TableInfo for table NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:58 AM com.im.df.impl.db.dao.DFDaoSupport addColumn
INFO: Added column FIELD_520 VARCHAR(1000) to table NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:58 AM com.im.df.core.DatabasePlatform clearTableInfo
INFO: Cleared table info from cache for NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:58 AM com.im.df.core.DatabasePlatform buildTableInfo
INFO: Building TableInfo for table NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:59 AM com.im.df.impl.db.dao.DFDaoSupport addColumn
INFO: Added column FIELD_521 VARCHAR(1000) to table NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:59 AM com.im.df.core.DatabasePlatform clearTableInfo
INFO: Cleared table info from cache for NEW_STANDARD_ENTITY
Aug 7, 2007 11:40:59 AM com.im.df.core.DatabasePlatform buildTableInfo
INFO: Building TableInfo for table NEW_STANDARD_ENTITY
Aug 7, 2007 11:41:01 AM com.im.df.impl.db.dao.DFDaoSupport addColumn
INFO: Added column FIELD_522 VARCHAR(1000) to table NEW_STANDARD_ENTITY
Aug 7, 2007 11:41:01 AM com.im.df.core.DatabasePlatform clearTableInfo
INFO: Cleared table info from cache for NEW_STANDARD_ENTITY
Aug 7, 2007 11:41:01 AM com.im.df.core.DatabasePlatform buildTableInfo
INFO: Building TableInfo for table NEW_STANDARD_ENTITY
Aug 7, 2007 11:41:02 AM com.im.df.impl.db.dao.DFDaoSupport addColumn
INFO: Added column FIELD_523 VARCHAR(1000) to table NEW_STANDARD_ENTITY
Aug 7, 2007 11:41:02 AM com.im.df.core.DatabasePlatform clearTableInfo
INFO: Cleared table info from cache for NEW_STANDARD_ENTITY
Aug 7, 2007 11:41:02 AM com.im.df.core.DatabasePlatform buildTableInfo
INFO: Building TableInfo for table NEW_STANDARD_ENTITY
Aug 7, 2007 11:41:02 AM com.im.df.impl.db.dao.DFDaoSupport addColumn
INFO: Added column FIELD_524 VARCHAR(1000) to table NEW_STANDARD_ENTITY
Aug 7, 2007 11:41:02 AM com.im.df.core.DatabasePlatform clearTableInfo
INFO: Cleared table info from cache for NEW_STANDARD_ENTITY
Aug 7, 2007 11:41:02 AM com.im.df.core.DatabasePlatform buildTableInfo
INFO: Building TableInfo for table NEW_STANDARD_ENTITY
*********** Exception occurred ************ at 11:41 AM on Aug 7, 2007
java.lang.ArrayIndexOutOfBoundsException
Aug 7, 2007 11:41:03 AM com.im.df.impl.db.dao.DFDaoSupport addColumn
INFO: Added column FIELD_525 VARCHAR(1000) to table NEW_STANDARD_ENTITY
User 677b9c22ff
20-08-2007 23:48:01
Hi,
the maximum number for columns is an old IBM DB heritage,
apache derby 1012 "maximum number of columns
http://svn.apache.org/viewvc?view=rev&revision=
Tobias
21 package org.apache.derby.iapi.reference;
22
23 public interface Limits
24 {
25 /**
26 * Various fixed Limits. DB2 related limits are prefixed with "DB2_".
27 */
28
29 public static final int DB2_MAX_TRIGGER_RECURSION = 16; /* Maximum nesting level for triggers */
30
31 /** Maximum number of indexes on a table */
32 public static final int DB2_MAX_INDEXES_ON_TABLE = 32767;
33 /* Maximum number of columns in a table */
34 public static final int DB2_MAX_COLUMNS_IN_TABLE = 1012;
35
36 /* Maximum number of columns in a view */
37 public static final int DB2_MAX_COLUMNS_IN_VIEW = 5000;
38
39 /* Maximum number of parameters in a stored procedure */
40 public static final int DB2_MAX_PARAMS_IN_STORED_PROCEDURE = 90;
41
42 /* Maximum number of elements in a select list */
43 public static final int DB2_MAX_ELEMENTS_IN_SELECT_LIST = 1012;
44 /* Maximum number of columns in a group by list */
45 public static final int DB2_MAX_ELEMENTS_IN_GROUP_BY = 32677;
46 /* Maximum number of columns in an order by list */
47 public static final int DB2_MAX_ELEMENTS_IN_ORDER_BY = 1012;
48
49 // Max length for an exception parameter string over CCC server.
50 public static final int DB2_CCC_MAX_EXCEPTION_PARAM_LENGTH = 70;
51
52 // Warning. Changing this value will affect upgrade and the creation of the
53 // SQLCAMESSAGE procedure. See org.apache.derby.impl.sql.catalog.
54 public static final int DB2_JCC_MAX_EXCEPTION_PARAM_LENGTH = 2400;
55
ChemAxon fa971619eb
20-08-2007 23:48:15
The first error is as expected. The Derby database has an in built limit of 1024 columns, so you get an error when you try to create the 1025th. Relational databaseses are fundamentally designed for relatively small numbers of columns and large numers of rows.
The second one is more interesting. I think it comes from the query panel, trying to display too many fields in in Java component that displays the query tree (e.g. an inbuilt java linitation). I may be wrong, but try to switch the query panel to advanced mode, where you have to build the query field by field and it may avoid the problem.
As for the slow perfomance, i think this is probably because the database file is needing to be re-organised to tale the extra column(s), so lots of disk IO is needed. Maybe wrong on this, but need to investigate.
Tim