Issue Details (XML | Word | Printable)

Key: JDBC-331
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Mark Rotteveel
Reporter: Jan Marten
Votes: 0
Watchers: 0

If you were logged in you would be able to see more operations.
Jaybird JCA/JDBC Driver

DatabaseMetaData.getPrimaryKeys returns wrong primary keys for tables containing underscores _

Created: 20/Nov/13 02:34 PM   Updated: 02/Jan/14 05:03 PM
Component/s: JDBC driver
Affects Version/s: Jaybird 2.2.3, Jaybird 2.2.4
Fix Version/s: Jaybird 2.2.4, Jaybird 3.0.0

Issue Links:

 Description  « Hide
The AbstractDatabaseMetaData.getPrimaryKeys uses the AbstractDatabaseMetaData.Clause class for querying the primary keys.
However, this Clause class checks whether the provided pattern contains wildcards.
For tables with names like "AB_DE" this is true thus a like query is executed.

If there are two tables

and getPrimaryKeys(null, null, "AB_DE") is called this returns also the primary keys of ABCDE.

A further problem is that a String right truncation error is thrown if the table name is 31 characters long and contains an underscore

The check for wildcards is not necessary in the case of getPrimaryKeys, since the exact table name is given

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Mark Rotteveel added a comment - 20/Nov/13 07:05 PM
The first is definitely a bug, the code uses code common to multiple metadata methods, but those other methods expect a LIKE pattern (eg getTables()), the presence of an underscore (or a percentage) will add the condition as a like clause.

This issues might also affect other metadata methods (not yet checked):
* getBestRowIdentifier
* getColumnPrivileges
* getCrossReference
* getExportedKeys
* getImportedKeys
* getIndexInfo

The second is debatable as Firebird objectnames (eg tablenames) cannot exceed 31 characters, but then I would expect the exception even if the parameter doesn't contain an underscore when it is longer than 31 characters. However I could include a CAST to make the parameter longer (by default the length of the metadata column is used).

Mark Rotteveel added a comment - 20/Nov/13 07:06 PM
Scheduled for fix in 2.2.4 (and 3.0).

Mark Rotteveel added a comment - 20/Nov/13 07:39 PM
With regard to the second item there is something wrong there (this was already known). When using no connection character set (or NONE), I get no error, with a single byte character set I don't get a string truncation error, but an "org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544726. Error reading data from the connection." because the parameter is too long. When using UTF-8 I get a string truncation error but that happens both with and without an underscore or percentage symbol.

Mark Rotteveel added a comment - 24/Nov/13 01:00 PM
Fixed the methods mentioned above so they now accept only the literal object name as required by JDBC.

The existing workaround for patterns longer than the column width in metadata (padding the column with 31 spaces and the argument with 31 spaces and a %) assumes the connection character set is NONE, it fails in various ways (see above) with a connection characterset other than NONE or UNICODE_FSS.

Modified the workaround by padding the argument with 15 spaces and a %, which allows a pattern 15 characters longer than the maximum object length. For literal objectnames I have cast the column to VARCHAR(40) so it supports 9 characters longer.

Casting the parameter to a longer width is currently not possible as this was introduced in Firebird 2.0 and Jaybird 2.2 still needs to support Firebird 1.x.

The current behavior is not entirely JDBC compliant (eg with regard to case sensitivity). For Jaybird 3.0, I will take a closer look at this in JDBC-231.