Steffen Goeldner wrote:
>
> I plan to provide Primary Key metadata in DBD::Oracle.
It's common practice to compare the behaviour and implementation
of a DBI driver with the corresponding ODBC driver.
In developement stage, it may be very helpful - sometimes.
Test case:
----------
create table tst1.tpk ( n number constraint pk1 primary key );
create table tst2.tpk ( n number constraint pk1 primary key );
MS ODBC Test:
-------------
SQLPrimaryKeys:
In:
CatalogName = SQL_NULL_HANDLE
SchemaName = "tst1"
TableName = "tpk"
Return:
SQL_SUCCESS = 0
Get Data All:
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "KEY_SEQ",
"PK_NAME"
<Null>, "TST1", "TPK", "N", 1,
"PK1"
<Null>, "TST1", "TPK", "N", 1,
"PK1"
2 rows fetched from 6 columns.
This nonsense is returned from Oracle ODBC 8.1.7.0.
They use the following SQL statement (inspected in v$sqlarea):
SELECT ''
, b.owner
, b.table_name
, b.column_name
, b.position
, b.constraint_name
FROM ALL_CONSTRAINTS a
, ALL_CONS_COLUMNS b
WHERE (
a.constraint_name = b.constraint_name
AND a.constraint_type = 'P'
AND b.table_name = 'TPK'
AND b.owner = 'TST1'
)
ORDER BY b.owner, b.table_name, b.position
Oracle ODBC 8.1.7.3 does a better job:
SELECT /*+ RULE */
''
, b.owner
, b.table_name
, b.column_name
, b.position
, b.constraint_name
FROM ALL_CONSTRAINTS a
, ALL_CONS_COLUMNS b
WHERE (
UPPER(b.table_name) = UPPER('TPK')
and UPPER(b.owner) = UPPER('TST1')
)
AND (
UPPER(a.table_name) = UPPER('TPK')
and UPPER(a.owner) = UPPER('TST1')
and a.constraint_type = 'P'
)
AND (a.constraint_name = b.constraint_name)
ORDER BY b.owner, b.table_name, b.position
Steffen G�ldner
