Railo and Mysql Column Alias
Aug 19 2009 - By George Webster
Ran into my first "real" hiccup working with Railo 3.1 and MySql this evening using column aliases.
While trying to so a simple select (dumbed down for example of course):
select some_name as alias_name from some_table
This would normally return alias_name in the results. However Railo kicks back an error: "alias_name" is absent from the resultset. This follows with a list of column names, showing only "some_name". Odd don't you think?
After some quick googling it turns out:
The old behavior of the MySQL JDBC driver was to return the alias ('same_name' in this case) for ResultSetMetaData.getColumnName(), which was wrong according to the JDBC spec, and instead, the actual column name is supposed to be returned. The 5.1 versions of the MySQL driver now default to using the correct behavior, which breaks apps that were coded against the old behavior (the correct way to get the alias info is to use ResultSetMetaData.getColumnLabel() instead). It looks like both the CF and JSP runtimes relied on getColumnName(), so aliases won't be accessible using their JDBC result variable syntax when using the latest MySQL drivers (wich Stax includes).
You can re-enable the old MySQL behavior by adding the ?useOldAliasMetadataBehavior=true to your JDBC URLs, which seems to fix this problem. I verified this fixes the issue for JSP apps, so I assume it will for CF apps too. The current "public" version of the Stax SDK may not support this parameter syntax, but the Stax cloud definitely does. If this fails for you when running locally, let me know, and I'll point you to the pre-release Stax SDK that support this.(source)
So instead of adjusting the MySql JDBC driver by hand, all you need to do go to your web/server admin in Railo: Server > Datasources -> Edit Your Data Source and set Alias Handling to true.