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.
I created a TAR at Oracle MetaLink. They accepted it as a bug:
BUG# 2008554 SQLFOREIGNKEY() BRINGS BACK WRONG RESULTS
They'll fix that in the next release (8.1.7.5).
Another FK related bug, not covered by my test cases, is:
BUG# 1271663 CONSTRAINT TYPE 'ON DELETE SET NULL' NOT DISPLAYED
CORRECTLY IN DBA_CONSTRAINTS
Here, the Oracle data dictionary views are wrong.
As a workaround, one could use the base tables (SYS.CDEF$, ...).
But this is not very portable.
Steffen
Oracle ODBC Driver 08.01.0704
-----------------------------
SQLForeignKeys:
In: StatementHandle = 0x00D31868,
PkCatalogName = SQL_NULL_HANDLE, NameLength1 = 0,
PkSchemaName = "TST" , NameLength2 = 5,
PkTableName = "P1" , NameLength3 = 4,
FkCatalogName = SQL_NULL_HANDLE, NameLength4 = 0,
FkSchemaName = SQL_NULL_HANDLE, NameLength5 = 0,
FkTableName = SQL_NULL_HANDLE, NameLength6 = 0
Return: SQL_SUCCESS=0
Get Data All:
"PKTABLE_CAT", "PKTABLE_SCHEM", "PKTABLE_NAME", "PKCOLUMN_NAME", "FKTABLE_CAT",
"FKTABLE_SCHEM", "FKTABLE_NAME", "FKCOLUMN_NAME", "KEY_SEQ", "UPDATE_RULE",
"DELETE_RULE", "FK_NAME", "PK_NAME", "DEFERRABILITY"
<Null>, "TST", "P1", "K1", <Null>, "TST", "C1", "K1", 1, <Null>, 3, "FK_C1", "PK_P1",
<Null>
<Null>, "TST", "P1", "K2", <Null>, "TST", "C1", "K2", 2, <Null>, 3, "FK_C1", "PK_P1",
<Null>
<Null>, "TST", "P1", "K1", <Null>, "TST", "C2", "K1", 1, <Null>, 3, "FK_C2", "PK_P1",
<Null>
<Null>, "TST", "P1", "K2", <Null>, "TST", "C2", "K2", 2, <Null>, 3, "FK_C2", "PK_P1",
<Null>
4 rows fetched from 14 columns.
SELECT /*+ RULE */
''
, a.owner
, a.table_name
, b.column_name
, NULL
, c.owner
, c.table_name
, d.column_name
, b.position
, NULL
, decode(c.delete_rule, 'CASCADE', 0, 'NO ACTION', 3)
, c.constraint_name
, a.constraint_name
, ''
FROM all_constraints a
, all_cons_columns b
, all_constraints c
, all_cons_columns d
WHERE a.owner = b.owner
and a.constraint_name = b.constraint_name
and c.owner = d.owner
and c.constraint_name = d.constraint_name
and a.constraint_type = 'P'
and c.constraint_type = 'R'
and a.constraint_name = c.r_constraint_name
and a.owner = c.r_owner
and b.position = d.position
and a.table_name = 'P1'
and a.owner = 'TST'
ORDER BY 6, 7, 9
SQLForeignKeys:
In: StatementHandle = 0x00D31868,
PkCatalogName = SQL_NULL_HANDLE, NameLength1 = 0,
PkSchemaName = SQL_NULL_HANDLE, NameLength2 = 2,
PkTableName = SQL_NULL_HANDLE, NameLength3 = 2,
FkCatalogName = SQL_NULL_HANDLE, NameLength4 = 0,
FkSchemaName = "TST" , NameLength5 = 5,
FkTableName = "C1" , NameLength6 = 4
Return: SQL_SUCCESS=0
Get Data All:
"PKTABLE_CAT", "PKTABLE_SCHEM", "PKTABLE_NAME", "PKCOLUMN_NAME", "FKTABLE_CAT",
"FKTABLE_SCHEM", "FKTABLE_NAME", "FKCOLUMN_NAME", "KEY_SEQ", "UPDATE_RULE",
"DELETE_RULE", "FK_NAME", "PK_NAME", "DEFERRABILITY"
<Null>, "TST", "P1", "K1", <Null>, "TST", "C1", "K1", 1, <Null>, 3, "FK_C1", "PK_P1",
<Null>
<Null>, "TST", "P1", "K2", <Null>, "TST", "C1", "K2", 2, <Null>, 3, "FK_C1", "PK_P1",
<Null>
2 rows fetched from 14 columns.
SELECT /*+ RULE */
''
, a.owner
, a.table_name
, b.column_name
, NULL
, c.owner
, c.table_name
, d.column_name
, b.position
, NULL
, decode(c.delete_rule, 'CASCADE', 0, 'NO ACTION', 3)
, c.constraint_name
, a.constraint_name
, ''
FROM all_constraints a
, all_cons_columns b
, all_constraints c
, all_cons_columns d
WHERE a.owner = b.owner
and a.constraint_name = b.constraint_name
and c.owner = d.owner
and c.constraint_name = d.constraint_name
and a.constraint_type = 'P'
and c.constraint_type = 'R'
and a.constraint_name = c.r_constraint_name
and a.owner = c.r_owner
and b.position = d.position
and c.table_name = 'C1'
and c.owner = 'TST'
ORDER BY 2, 3, 9
SQLForeignKeys:
In: StatementHandle = 0x00D31868,
PkCatalogName = SQL_NULL_HANDLE, NameLength1 = 0,
PkSchemaName = "TST" , NameLength2 = 5,
PkTableName = "P1" , NameLength3 = 4,
FkCatalogName = SQL_NULL_HANDLE, NameLength4 = 0,
FkSchemaName = "TST" , NameLength5 = 5,
FkTableName = "C1" , NameLength6 = 4
Return: SQL_SUCCESS=0
Get Data All:
"PKTABLE_CAT", "PKTABLE_SCHEM", "PKTABLE_NAME", "PKCOLUMN_NAME", "FKTABLE_CAT",
"FKTABLE_SCHEM", "FKTABLE_NAME", "FKCOLUMN_NAME", "KEY_SEQ", "UPDATE_RULE",
"DELETE_RULE", "FK_NAME", "PK_NAME", "DEFERRABILITY"
<Null>, "TST", "P1", "K1", <Null>, "TST", "C1", "K1", 1, <Null>, 3, "FK_C1", "PK_P1",
<Null>
<Null>, "TST", "P1", "K2", <Null>, "TST", "C1", "K2", 2, <Null>, 3, "FK_C1", "PK_P1",
<Null>
<Null>, "TST", "P1", "K1", <Null>, "TST", "C2", "K1", 1, <Null>, 3, "FK_C2", "PK_P1",
<Null>
<Null>, "TST", "P1", "K2", <Null>, "TST", "C2", "K2", 2, <Null>, 3, "FK_C2", "PK_P1",
<Null>
4 rows fetched from 14 columns.
SELECT /*+ RULE */
''
, a.owner
, a.table_name
, b.column_name
, NULL
, c.owner
, c.table_name
, d.column_name
, b.position
, NULL
, decode(c.delete_rule, 'CASCADE', 0, 'NO ACTION', 3)
, c.constraint_name
, a.constraint_name
, ''
FROM all_constraints a
, all_cons_columns b
, all_constraints c
, all_cons_columns d
WHERE a.owner = b.owner
and a.constraint_name = b.constraint_name
and c.owner = d.owner
and c.constraint_name = d.constraint_name
and a.constraint_type = 'P'
and c.constraint_type = 'R'
and a.constraint_name = c.r_constraint_name
and a.owner = c.r_owner
and b.position = d.position
and a.table_name = 'P1'
and a.owner = 'TST'
and c.owner = 'TST'
