[#SQLQ-18] No keys highlighting with the latest versions of Mysql Connector/J

SQL Query Plugin

No keys highlighting with the latest versions of Mysql Connector/J

Created: 25 Nov 06 01:17 Updated: 28 Feb 07 01:50

Component/s:

Result Table

Affects Version/s:

None

Fix Version/s:

None

Original Estimate:	Unknown	Remaining Estimate:	Unknown	Time Spent:	Unknown
Environment:	Win2K, Idea5/6

Description

« Hide

Highlighting Primary/Foreign keys in queries used to work flawlessly with Mysql database and Connector/J version 3.1.1-alpha. But when I'm trying later versions of Connector/J (tried 3.1.13 and 5.0.3 so far) - nothing is highlighted even though browser can see primary and foreign keys no problem. Of course "show related" doesn't work either.

All

Comments

Work Log

Change History

Sort Order:

[ Permlink | « Hide ]

Stefan Stiller - 27 Nov 06 12:28

I've tested it with Connector/J 5 on a MySQL 4.0.4 database it it seems to work fine. Maybe it depends on both the driver version and the database version.

[ Permlink | « Hide ]

oliver henlich - 27 Feb 07 07:34

Highlighting Primary/Foreign keys is broken for me too with the following configurations:

Connecting to [MySQL 4.1.20-community-nt] with [mysql-connector-java-5.0.4 ( $Date: 2006-10-19 17:47:48 +0200 (Thu, 19 Oct 2006) $, $Revision: 5908 $ )]

Connecting to [MySQL 5.0.27-community-nt] with [mysql-connector-java-5.0.4 ( $Date: 2006-10-19 17:47:48 +0200 (Thu, 19 Oct 2006) $, $Revision: 5908 $ )]

Note: The schema browser works and sees all the pk and fk relations.

[ Permlink | « Hide ]

oliver henlich - 27 Feb 07 07:51

Forgot to add:

IntelliJ 6.0.4 (#6148)
SQLPlugin 2.2 (#307)

[ Permlink | « Hide ]

oliver henlich - 28 Feb 07 01:50

Ok i found the problem.

Root cause is the MySQL jdbc driver implementation of

storesLowerCaseIdentifiers(), storesUpperCaseIdentifiers(), etc

on windows.

2.2 Source zip

ResultSetTableModel.createData()

String columnName=metaData.getColumnName(column); // returns UPPER case column names
...
int identifierCase=database.getIdentifiersCase(project); // returns StringUtils.LOWER_CASE with mysql drivers on windows
...
String actualColumnName=StringUtils.applyCharacterCase(columnName, identifierCase); // converts column name to LOWER case
...
// returns false base set of primary keys has UPPER case column names but actualColumnName is LOWER case
tableMetaData.getPrimaryKeys().contains(actualColumnName)

// so primary key columns are not recognized as primary key columns!

Suggested fix is that the plugin treats all table and column names in a uniform internal case.

So com.kiwisoft.db.Database would convert all column names to (say) lower case when it is
storing things in the Sets.

Then ResultSetTableModel need not worry and check/convert case...

Basically all metadata is handled in a fixed case regardless of JDBC driver implementation.