db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kathey Marsden (JIRA)" <j...@apache.org>
Subject [jira] Resolved: (DERBY-1577) DatabaseMetaData.getIndexInfo() returns internal names
Date Mon, 30 Jul 2007 19:55:53 GMT

     [ 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.


Mime
View raw message