Many thanks for the continuing great work Steffen. Tim.
On Fri, Sep 28, 2001 at 05:48:47PM +0200, 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. > 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' >
