Steffen Goeldner wrote:
>
> Attached are some results for the Oracle ODBC Driver 08.01.0704,
> generated with MS ODBC Test 03.00.2301 (Odbcte32.exe).
>
> The last result is wrong: tst.c2 should not appear in the result set.
For that test case, Microsoft's ODBC Driver for Oracle returns the
result set correctly. Their SQL looks like:
select to_char(null)
, pk.owner
, pk.table_name
, pc.column_name
, to_char(null)
, fk.owner
, fk.table_name
, fc.column_name
, pc.position
, 1
, decode(fk.delete_rule,'CASCADE',0,'NO ACTION', 1,1)
, fk.constraint_name
, pk.constraint_name
from all_constraints pk
, all_cons_columns pc
, all_constraints fk
, all_cons_columns fc
where pk.owner = pc.owner
and pk.constraint_name = pc.constraint_name
and fk.owner = fc.owner
and fk.constraint_name = fc.constraint_name
and pk.constraint_type = 'P'
and fk.constraint_type = 'R'
and pk.constraint_name = fk.r_constraint_name
and pk.owner = fk.r_owner
and pc.position = fc.position
and pk.owner like 'TST' escape '\'
and pk.table_name like 'P1' escape '\'
and fk.owner like 'TST' escape '\'
and fk.table_name like 'C1' escape '\'
order by 6, 7, 9
However:
- They use LIKE clauses, i.e. search pattern.
Their own ODBC spec does not allow search pattern.
- In the DELETE_RULE, they map 'NO ACTION' to 1, i.e. 'RESTRICT'.
To be fair, there is only a marginal difference between 'NO ACTION'
and 'RESTRICT'. And who knows, maybe Oracle's 'NO ACTION' really
means 'RESTRICT'?
- The UPDATE_RULE is similar: always 1, i.e. 'RESTRICT'.
- They omit the DEFERRABILITY column.
Steffen
