You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Kathey Marsden (JIRA)" <ji...@apache.org> on 2007/07/30 21:55:53 UTC
[jira] Resolved: (DERBY-1577) DatabaseMetaData.getIndexInfo()
returns internal names
[ https://issues.apache.org/jira/browse/DERBY-1577?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden resolved DERBY-1577.
-----------------------------------
Resolution: Duplicate
duplicate of DERBY-1669
> DatabaseMetaData.getIndexInfo() returns internal names
> ------------------------------------------------------
>
> Key: DERBY-1577
> URL: https://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_ID DECIMAL(10,0) NOT NULL,
> AD_ORG_ID DECIMAL(10,0) NOT NULL,
> ISACTIVE CHAR(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_ID DECIMAL(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.
-
You can reply to this email to add a comment to the issue online.