Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <9967409.1181245166543.JavaMail.jira@brutus>
Date: Thu, 7 Jun 2007 12:39:26 -0700 (PDT)
From: =?utf-8?Q?J=C3=B8rgen_L=C3=B8land_=28JIRA=29?= <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-2758) ODBC metadata function
 "SQLForeignKeys" returns different results in 10.3.
In-Reply-To: <28495783.1180991605933.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


    [ https://issues.apache.org/jira/browse/DERBY-2758?page=3Dcom.atlassian=
.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12502491 ]=20

J=C3=B8rgen L=C3=B8land commented on DERBY-2758:
--------------------------------------

Thank you for reviewing the patch, Army.=20

I was rather surprised that all tests passed without modifications, which, =
as you say, indicates that new test cases are required.
I'll look into the tests tomorrow or early next week. It would be great if =
the current patch was committed, though.

> ODBC metadata function "SQLForeignKeys" returns different results in 10.3=
.
> -------------------------------------------------------------------------=
-
>
>                 Key: DERBY-2758
>                 URL: https://issues.apache.org/jira/browse/DERBY-2758
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC, Miscellaneous
>    Affects Versions: 10.3.0.0
>         Environment: DB2 Runtime Client running against Derby network ser=
ver.
>            Reporter: A B
>            Assignee: J=C3=B8rgen L=C3=B8land
>             Fix For: 10.3.0.0
>
>         Attachments: DERBY-2758-1.diff, DERBY-2758-1.stat
>
>
> In Derby 10.2 and earlier an ODBC application which called the SQLForeign=
Keys function would return a set of "imported" and/or "exported" keys depen=
ding on the arguments passed in.  For more on that function and its argumen=
ts, see:
>   http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/odbc=
/htm/odbcsqlforeignkeys.asp
> In particular we have the following (pasted from the above link):
> <begin paste>
> If *PKTableName contains a table name, SQLForeignKeys returns a result se=
t containing the primary key of the specified table and all of the foreign =
keys that refer to it. The list of foreign keys in other tables does not in=
clude foreign keys that point to unique constraints in the specified table.
> If *FKTableName contains a table name, SQLForeignKeys returns a result se=
t containing all of the foreign keys in the specified table that point to p=
rimary keys in others tables, and the primary keys in the other tables to w=
hich they refer. The list of foreign keys in the specified table does not c=
ontain foreign keys that refer to unique constraints in other tables.
> If both *PKTableName and *FKTableName contain table names, SQLForeignKeys=
 returns the foreign keys in the table specified in *FKTableName that refer=
 to the primary key of the table specified in *PKTableName. This should be =
one key at most.
> <end paste>
> Note that either PKTableName or FKTableName could be missing, i.e. could =
be null.
> Now, in org/apache/derby/catalog/SystemProcedures.java, there is a static=
 method called "SQLFOREIGNKEYS" which, to quote the javadoc, "map[s] SQLFor=
eignKeys to EmbedDatabaseMetaData.getImportedKeys, getExportedKeys, and get=
CrossReference".
> That method looks at some "options" that it receives from the client and =
makes a call to the corresponding method on EmbedDatabaseMetaData:
>         String exportedKeyProp =3D getOption("EXPORTEDKEY", options);
>         String importedKeyProp =3D getOption("IMPORTEDKEY", options);
>         if (importedKeyProp !=3D null && importedKeyProp.trim().equals("1=
"))
>             rs[0] =3D getDMD().getImportedKeys(fkCatalogName,
>                                         fkSchemaName,fkTableName);
>         else if (exportedKeyProp !=3D null && exportedKeyProp.trim().equa=
ls("1"))
>             rs[0] =3D getDMD().getExportedKeys(pkCatalogName,
>                                         pkSchemaName,pkTableName);
>         else
>             rs[0] =3D getDMD().getCrossReference (pkCatalogName,
>                                            pkSchemaName,
>                                            pkTableName,
>                                            fkCatalogName,
>                                            fkSchemaName,
>                                            fkTableName);
> That said, when running with the DB2 Runtime Client the "options" argumen=
t only contains "ODBC"; it does not (appear to) contain "IMPORTEDKEY" nor "=
EXPORTEDKEY".  So with that client we ultimately end up calling "getCrossRe=
ference()" every time.  And in EmbedDatabaseMetaData.getCrossReference(), w=
e see:
>     PreparedStatement s =3D getPreparedQuery("getCrossReference");
>     s.setString(1, swapNull(primaryCatalog));
>     s.setString(2, swapNull(primarySchema));
>     s.setString(3, swapNull(primaryTable));
>     s.setString(4, swapNull(foreignCatalog));
>     s.setString(5, swapNull(foreignSchema));
>     s.setString(6, swapNull(foreignTable));
>     return s.executeQuery();
> That is to say, if either primaryTable or foreignTable is null, we swap i=
t with a "%" to be used for pattern matching.  Prior to the 10.3, that work=
ed fine.  With DERBY-2610, though, the getCrossReference query in metadata.=
properties was changed to disallow pattern matching for the primary key:
> @@ -532,11 +532,15 @@
>  #
>  #param1 =3D pattern for the PRIMARY CATALOG name=20
>  #param2 =3D pattern for the PRIMARY SCHEMA name=20
> -#param3 =3D pattern for the PRIMARY TABLE name=20
> +#param3 =3D PRIMARY TABLE name=20
>  #
>  #param4 =3D pattern for the FOREIGN CATALOG name ('%' for getExportedKey=
s())
>  #param5 =3D pattern for the FOREIGN SCHEMA name ('%' for getExportedKeys=
())
>  #param6 =3D pattern for the FOREIGN TABLE name ('%' for getExportedKeys(=
))
> +#  DERBY-2610: did not change from pattern matching to "T2.TABLENAME=3D?=
"=20
> +#          because getExportedKeys uses this query with '%' for foreign =
table
> +#  Future: may want to add a new query for getExportedKeys to remove the
> +#          "T2.TABLENAME LIKE ?" pattern
>  getCrossReference=3D\
>  SELECT CAST ('' AS VARCHAR(128)) AS PKTABLE_CAT, \
>          PKTABLE_SCHEM, \
> @@ -587,7 +591,7 @@
>                           WHERE \
>                              ((1=3D1) OR ? IS NOT NULL) \
>                              AND S.SCHEMANAME LIKE ? \
>                              AND T.TABLENAME LIKE ? \     <-- removed w/ =
DERBY-2610
>                              AND T.TABLENAME=3D? \  <-- added with DERBY-=
2610
>                              AND S.SCHEMAID =3D T.SCHEMAID \
> As a result, the ODBC "SQLForeignKeys" function now returns zero rows bec=
ause there is no table whose name equals the "%" that we swapped in for the=
 null.
> I'm not sure what the best fix for this should be.  The JDBC API for getC=
rossReference() indicates that both primaryTable and foreignTable "must mat=
ch the table name as it is stored in the database", so perhaps the bug is i=
n SystemProcedures.java, where the SQLForeignKeys function is mapped to a g=
etCrossReference() call that passes nulls.  But one could argue that, given=
 the lack of information in the "options" string received from the client (=
esp. the missing "IMPORTEDKEY" or "EXPORTEDKEY" keywords), SystemProcedures=
.java is actually doing the right thing.  Either way, it's not immediately =
clear to me how this should best be resolved...
> I'm marking this as a 10.3 regression (with 10.3 fixin) since the behavio=
r in the 10.3 trunk is different than what it was in previous releases.  If=
 anyone disagrees with this, please feel free to say so and/or update accor=
dingly.
> Note: I don't think this behavior is technically covered by the release n=
ote for DERBY-2610 because a) the release note does not mention getCrossRef=
erence() (should it??), and b) the call to "getCrossReference()" is made in=
ternally; a user's ODBC app would be calling SQLForeignKeys, for which a nu=
ll primary/foreign table name are in fact perfectly legal.

--=20
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.