Friday, May 20, 2011

java.sql.SQLException: Column 'name' not found.

In OSX Tomcat MySQL 5.1 my query was failing from Java with the below error:
java.sql.SQLException: Column 'name' not found.

The query was perfectly running from the command prompt. Developers using Windows and Ubuntu did not have the same issue so I decided to upgrade to latest version both MySQL and the JDBC connector. The issue did not go away.

Problem explained

The below will fail ...
SELECT DISTINCT c.name AS `Client Name`, ...
But this will work:
SELECT DISTINCT c.name, ...
Whether we need the aliases in the query is a good point. Especially for internationalization this is a bad practice but I needed to be sure my MAC could support this :-)

The Solution

Use a flag in the connection string:
SELECT DISTINCT c.name, ...
jdbc.url=jdbc:mysql://localhost:3306/nestorurquiza?useOldAliasMetadataBehavior=true
From MySQL Reference Manual:

"Should the driver use the legacy behavior for "AS" clauses on columns and tables, and only return aliases (if any) for ResultSetMetaData.getColumnName() or ResultSetMetaData.getTableName() rather than the original column/table name? In 5.0.x, the default value was true."

3 comments:

Raul Luna said...

You have saved my day!!! congratulations for the explanation.

Sandeep Kaur said...

Hi,I was facing the same issue and tried this way by setting the useOldAliasMetadataBehaviour property to true.But,it's not working.Any suggestions or help?

Thanks in advance!

Nestor Urquiza said...

Hi Sandeep, since it's been a while I recommend you reach out stack overflow for your question. Make sure you refer to specific versions of mysql, app server, OS etc. Best, - Nestor

Followers