Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

DatabaseMetaData.getPrimaryKeys returns wrong primary keys for tables containing underscores _ [JDBC331] #377

Closed
firebird-automations opened this issue Nov 20, 2013 · 9 comments

Comments

@firebird-automations
Copy link

Submitted by: Jan Marten (janmarten)

Is related to JDBC231
Relate to JDBC335

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
AB_DE
ABCDE

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

Commits: 8135b90 fbd6341 6135d82 06616b1 d2c621d 11f22eb f98651e b4043b4 95c2000 FirebirdSQL/fbt-repository@9968970 FirebirdSQL/fbt-repository@7ce6ced FirebirdSQL/fbt-repository@4c1a054 FirebirdSQL/fbt-repository@0e5a24d FirebirdSQL/fbt-repository@9c37a2b FirebirdSQL/fbt-repository@fcf6ffd FirebirdSQL/fbt-repository@7682563 FirebirdSQL/fbt-repository@e7cdef9 FirebirdSQL/fbt-repository@84c902c

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

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).

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Scheduled for fix in 2.2.4 (and 3.0).

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Fix Version: Jaybird 2.2.4 [ 10531 ]

Fix Version: Jaybird 3.0 [ 10440 ]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

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.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue is related to JDBC231 [ JDBC231 ]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

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 JDBC231.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue relate to JDBC335 [ JDBC335 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment