MaxDB 7.7: JDBC select with alias doesn't work

[Former Member]

I use the newest 7.7 under Windows 2003 64-bit.

When i run the following statement with JDBC

SELECT COUNT(*) AS ANZAHL, BENUTZER_ADRESSE.BUNDESLAND AS GRUPPE

FROM DOC_META, BENUTZER_ADRESSE

...

GROUP BY BENUTZER_ADRESSE.BUNDESLAND ORDER BY GRUPPE

The result columns are"ANZAHL", "BUNDESLAND".

SELECT COUNT(*) AS ANZAHL, BENUTZER_ADRESSE.BUNDESLAND & '' AS GRUPPE

In the result list the expected "ANZAHL" and "GRUPPE" will be displayed.

Is their something suspicious with my statement or is their a bug in the jdbc driver?

Regards

Stefan

[former_member185699]

Hi Stefan,

I didn't catch the problem in detail. For the query

SELECT COUNT(*) AS ANZAHL, BENUTZER_ADRESSE.BUNDESLAND & '' AS GRUPPE 
FROM DOC_META, BENUTZER_ADRESSE 

you get the columnnames "ANZAHL" and "GRUPPE". But you would like to get "ANZAHL" and "BUNDESLAND", right?

Do you know what JDBC call (getColumnLabel or getColumnName) the application is using? If not a JDBC trace (java -jar sapdbc.jar) would help.

Regards

Marco

[Former Member]

Hi Marco,

I want to get "ANZAHL" and "GRUPPE" because the results are send to a statistics component.

After digging deeper and deeper I found, that getColumnName() is used.

On the other side I figured out, that their is a method for rawSql where I can send down a "saveKeys" array.

Now I start to change my code (sigh).

Thanx for bringing me on the right way

Stefan

[Former Member]

Hi Marco,

thanx for your detailed answer.

But I can't change my third party persistence framework to fit this behavior, and it's not only EOF (WebObjects) also the SQL Explorer (Eclipse based) shows the wrong result. Under 7.6 it all works fine.

So I would say it's a bug ;-(

Do you know if there are any plans to drop that feature?

Regards

Stefan

[former_member185699]

The JDBC driver offers two different methods in ResultSetMetaData to retrieve column names:

getColumnName - Get the designated column's name.

getColumnLabel - Gets the designated column's suggested title for use in printouts and displays. The suggested title is usually specified by the SQL AS clause. If a SQL AS is not specified, the value returned from getColumnLabel will be the same as the value returned by the getColumnName method.

For select list items where you can't identify the designated column's name (i.e.. count(*)) you will get the column label also as column name.

In your case the second query contains a string concatenation (BENUTZER_ADRESSE.BUNDESLAND & ''). Therefor you will get the column label as column name.

So I would say it's not bug, it's a feature

Regards,

Marco