After some investigation, I get the impression that BIRT improperly retrieves data from ResultSets. According to JDBC standard, the way to retrieve a certain column is accomplished by calling ResultSetMetaData.getColumnLabel(), NOT getColumnName().<br />
<br />
A simple example:<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
SELECT COMPANY.NAME AS COMPANY_NAME,
INSTALLATION.NAME AS INSTALLATION_NAME
FROM COMPANY
JOIN INSTALLATION ON INSTALLATION.MANAGER_COMPANY_ID = COMPANY.COMPANY_ID
</pre>
<br />
It's important to see here, that two attributes are selected, both called "NAME" on their respective tables. <br />
<br />
In the "Output Columns" dialog of "Edit Data Set", BIRT displays for Name / Type / Alias / Display Name:<br />
<br />
- NAME / String / <empty> / COMPANY_NAME<br />
- NAME_2 / String / <empty> / INSTALLATION_NAME<br />
<br />
If I put together a report by drag and drop, BIRT accesses the two fields with dataSetRow["NAME"] and dataSetRow["NAME_2"] which perfectly works but IMHO is the wrong way to access these attributes.<br />
Instead, the attributes should be accessed by dataSetRow["COMPANY_NAME"] and dataSetRow["INSTALLATION_NAME"], since my query neither returns a NAME nor a NAME_2. BIRT should not care about the column name, but the column label! Just think about what happens, if a third or fourth NAME attribute is added with additional joins. All existing attributes will get new numbers which will lead to incorrect results.<br />
<br />
Many questions, posts and bug reports can be found on this issue. The reason for this confusion might originate from a bug in the MySQL driver which has been fixed with version 5.1. As of then, getColumnName() no longer returned the Alias name but the "native" column name (the "wrong" old behavior can be forced by adding useOldAliasMetadataBehavior=true in the connection string).<br />
I have the impression that BIRT didn't adopt this change.<br />
<br />
To complete the picture: if I look at the meta data returned from squirrel when executing the above query with a current driver, I see:<br />
<br />
getColumnLabel()___________getColumnName()<br />
COMPANY_NAME____________NAME<br />
INSTALLATION_NAME________NAME<br />
<br />
where as the same query with an old driver (or the new driver with useOldAliasMetadataBehavior=true) returns:<br />
<br />
getColumnLabel()___________getColumnName()<br />
COMPANY_NAME____________COMPANY_NAME<br />
INSTALLATION_NAME________INSTALLATION_NAME<br />
<br />
In short, I expect that my query's attributes can be accessed by dataSetRow["COMPANY_NAME"] and dataSetRow["INSTALLATION_NAME"] and not by dataSetRow["NAME"] and dataSetRow["NAME_2"].<br />
<br />
The JDBC-4.0 specification states that ResultSet.get...(String), takes the column *label*, not the name. If you're writing spec-compliant software, you should be use ResultSetMetadata.getColumnLabel() to retrieve<br />
names to pass into ResultSet.get...(String).<br />
<br />
And the API of e.g. ResultSet.getString (String columnLabel) says:<br />
columnLabel - the label for the column specified with the SQL AS clause. If the SQL AS clause was not specified, then the label is the name of the column<br />
<br />
Related issues: <a class='bbc_url' href='
http://www.birt-exchange.org/forum/designing-birt-reports/12448-problem-using-alias-names-sql-query.html?&spm=1&pm=VGhhbmsgeW91IGZvciBsb2dnaW5nIGluLiBXZSdsbCBrZWVwIHlvdSBsb2dnZWQgaW4gZm9yIHR3byB3ZWVrcwp1bmxlc3MgeW91IGxvZyBvdXQu&err='>http://www.birt-exchange.org/forum/designing-birt-reports/12448-problem-using-alias-names-sql-query.html?&spm=1&pm=VGhhbmsgeW91IGZvciBsb2dnaW5nIGluLiBXZSdsbCBrZWVwIHlvdSBsb2dnZWQgaW4gZm9yIHR3byB3ZWVrcwp1bmxlc3MgeW91IGxvZyBvdXQu&err=</a><br />
<br />
and:<br />
<a class='bbc_url' href='
http://bugs.mysql.com/bug.php?id=43684'>MySQL Bugs: #43684: JDBC Metadata ColumnName Name is incorrect if using field alias</a><br />
<br />
and:<br />
<a class='bbc_url' href='
http://bugs.mysql.com/bug.php?id=35150'>MySQL Bugs: #35150: Inconsistency in column name causes Hibernate to fail.</a><br />
<br />
Thanks,<br />
Remo<br />
<br />
I work with BIRT 2.5.1
