[jira] Commented: (DERBY-1577) DatabaseMetaData.getIndexInfo() returns internal names
[ http://issues.apache.org/jira/browse/DERBY-1577?page=comments#action_12427514 ] Kathey Marsden commented on DERBY-1577: --- Yes, I like DERBY-1669 better as it states the core problem that there is not a way to differentiate between a normal index, a primary key or foreign key. It sounds like we need to discuss the best technical solution on the derby-dev alias. We can leave this one open too until that is decided, but I think it is better to branch the discussion off of DERBY-1669 as it is states the problem to be solved most clearly. (Just for clarification since folks on this list don't know Jason, Jason is one of the frontline Cloudscape support folks at IBM. ) DatabaseMetaData.getIndexInfo() returns internal names -- Key: DERBY-1577 URL: http://issues.apache.org/jira/browse/DERBY-1577 Project: Derby Issue Type: Bug Components: JDBC Affects Versions: 10.1.3.1 Environment: Windows 2003 Server Reporter: Jorg Janke Problem: - We inquire the meta data of the database and then dynamically update the database to its target date (e.g. add/modify tables, columns, indexes, constraints, ...) via (standard) DDL. When requesting the indexes for a table, we get the internal name, not the index name. When (re-) the submitting the DDL ALTER TABLE AD_ACCESSLOG ADD CONSTRAINT AD_ACCESSLOG_KEY PRIMARY KEY (AD_ACCESSLOG_ID) I get the error message Constraints 'AD_ACCESSLOG_KEY' and 'AD_ACCESSLOG_KEY' have the same set of columns, which is not allowed. Technical Description - Problem is that the Derby implementation of DatabaseMetaData.getIndexInfo() returns the internal (conglomerate) name rather then the real name of the index. I checked - in org.apache.derby.client.am.DatabaseMetaData.getIndexInfoX(..) you call the function SYSIBM.SQLSTATISTICS(?,?,?,?,?,?) - which returns the wrong data. Results from getIndexInfo(..) 0: TABLE_CAT=, TABLE_SCHEM=COMPIERE, TABLE_NAME=AD_ACCESSLOG, NON_UNIQUE=0, INDEX_QUALIFIER=, INDEX_NAME=SQL060709062929330, TYPE=3, ORDINAL_POSITION=1, COLUMN_NAME=AD_ACCESSLOG_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null 1: TABLE_CAT=, TABLE_SCHEM=COMPIERE, TABLE_NAME=AD_ACCESSLOG, NON_UNIQUE=1, INDEX_QUALIFIER=, INDEX_NAME=SQL060716064852400, TYPE=3, ORDINAL_POSITION=1, COLUMN_NAME=AD_COLUMN_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null Results from getImportedKeys(..) 0: PKTABLE_CAT=, PKTABLE_SCHEM=COMPIERE, PKTABLE_NAME=AD_COLUMN, PKCOLUMN_NAME=AD_COLUMN_ID, FKTABLE_CAT=, FKTABLE_SCHEM=COMPIERE, FKTABLE_NAME=AD_ACCESSLOG, FKCOLUMN_NAME=AD_COLUMN_ID, KEY_SEQ=1, UPDATE_RULE=3, DELETE_RULE=0, FK_NAME=ADCOLUMN_ADACCESSLOG, PK_NAME=AD_COLUMN_KEY, DEFERRABILITY=7 The problem would be solved, if in addition to the (internal type 3) index info you would provide the index type 1/2 info with the resuly of 0: .. INDEX_NAME=AD_ACCESSLOG_KEY, TYPE=1, ORDINAL_POSITION=1, COLUMN_NAME=AD_ACCESSLOG_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null 1: .. INDEX_NAME=ADCOLUMN_ADACCESSLOG, TYPE=3, ORDINAL_POSITION=1, COLUMN_NAME=AD_COLUMN_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null The original table definition is: CREATE TABLE AD_ACCESSLOG ( AD_ACCESSLOG_ID DECIMAL(10,0) NOT NULL, AD_CLIENT_IDDECIMAL(10,0) NOT NULL, AD_ORG_ID DECIMAL(10,0) NOT NULL, ISACTIVECHAR(1)DEFAULT 'Y' NOT NULL, CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, CREATEDBY DECIMAL(10,0) NOT NULL, UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, UPDATEDBY DECIMAL(10,0) NOT NULL, AD_TABLE_ID DECIMAL(10,0) NULL, AD_COLUMN_IDDECIMAL(10,0) NULL, RECORD_ID DECIMAL(10,0) NULL, CONSTRAINT AD_ACCESSLOG_KEY PRIMARY KEY (AD_ACCESSLOG_ID), CONSTRAINT ADCOLUMN_ADACCESSLOG FOREIGN KEY (AD_COLUMN_ID) REFERENCES AD_COLUMN (AD_COLUMN_ID) ) --- Note that you create an index for a constraint - that is fine, but it would be helpful to again not get the internal name, but the external. Index 'SQL060716064852400' was created to enforce constraint 'ADCOLUMN_ADACCESSLOG'. It can only be dropped by dropping the constraint. - DROP INDEX SQL060716064852400 --- Help requested: --- If you please could fix it and tell me if I could find/update/fix the function SYSIBM.SQLSTATISTICS. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information
[jira] Commented: (DERBY-1577) DatabaseMetaData.getIndexInfo() returns internal names
[ http://issues.apache.org/jira/browse/DERBY-1577?page=comments#action_12427392 ] Jorg Janke commented on DERBY-1577: --- A work-around is to use DatabaseMetaData.getPrimaryKeys(). The cause is that in most database systems, the constraint name is also used for the supporting index. The follow up issue is that Cloudscape does not allow to differentiate between real indexes and indexes created to support a constraint. https://issues.apache.org/jira/browse/DERBY-1669 DatabaseMetaData.getIndexInfo() returns internal names -- Key: DERBY-1577 URL: http://issues.apache.org/jira/browse/DERBY-1577 Project: Derby Issue Type: Bug Components: JDBC Affects Versions: 10.1.3.1 Environment: Windows 2003 Server Reporter: Jorg Janke Problem: - We inquire the meta data of the database and then dynamically update the database to its target date (e.g. add/modify tables, columns, indexes, constraints, ...) via (standard) DDL. When requesting the indexes for a table, we get the internal name, not the index name. When (re-) the submitting the DDL ALTER TABLE AD_ACCESSLOG ADD CONSTRAINT AD_ACCESSLOG_KEY PRIMARY KEY (AD_ACCESSLOG_ID) I get the error message Constraints 'AD_ACCESSLOG_KEY' and 'AD_ACCESSLOG_KEY' have the same set of columns, which is not allowed. Technical Description - Problem is that the Derby implementation of DatabaseMetaData.getIndexInfo() returns the internal (conglomerate) name rather then the real name of the index. I checked - in org.apache.derby.client.am.DatabaseMetaData.getIndexInfoX(..) you call the function SYSIBM.SQLSTATISTICS(?,?,?,?,?,?) - which returns the wrong data. Results from getIndexInfo(..) 0: TABLE_CAT=, TABLE_SCHEM=COMPIERE, TABLE_NAME=AD_ACCESSLOG, NON_UNIQUE=0, INDEX_QUALIFIER=, INDEX_NAME=SQL060709062929330, TYPE=3, ORDINAL_POSITION=1, COLUMN_NAME=AD_ACCESSLOG_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null 1: TABLE_CAT=, TABLE_SCHEM=COMPIERE, TABLE_NAME=AD_ACCESSLOG, NON_UNIQUE=1, INDEX_QUALIFIER=, INDEX_NAME=SQL060716064852400, TYPE=3, ORDINAL_POSITION=1, COLUMN_NAME=AD_COLUMN_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null Results from getImportedKeys(..) 0: PKTABLE_CAT=, PKTABLE_SCHEM=COMPIERE, PKTABLE_NAME=AD_COLUMN, PKCOLUMN_NAME=AD_COLUMN_ID, FKTABLE_CAT=, FKTABLE_SCHEM=COMPIERE, FKTABLE_NAME=AD_ACCESSLOG, FKCOLUMN_NAME=AD_COLUMN_ID, KEY_SEQ=1, UPDATE_RULE=3, DELETE_RULE=0, FK_NAME=ADCOLUMN_ADACCESSLOG, PK_NAME=AD_COLUMN_KEY, DEFERRABILITY=7 The problem would be solved, if in addition to the (internal type 3) index info you would provide the index type 1/2 info with the resuly of 0: .. INDEX_NAME=AD_ACCESSLOG_KEY, TYPE=1, ORDINAL_POSITION=1, COLUMN_NAME=AD_ACCESSLOG_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null 1: .. INDEX_NAME=ADCOLUMN_ADACCESSLOG, TYPE=3, ORDINAL_POSITION=1, COLUMN_NAME=AD_COLUMN_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null The original table definition is: CREATE TABLE AD_ACCESSLOG ( AD_ACCESSLOG_ID DECIMAL(10,0) NOT NULL, AD_CLIENT_IDDECIMAL(10,0) NOT NULL, AD_ORG_ID DECIMAL(10,0) NOT NULL, ISACTIVECHAR(1)DEFAULT 'Y' NOT NULL, CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, CREATEDBY DECIMAL(10,0) NOT NULL, UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, UPDATEDBY DECIMAL(10,0) NOT NULL, AD_TABLE_ID DECIMAL(10,0) NULL, AD_COLUMN_IDDECIMAL(10,0) NULL, RECORD_ID DECIMAL(10,0) NULL, CONSTRAINT AD_ACCESSLOG_KEY PRIMARY KEY (AD_ACCESSLOG_ID), CONSTRAINT ADCOLUMN_ADACCESSLOG FOREIGN KEY (AD_COLUMN_ID) REFERENCES AD_COLUMN (AD_COLUMN_ID) ) --- Note that you create an index for a constraint - that is fine, but it would be helpful to again not get the internal name, but the external. Index 'SQL060716064852400' was created to enforce constraint 'ADCOLUMN_ADACCESSLOG'. It can only be dropped by dropping the constraint. - DROP INDEX SQL060716064852400 --- Help requested: --- If you please could fix it and tell me if I could find/update/fix the function SYSIBM.SQLSTATISTICS. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1577) DatabaseMetaData.getIndexInfo() returns internal names
[ http://issues.apache.org/jira/browse/DERBY-1577?page=comments#action_12427405 ] Kathey Marsden commented on DERBY-1577: --- Thanks Jorg, I am glad you were able to find a solution for your application. Would it be ok to close out this issue then? Since the backing indexes do indeed have different (generated) names, I think the current behaviour is correct that Derby should return them. Kathey DatabaseMetaData.getIndexInfo() returns internal names -- Key: DERBY-1577 URL: http://issues.apache.org/jira/browse/DERBY-1577 Project: Derby Issue Type: Bug Components: JDBC Affects Versions: 10.1.3.1 Environment: Windows 2003 Server Reporter: Jorg Janke Problem: - We inquire the meta data of the database and then dynamically update the database to its target date (e.g. add/modify tables, columns, indexes, constraints, ...) via (standard) DDL. When requesting the indexes for a table, we get the internal name, not the index name. When (re-) the submitting the DDL ALTER TABLE AD_ACCESSLOG ADD CONSTRAINT AD_ACCESSLOG_KEY PRIMARY KEY (AD_ACCESSLOG_ID) I get the error message Constraints 'AD_ACCESSLOG_KEY' and 'AD_ACCESSLOG_KEY' have the same set of columns, which is not allowed. Technical Description - Problem is that the Derby implementation of DatabaseMetaData.getIndexInfo() returns the internal (conglomerate) name rather then the real name of the index. I checked - in org.apache.derby.client.am.DatabaseMetaData.getIndexInfoX(..) you call the function SYSIBM.SQLSTATISTICS(?,?,?,?,?,?) - which returns the wrong data. Results from getIndexInfo(..) 0: TABLE_CAT=, TABLE_SCHEM=COMPIERE, TABLE_NAME=AD_ACCESSLOG, NON_UNIQUE=0, INDEX_QUALIFIER=, INDEX_NAME=SQL060709062929330, TYPE=3, ORDINAL_POSITION=1, COLUMN_NAME=AD_ACCESSLOG_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null 1: TABLE_CAT=, TABLE_SCHEM=COMPIERE, TABLE_NAME=AD_ACCESSLOG, NON_UNIQUE=1, INDEX_QUALIFIER=, INDEX_NAME=SQL060716064852400, TYPE=3, ORDINAL_POSITION=1, COLUMN_NAME=AD_COLUMN_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null Results from getImportedKeys(..) 0: PKTABLE_CAT=, PKTABLE_SCHEM=COMPIERE, PKTABLE_NAME=AD_COLUMN, PKCOLUMN_NAME=AD_COLUMN_ID, FKTABLE_CAT=, FKTABLE_SCHEM=COMPIERE, FKTABLE_NAME=AD_ACCESSLOG, FKCOLUMN_NAME=AD_COLUMN_ID, KEY_SEQ=1, UPDATE_RULE=3, DELETE_RULE=0, FK_NAME=ADCOLUMN_ADACCESSLOG, PK_NAME=AD_COLUMN_KEY, DEFERRABILITY=7 The problem would be solved, if in addition to the (internal type 3) index info you would provide the index type 1/2 info with the resuly of 0: .. INDEX_NAME=AD_ACCESSLOG_KEY, TYPE=1, ORDINAL_POSITION=1, COLUMN_NAME=AD_ACCESSLOG_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null 1: .. INDEX_NAME=ADCOLUMN_ADACCESSLOG, TYPE=3, ORDINAL_POSITION=1, COLUMN_NAME=AD_COLUMN_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null The original table definition is: CREATE TABLE AD_ACCESSLOG ( AD_ACCESSLOG_ID DECIMAL(10,0) NOT NULL, AD_CLIENT_IDDECIMAL(10,0) NOT NULL, AD_ORG_ID DECIMAL(10,0) NOT NULL, ISACTIVECHAR(1)DEFAULT 'Y' NOT NULL, CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, CREATEDBY DECIMAL(10,0) NOT NULL, UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, UPDATEDBY DECIMAL(10,0) NOT NULL, AD_TABLE_ID DECIMAL(10,0) NULL, AD_COLUMN_IDDECIMAL(10,0) NULL, RECORD_ID DECIMAL(10,0) NULL, CONSTRAINT AD_ACCESSLOG_KEY PRIMARY KEY (AD_ACCESSLOG_ID), CONSTRAINT ADCOLUMN_ADACCESSLOG FOREIGN KEY (AD_COLUMN_ID) REFERENCES AD_COLUMN (AD_COLUMN_ID) ) --- Note that you create an index for a constraint - that is fine, but it would be helpful to again not get the internal name, but the external. Index 'SQL060716064852400' was created to enforce constraint 'ADCOLUMN_ADACCESSLOG'. It can only be dropped by dropping the constraint. - DROP INDEX SQL060716064852400 --- Help requested: --- If you please could fix it and tell me if I could find/update/fix the function SYSIBM.SQLSTATISTICS. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1577) DatabaseMetaData.getIndexInfo() returns internal names
[ http://issues.apache.org/jira/browse/DERBY-1577?page=comments#action_12427409 ] Daniel John Debrunner commented on DERBY-1577: -- Is this the same issue as DERBY-543? DatabaseMetaData.getIndexInfo provides misleading/confusing information for indexes backing constraints. DatabaseMetaData.getIndexInfo() returns internal names -- Key: DERBY-1577 URL: http://issues.apache.org/jira/browse/DERBY-1577 Project: Derby Issue Type: Bug Components: JDBC Affects Versions: 10.1.3.1 Environment: Windows 2003 Server Reporter: Jorg Janke Problem: - We inquire the meta data of the database and then dynamically update the database to its target date (e.g. add/modify tables, columns, indexes, constraints, ...) via (standard) DDL. When requesting the indexes for a table, we get the internal name, not the index name. When (re-) the submitting the DDL ALTER TABLE AD_ACCESSLOG ADD CONSTRAINT AD_ACCESSLOG_KEY PRIMARY KEY (AD_ACCESSLOG_ID) I get the error message Constraints 'AD_ACCESSLOG_KEY' and 'AD_ACCESSLOG_KEY' have the same set of columns, which is not allowed. Technical Description - Problem is that the Derby implementation of DatabaseMetaData.getIndexInfo() returns the internal (conglomerate) name rather then the real name of the index. I checked - in org.apache.derby.client.am.DatabaseMetaData.getIndexInfoX(..) you call the function SYSIBM.SQLSTATISTICS(?,?,?,?,?,?) - which returns the wrong data. Results from getIndexInfo(..) 0: TABLE_CAT=, TABLE_SCHEM=COMPIERE, TABLE_NAME=AD_ACCESSLOG, NON_UNIQUE=0, INDEX_QUALIFIER=, INDEX_NAME=SQL060709062929330, TYPE=3, ORDINAL_POSITION=1, COLUMN_NAME=AD_ACCESSLOG_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null 1: TABLE_CAT=, TABLE_SCHEM=COMPIERE, TABLE_NAME=AD_ACCESSLOG, NON_UNIQUE=1, INDEX_QUALIFIER=, INDEX_NAME=SQL060716064852400, TYPE=3, ORDINAL_POSITION=1, COLUMN_NAME=AD_COLUMN_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null Results from getImportedKeys(..) 0: PKTABLE_CAT=, PKTABLE_SCHEM=COMPIERE, PKTABLE_NAME=AD_COLUMN, PKCOLUMN_NAME=AD_COLUMN_ID, FKTABLE_CAT=, FKTABLE_SCHEM=COMPIERE, FKTABLE_NAME=AD_ACCESSLOG, FKCOLUMN_NAME=AD_COLUMN_ID, KEY_SEQ=1, UPDATE_RULE=3, DELETE_RULE=0, FK_NAME=ADCOLUMN_ADACCESSLOG, PK_NAME=AD_COLUMN_KEY, DEFERRABILITY=7 The problem would be solved, if in addition to the (internal type 3) index info you would provide the index type 1/2 info with the resuly of 0: .. INDEX_NAME=AD_ACCESSLOG_KEY, TYPE=1, ORDINAL_POSITION=1, COLUMN_NAME=AD_ACCESSLOG_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null 1: .. INDEX_NAME=ADCOLUMN_ADACCESSLOG, TYPE=3, ORDINAL_POSITION=1, COLUMN_NAME=AD_COLUMN_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null The original table definition is: CREATE TABLE AD_ACCESSLOG ( AD_ACCESSLOG_ID DECIMAL(10,0) NOT NULL, AD_CLIENT_IDDECIMAL(10,0) NOT NULL, AD_ORG_ID DECIMAL(10,0) NOT NULL, ISACTIVECHAR(1)DEFAULT 'Y' NOT NULL, CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, CREATEDBY DECIMAL(10,0) NOT NULL, UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, UPDATEDBY DECIMAL(10,0) NOT NULL, AD_TABLE_ID DECIMAL(10,0) NULL, AD_COLUMN_IDDECIMAL(10,0) NULL, RECORD_ID DECIMAL(10,0) NULL, CONSTRAINT AD_ACCESSLOG_KEY PRIMARY KEY (AD_ACCESSLOG_ID), CONSTRAINT ADCOLUMN_ADACCESSLOG FOREIGN KEY (AD_COLUMN_ID) REFERENCES AD_COLUMN (AD_COLUMN_ID) ) --- Note that you create an index for a constraint - that is fine, but it would be helpful to again not get the internal name, but the external. Index 'SQL060716064852400' was created to enforce constraint 'ADCOLUMN_ADACCESSLOG'. It can only be dropped by dropping the constraint. - DROP INDEX SQL060716064852400 --- Help requested: --- If you please could fix it and tell me if I could find/update/fix the function SYSIBM.SQLSTATISTICS. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1577) DatabaseMetaData.getIndexInfo() returns internal names
[ http://issues.apache.org/jira/browse/DERBY-1577?page=comments#action_12427428 ] Jorg Janke commented on DERBY-1577: --- Yes DERBY-543 is basically the same - my suggestion would to use the same name for the index as for the constraint. But, that the type (statistic, normal, other, ..) of index is unfortunately the next hurdle. Basically the index info do not allow me to differentiate between a normal index, a primary key or forign key - So the issue is NOT solved - Jason asked me to close this one and open a new bug for this issue - DERBY-1669 DatabaseMetaData.getIndexInfo() returns internal names -- Key: DERBY-1577 URL: http://issues.apache.org/jira/browse/DERBY-1577 Project: Derby Issue Type: Bug Components: JDBC Affects Versions: 10.1.3.1 Environment: Windows 2003 Server Reporter: Jorg Janke Problem: - We inquire the meta data of the database and then dynamically update the database to its target date (e.g. add/modify tables, columns, indexes, constraints, ...) via (standard) DDL. When requesting the indexes for a table, we get the internal name, not the index name. When (re-) the submitting the DDL ALTER TABLE AD_ACCESSLOG ADD CONSTRAINT AD_ACCESSLOG_KEY PRIMARY KEY (AD_ACCESSLOG_ID) I get the error message Constraints 'AD_ACCESSLOG_KEY' and 'AD_ACCESSLOG_KEY' have the same set of columns, which is not allowed. Technical Description - Problem is that the Derby implementation of DatabaseMetaData.getIndexInfo() returns the internal (conglomerate) name rather then the real name of the index. I checked - in org.apache.derby.client.am.DatabaseMetaData.getIndexInfoX(..) you call the function SYSIBM.SQLSTATISTICS(?,?,?,?,?,?) - which returns the wrong data. Results from getIndexInfo(..) 0: TABLE_CAT=, TABLE_SCHEM=COMPIERE, TABLE_NAME=AD_ACCESSLOG, NON_UNIQUE=0, INDEX_QUALIFIER=, INDEX_NAME=SQL060709062929330, TYPE=3, ORDINAL_POSITION=1, COLUMN_NAME=AD_ACCESSLOG_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null 1: TABLE_CAT=, TABLE_SCHEM=COMPIERE, TABLE_NAME=AD_ACCESSLOG, NON_UNIQUE=1, INDEX_QUALIFIER=, INDEX_NAME=SQL060716064852400, TYPE=3, ORDINAL_POSITION=1, COLUMN_NAME=AD_COLUMN_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null Results from getImportedKeys(..) 0: PKTABLE_CAT=, PKTABLE_SCHEM=COMPIERE, PKTABLE_NAME=AD_COLUMN, PKCOLUMN_NAME=AD_COLUMN_ID, FKTABLE_CAT=, FKTABLE_SCHEM=COMPIERE, FKTABLE_NAME=AD_ACCESSLOG, FKCOLUMN_NAME=AD_COLUMN_ID, KEY_SEQ=1, UPDATE_RULE=3, DELETE_RULE=0, FK_NAME=ADCOLUMN_ADACCESSLOG, PK_NAME=AD_COLUMN_KEY, DEFERRABILITY=7 The problem would be solved, if in addition to the (internal type 3) index info you would provide the index type 1/2 info with the resuly of 0: .. INDEX_NAME=AD_ACCESSLOG_KEY, TYPE=1, ORDINAL_POSITION=1, COLUMN_NAME=AD_ACCESSLOG_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null 1: .. INDEX_NAME=ADCOLUMN_ADACCESSLOG, TYPE=3, ORDINAL_POSITION=1, COLUMN_NAME=AD_COLUMN_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null The original table definition is: CREATE TABLE AD_ACCESSLOG ( AD_ACCESSLOG_ID DECIMAL(10,0) NOT NULL, AD_CLIENT_IDDECIMAL(10,0) NOT NULL, AD_ORG_ID DECIMAL(10,0) NOT NULL, ISACTIVECHAR(1)DEFAULT 'Y' NOT NULL, CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, CREATEDBY DECIMAL(10,0) NOT NULL, UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, UPDATEDBY DECIMAL(10,0) NOT NULL, AD_TABLE_ID DECIMAL(10,0) NULL, AD_COLUMN_IDDECIMAL(10,0) NULL, RECORD_ID DECIMAL(10,0) NULL, CONSTRAINT AD_ACCESSLOG_KEY PRIMARY KEY (AD_ACCESSLOG_ID), CONSTRAINT ADCOLUMN_ADACCESSLOG FOREIGN KEY (AD_COLUMN_ID) REFERENCES AD_COLUMN (AD_COLUMN_ID) ) --- Note that you create an index for a constraint - that is fine, but it would be helpful to again not get the internal name, but the external. Index 'SQL060716064852400' was created to enforce constraint 'ADCOLUMN_ADACCESSLOG'. It can only be dropped by dropping the constraint. - DROP INDEX SQL060716064852400 --- Help requested: --- If you please could fix it and tell me if I could find/update/fix the function SYSIBM.SQLSTATISTICS. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1577) DatabaseMetaData.getIndexInfo() returns internal names
[ http://issues.apache.org/jira/browse/DERBY-1577?page=comments#action_12424942 ] Kathey Marsden commented on DERBY-1577: --- I think that the name of the backing index created is in fact different than the primary key, so I think it is correct that getIndexInfo() returns the generated index name.Is it really a bug? If you need the primary key and the index name to match, I wonder if RENAME INDEX might help: http://db.apache.org/derby/docs/10.1/ref/rrefsqlj95598.html DatabaseMetaData.getIndexInfo() returns internal names -- Key: DERBY-1577 URL: http://issues.apache.org/jira/browse/DERBY-1577 Project: Derby Issue Type: Bug Components: JDBC Affects Versions: 10.1.3.1 Environment: Windows 2003 Server Reporter: Jorg Janke Problem: - We inquire the meta data of the database and then dynamically update the database to its target date (e.g. add/modify tables, columns, indexes, constraints, ...) via (standard) DDL. When requesting the indexes for a table, we get the internal name, not the index name. When (re-) the submitting the DDL ALTER TABLE AD_ACCESSLOG ADD CONSTRAINT AD_ACCESSLOG_KEY PRIMARY KEY (AD_ACCESSLOG_ID) I get the error message Constraints 'AD_ACCESSLOG_KEY' and 'AD_ACCESSLOG_KEY' have the same set of columns, which is not allowed. Technical Description - Problem is that the Derby implementation of DatabaseMetaData.getIndexInfo() returns the internal (conglomerate) name rather then the real name of the index. I checked - in org.apache.derby.client.am.DatabaseMetaData.getIndexInfoX(..) you call the function SYSIBM.SQLSTATISTICS(?,?,?,?,?,?) - which returns the wrong data. Results from getIndexInfo(..) 0: TABLE_CAT=, TABLE_SCHEM=COMPIERE, TABLE_NAME=AD_ACCESSLOG, NON_UNIQUE=0, INDEX_QUALIFIER=, INDEX_NAME=SQL060709062929330, TYPE=3, ORDINAL_POSITION=1, COLUMN_NAME=AD_ACCESSLOG_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null 1: TABLE_CAT=, TABLE_SCHEM=COMPIERE, TABLE_NAME=AD_ACCESSLOG, NON_UNIQUE=1, INDEX_QUALIFIER=, INDEX_NAME=SQL060716064852400, TYPE=3, ORDINAL_POSITION=1, COLUMN_NAME=AD_COLUMN_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null Results from getImportedKeys(..) 0: PKTABLE_CAT=, PKTABLE_SCHEM=COMPIERE, PKTABLE_NAME=AD_COLUMN, PKCOLUMN_NAME=AD_COLUMN_ID, FKTABLE_CAT=, FKTABLE_SCHEM=COMPIERE, FKTABLE_NAME=AD_ACCESSLOG, FKCOLUMN_NAME=AD_COLUMN_ID, KEY_SEQ=1, UPDATE_RULE=3, DELETE_RULE=0, FK_NAME=ADCOLUMN_ADACCESSLOG, PK_NAME=AD_COLUMN_KEY, DEFERRABILITY=7 The problem would be solved, if in addition to the (internal type 3) index info you would provide the index type 1/2 info with the resuly of 0: .. INDEX_NAME=AD_ACCESSLOG_KEY, TYPE=1, ORDINAL_POSITION=1, COLUMN_NAME=AD_ACCESSLOG_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null 1: .. INDEX_NAME=ADCOLUMN_ADACCESSLOG, TYPE=3, ORDINAL_POSITION=1, COLUMN_NAME=AD_COLUMN_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, FILTER_CONDITION=null The original table definition is: CREATE TABLE AD_ACCESSLOG ( AD_ACCESSLOG_ID DECIMAL(10,0) NOT NULL, AD_CLIENT_IDDECIMAL(10,0) NOT NULL, AD_ORG_ID DECIMAL(10,0) NOT NULL, ISACTIVECHAR(1)DEFAULT 'Y' NOT NULL, CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, CREATEDBY DECIMAL(10,0) NOT NULL, UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, UPDATEDBY DECIMAL(10,0) NOT NULL, AD_TABLE_ID DECIMAL(10,0) NULL, AD_COLUMN_IDDECIMAL(10,0) NULL, RECORD_ID DECIMAL(10,0) NULL, CONSTRAINT AD_ACCESSLOG_KEY PRIMARY KEY (AD_ACCESSLOG_ID), CONSTRAINT ADCOLUMN_ADACCESSLOG FOREIGN KEY (AD_COLUMN_ID) REFERENCES AD_COLUMN (AD_COLUMN_ID) ) --- Note that you create an index for a constraint - that is fine, but it would be helpful to again not get the internal name, but the external. Index 'SQL060716064852400' was created to enforce constraint 'ADCOLUMN_ADACCESSLOG'. It can only be dropped by dropping the constraint. - DROP INDEX SQL060716064852400 --- Help requested: --- If you please could fix it and tell me if I could find/update/fix the function SYSIBM.SQLSTATISTICS. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
