Zeoslib Portal

Hallo @All

there seems to be a problem retrieving the name of the Primary Key in an ORACLE environment.
In the current implementation the Index name is retrieved by:

Select ...
FROM FROM ALL_INDEXES A, ALL_IND_COLUMNS B
WHERE ....
AND A.UNIQUENESS='UNIQUE'
AND A.GENERATED=''Y'''
AND A.INDEX_NAME LIKE 'SYS_%'

This may lead to a problem when the primary key is user generated and given a name other than 'SYS_*'.
In that situation ZEOS builds an update-where-clause containing all columns with potentional 'IS NULL'
statements.
As a result no record in the DB is updated.


Environment

File: ZDbcOracleMetadata.pas

function TZOracleDatabaseMetadata.UncachedGetPrimaryKeys(const Catalog: string;

Proposal to remove the WHERE-restriction "AND A.GENERATED=''Y'' AND A.INDEX_NAME LIKE ''SYS_%''"


SQL := 'SELECT NULL AS TABLE_CAT, A.OWNER AS TABLE_SCHEM, A.TABLE_NAME,'
+ ' B.COLUMN_NAME, B.COLUMN_POSITION AS KEY_SEQ, A.INDEX_NAME AS PK_NAME'
+ ' FROM ALL_INDEXES A, ALL_IND_COLUMNS B'
+ ' WHERE A.OWNER=B.INDEX_OWNER AND A.INDEX_NAME=B.INDEX_NAME'
+ ' AND A.TABLE_OWNER=B.TABLE_OWNER AND A.TABLE_NAME=B.TABLE_NAME'
+ ' AND A.UNIQUENESS=''UNIQUE'''
+ CreateExtraWhere
+ ' ORDER BY A.INDEX_NAME, B.COLUMN_POSITION';

Best regards
Chris

ZEOS Version 7.1.3-Stable
ORACLE 11.2.0.1
Delphi 2010