This Bugzilla instance is a read-only archive of historic NetBeans bug reports. To report a bug in NetBeans please follow the project's instructions for reporting issues.
Using Netbeans 6.1 RC1 Build: 200804100130 Using a mysql database, any time that a column alias comes into play, while the visual query tool will display columns correctly, the query results return the actual column name. And, when binding to a cachedrowset whose definition uses a column alias, only actual column names are available for selection. If the column alias is forced, the project will not build. This is also true when a database view which contains a column alias is used. The view's column names will appear correctly in the query designer. However, when you run the query, the actual table column name will appear in the column header. Unfortunately, there's no work-around because, if you pick the real column name when binding to data in Netbeans, when you load the application in glassfish, it's working with the column alias instead, and can't find the actual column name which is referenced in the binding. This was discovered when moving a project from 6.0.1 to 6.1. The behavior does not exist in 6.0.1
As far as I know nothing has been changed between 6.0.1 & 6.1 in this area. So, first I checked this with well tested bundled derby databaes I created an alias for the derby database table column (PRODUCT.POSTAL_CODE as PRODUCT.p_code) and then bind it to the JSF table. It correctly displayed the aliased name. So the logic to show the alias is not broken in the CachedRowset data provider. Howerver, the names displayed by the table is obtained after executing the SQL and then via the method CachedRowset.getMetadat()a (which uses the JDBC driver). I'm not sure if this is a problem with the JDBC driver. Also, we introduced Caching of meta-adat in NB 6.1. Not sure, if this has any effect when Cached metadata is fetched after changing a column alias. I tried it with derby after closing and opening the project (so that cached metadata will come in to play), but it seems to work fine when I aliased a column.
I was wondering about this as well. As far as I know, not much has changed in 6.1 in cached rowset/query editor. Anyway, Roman can you please help reproduce this issue with MySQL ? thanks
Using 6.0 and setting an alias for a column is working in 6.0. The Query Editor executes the SQL and the alias appears in place of the column name. Also the dataprovider uses the alias instead of the column. So, this looks like a regression in 6.1. The Query Editor, at design-time uses a live connection to obtain table information, not the cached resultset. So, this doesn't seem to be related to the new feature in 6.1 for cached resultset. There were some changes in Query Editor and CachedRowSetDataProvider for 6.1, but I'll have to identify the changes made.
I've tried with Derby and MySQL. For Derby everything was fine, as Winston wrote. But with MySQL for me alias doesn't work. But in contrast to reporter I didn't see column alias even in visual query editor. Applications (with Derby and with MySQL) both were successfully deployed and loaded in browser. Just for Derby shows column alias, but for MySQL actual column name.
I didn't see any changes that could have caused this, so I tried debugging a local build of my nearly up-to-date local workspace. The alias worked fine for MySQL and Java DB for the Query Editor and dataprovider. AFAIK no recent changes have been made by me or David in the Query Editor or Dataprovider. I'm still investigating
I tried also with MS SQL Server and result is the same as for Derby: everything is fine. As I see there is a difference between query for MySQL and Derby, MS SQL Server. MySQL column names are marked out with '`' sign, but for Derby and MS SQL Server there is no such mark. Can this be a reason?
I recompiled the db.sql.visualeditor module in my local workspace (the one where I wasn't seeing the problem) and can now also reproduce using MySQL. However, this is not reproducible for Derby It looks like a MySQL driver issue. In 6.1 we moved to MySQL driver 5.1.5, whereas in 6.0 we used 5.0.7 Instead of using MySQL driver 5.1.5, I used 5.0.7 and aliases are now working fine. Although there appears to be a workaround, we might want to consider moving back to driver 5.0.7 Roman please confirm using the 5.0.7 MySQL driver
Perhaps the 5.0.8 driver works also. http://dev.mysql.com/downloads/connector/j/5.0.html
Yes, John, with 5.0.7 MySQL driver everything works fine.
Not everything. In visual SQL editor and after binding with Table JSF component column alias is displayed correctly, but after deployment I've got exception (in attach).
Created attachment 60257 [details] Exception from application server log file.
This is a known "issue" - MySQL does not consider this a bug. See http://bugs.mysql.com/bug.php?id=21596 Our problem is we are using ResultSetMetaData.getColumnName() when we *should* be using ResuletSetMetaData.getColumnLabel(). In the old driver MySQL had a bug where they returned the alias name (the label) even for getColumnName(), which properly should return the underlying name regardless of the alias. I apologize I didn't catch this sooner - I see I'm on the cc list but I don't think I paid attention. The proper fix is for us to change our code to use getColumnLabel() instead of getColumName(). I fixed this in the query editor already, but it looks like there are other places we need to fix this. As a workaround, the user can add the following property to their connection string: "useOldAliasMetadataBehavior". For example: "jdbc:mysql://localhost:3306/sample?useOldAliasMetadataBehavior=true"
David's correct. A change could be made in dataprovider but I thought it was a risky fix at the time. However, regarding the use of the 5.0.7 driver, runtime works for me. For Roman in desc11, probably the newer driver had been deployed to glassfish previously and reused instead of 5.0.7 so I would expect runtime to fail
will fix in the trunk for the next patch might want to add an FAQ for 6.1
fix integrated in main. I'll request for inclusion in the 6.1 patch *** This issue has been marked as a duplicate of 129330 ***