ResultSetMetaData.getColumnName() has unexpected results

[Adam McMahon]

Hi,

My tool is compatible with embedded H2 and Derby, and I would like to give clients the options of FB embedded. 

When I run sql like: select count(*) as n from PERSON the results are correct, but the ResultSetMetaData.getColumnName() does not capture the column name as "n" and instead returns the column name as "count".  This breaks some of my code.  In the following example, I would expect ResultSetMetaData.getColumnName() to return "N", but instead it returns "COUNT".

 sql = "select count(*) as n from PERSON";
 rs = st.executeQuery(sql);

ResultSetMetaData rsMetaData = rs.getMetaData();

 int count = rsMetaData.getColumnCount();
 for (int i = 1; i <= count; i++) {
       System.out.println(rsMetaData.getColumnName(i));
 }

Thanks to the team for their help!

-Adam

[Arioch The]

perhaps you need alias aka label, not name

see https://stackoverflow.com/a/12724236/976391

чт, 13 авг. 2020 г. в 23:33, Adam McMahon <ad...@cs.miami.edu>:

> --
> You received this message because you are subscribed to the Google Groups "firebird-java" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to firebird-jav...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-java/43853fa0-19dd-416e-8c8f-d79ffded0f3dn%40googlegroups.com.

[Adam McMahon]

Yes, using Label worked.  Thanks, and using label also works with DERBY and H2, so it seems to be a good solution.

-Adam

[Mark Rotteveel]

On 13-08-2020 22:33, Adam McMahon wrote:
> My tool is compatible with embedded H2 and Derby, and I would like to
> give clients the options of FB embedded.
>
> When I run sql like: select count(*) as n from PERSON the results are
> correct, but the ResultSetMetaData.getColumnName() does not capture

> thecolumn name as "n" and instead returns the column name as "count".

> This breaks some of my code.  In the following example, I would expect
> ResultSetMetaData.getColumnName() to return "N", but instead it returns
> "COUNT".

You need to use getColumnLabel(). The JDBC specification differentiates
between column name and column label. The column name is the original
column name, while the column label is the specified alias - if present
- or otherwise the the column name.

There are 2 problems:

1) Firebird gives (some) expressions are function calls a generated name
(based on the name of the function or expression), while some database
systems will use no name, and yet other database systems will use the alias.

2) Historically the JDBC specification wasn't very clearly about the
difference between column name and column label, but this was clarified
in JDBC 4 (Java 6), so some JDBC drivers (including Jaybird 2.1.x and
earlier) would return the column label from both getColumnName and
getColumnLabel. Jaybird fixed this in version 2.2.

See also:
-
https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/ResultSetMetaData.html#getColumnName(int)
-
https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/ResultSetMetaData.html#getColumnLabel(int)

Retrieval of columns from a result also happens by label and not by name
(eg see
https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/ResultSet.html#getString(java.lang.String)
).

You either need to switch your code to use getColumnLabel (which should
also work correctly on the other database drivers), or you need to
specify the connection property columnLabelForName=true which will make
getColumnName return the same value as getColumnValue

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 14-08-2020 08:08, Mark Rotteveel wrote:
> 2) Historically the JDBC specification wasn't very clearly about the
> difference between column name and column label, but this was clarified
> in JDBC 4 (Java 6)

Correction, it was clarified in JDBC 3.0 (Java 1.4 IIRC).

Mark
--
Mark Rotteveel