tisdag, januari 08, 2008

Are you using ResultSetMetaData.getColumnName?

As the title says, are you using java.sql.ResultSetMetaData.getColumnName in your code? It's interesting, I have done for years, and I didn't know that it was just a bug, waiting to happen.

Until I tried MySQL's 5.1-branch of their JDBC code, I'd always assumed that getColumnName was the right one for generic SQL code. Turns out it isn't. Specifically, it isn't the right one if you're using aliasing in your code. Say you have SELECT Host AS h FROM Host. Now, until the 5.1 branch MySQL JDBC, you would get "h" if you did getColumnName(1) on this result sets metadata. Not so anymore. Now you get "Host". So what should you use? getColumnLabel. It's on the same interface. Until tonight I'd never seen a difference between them. But now there is one - so go through all your JDBC code and make sure you're using the right one here.

Oh, that's right. MySQL 5.0.5 seems to have a bug in multibyte aliasing. So if you alias Host to be a Chinese character, for example, you will not get the same value back from getColumnName or getColumnLabel. I assume this is a bug, since the 5.1-branch seems good.

1 kommentar:

MF Jones sa...

I've just found this bug when I was using Scriptella ETL with a new mysql driver. I've patched Scriptella to support both getColumnName and getColumnLabel. I always used getColumnName and was quite happy with other drivers until today :)