SQL column aliases

[AxiomShell]

Hi everyone,

Does anyone know how to retrieve a value by its column alias?

I'll give you an example of what I'm doing:

def rows = Database.sql.rows("select blah as aliased_blah from db")
println rows

will print:

["blah":"1"], ..., ["blah":"n"]

when what I need is:

["aliased_blah":"1"], ..., ["aliased_blah":"n"]

Is this a JDBC limitation?

Many thanks

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[Mike Dillon-4]

begin AxiomShell quotation:

> Does anyone know how to retrieve a value by its column alias?
>
> I'll give you an example of what I'm doing:
>
> def rows = Database.sql.rows("select blah as aliased_blah from db")
> println rows
>
> will print:
>
> ["blah":"1"], ..., ["blah":"n"]
>
> when what I need is:
>
> ["aliased_blah":"1"], ..., ["aliased_blah":"n"]
>
> Is this a JDBC limitation?

It may be a problem with your driver. All of the drivers I've ever dealt
with (Oracle, MySQL, Postgres, MS SQL) would have "aliased_blah" for the
column name here.

-md

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[AxiomShell]

Hi Mike,

Thanks for the info.
I should've mentioned:

I'm using JDK 1.6.0, Groovy 1.6.0, MySQL 5.0.22 and MySQL's Connector
5.1.2.

If no one else can reproduce it (in Groovy), I'll look for the reason
for this somewhere else.

Best regards.

On Apr 27, 6:46 pm, Mike Dillon <[hidden email]> wrote:

> begin AxiomShell quotation:
>
>
>
> > Does anyone know how to retrieve a value by its column alias?
>
> > I'll give you an example of what I'm doing:
>
> > def rows = Database.sql.rows("select blah as aliased_blah from db")
> > println rows
>
> > will print:
>
> > ["blah":"1"], ..., ["blah":"n"]
>
> > when what I need is:
>
> > ["aliased_blah":"1"], ..., ["aliased_blah":"n"]
>
> > Is this a JDBC limitation?
>
> It may be a problem with your driver. All of the drivers I've ever dealt
> with (Oracle, MySQL, Postgres, MS SQL) would have "aliased_blah" for the
> column name here.
>
> -md
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>    http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[AxiomShell]

Sorry, I meant Groovy 1.5.6.

On Apr 27, 7:25 pm, AxiomShell <[hidden email]> wrote:

> Hi Mike,
>
> Thanks for the info.
> I should've mentioned:
>
> I'm using JDK 1.6.0, Groovy 1.6.0, MySQL 5.0.22 and MySQL's Connector
> 5.1.2.
>
> If no one else can reproduce it (in Groovy), I'll look for the reason
> for this somewhere else.
>
> Best regards.
>
> On Apr 27, 6:46 pm, Mike Dillon <[hidden email]> wrote:
>
>
>
> > begin AxiomShell quotation:
>
> > > Does anyone know how to retrieve a value by its column alias?
>
> > > I'll give you an example of what I'm doing:
>
> > > def rows = Database.sql.rows("select blah as aliased_blah from db")
> > > println rows
>
> > > will print:
>
> > > ["blah":"1"], ..., ["blah":"n"]
>
> > > when what I need is:
>
> > > ["aliased_blah":"1"], ..., ["aliased_blah":"n"]
>
> > > Is this a JDBC limitation?
>
> > It may be a problem with your driver. All of the drivers I've ever dealt
> > with (Oracle, MySQL, Postgres, MS SQL) would have "aliased_blah" for the
> > column name here.
>
> > -md
>
> > ---------------------------------------------------------------------
> > To unsubscribe from this list, please visit:
>
> >    http://xircles.codehaus.org/manage_email
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>    http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[Mike Dillon-4]

begin AxiomShell quotation:
> If no one else can reproduce it (in Groovy), I'll look for the reason
> for this somewhere else.

I just tested this using groovy.sql.Sql with the Postgres driver and it
worked as expected (the dumped items were keyed by the alias). So I
don't think this is Groovy's problem.

You could take a look at the ResultSetMetaData explicitly to see what's
happening:

    def rows = sql.rows("select blah as aliased_blah from db") { meta ->
        println "Column names:"
        println((1..meta.columnCount).collect { n -> meta.getColumnName(n) })
    }
    println "Data:"
    println rows

Just based on the relatively small amount of work I've done with MySQL,
I wouldn't be surprised if there is a config property somewhere that
toggles the behavior between the correct behavior and some arbitrary
non-compliant one that's "way faster" :)

-md

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[AxiomShell]

Many thanks for that Mike.

It's definitely some weird problem (not a obvious one for me at
least).

I logged into the database (the one used for my testing) and the raw
SQL produced the expected result (aliased columns).
I'll post in here if I find out the solution.

Thanks again.

On Apr 27, 10:05 pm, Mike Dillon <[hidden email]> wrote:

> begin AxiomShell quotation:
>
> > If no one else can reproduce it (in Groovy), I'll look for the reason
> > for this somewhere else.
>
> I just tested this using groovy.sql.Sql with the Postgres driver and it
> worked as expected (the dumped items were keyed by the alias). So I
> don't think this is Groovy's problem.
>
> You could take a look at the ResultSetMetaData explicitly to see what's
> happening:
>
>     def rows = sql.rows("select blah as aliased_blah from db") { meta ->
>         println "Column names:"
>         println((1..meta.columnCount).collect { n -> meta.getColumnName(n) })
>     }
>     println "Data:"
>     println rows
>
> Just based on the relatively small amount of work I've done with MySQL,
> I wouldn't be surprised if there is a config property somewhere that
> toggles the behavior between the correct behavior and some arbitrary
> non-compliant one that's "way faster" :)
>
> -md
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>    http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[AxiomShell]

Hi Mike,

Yes, I can now tell you it isn't a Groovy problem.
I tried the same script, on the same remote database from another
computer and the columns are aliased.
It must be some setting in my working environment.

All the best.

On Apr 27, 10:05 pm, Mike Dillon <[hidden email]> wrote:

> begin AxiomShell quotation:
>
> > If no one else can reproduce it (in Groovy), I'll look for the reason
> > for this somewhere else.
>
> I just tested this using groovy.sql.Sql with the Postgres driver and it
> worked as expected (the dumped items were keyed by the alias). So I
> don't think this is Groovy's problem.
>
> You could take a look at the ResultSetMetaData explicitly to see what's
> happening:
>
>     def rows = sql.rows("select blah as aliased_blah from db") { meta ->
>         println "Column names:"
>         println((1..meta.columnCount).collect { n -> meta.getColumnName(n) })
>     }
>     println "Data:"
>     println rows
>
> Just based on the relatively small amount of work I've done with MySQL,
> I wouldn't be surprised if there is a config property somewhere that
> toggles the behavior between the correct behavior and some arbitrary
> non-compliant one that's "way faster" :)
>
> -md
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>    http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[AxiomShell]

Ok.... this one gave me some trouble hunting down...
It's actually a MySQL bug:

http://bugs.mysql.com/bug.php?id=31499


On Apr 27, 10:05 pm, Mike Dillon <[hidden email]> wrote:

> begin AxiomShell quotation:
>
> > If no one else can reproduce it (in Groovy), I'll look for the reason
> > for this somewhere else.
>
> I just tested this using groovy.sql.Sql with the Postgres driver and it
> worked as expected (the dumped items were keyed by the alias). So I
> don't think this is Groovy's problem.
>
> You could take a look at the ResultSetMetaData explicitly to see what's
> happening:
>
>     def rows = sql.rows("select blah as aliased_blah from db") { meta ->
>         println "Column names:"
>         println((1..meta.columnCount).collect { n -> meta.getColumnName(n) })
>     }
>     println "Data:"
>     println rows
>
> Just based on the relatively small amount of work I've done with MySQL,
> I wouldn't be surprised if there is a config property somewhere that
> toggles the behavior between the correct behavior and some arbitrary
> non-compliant one that's "way faster" :)
>
> -md
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>    http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[Mike Dillon-4]

begin AxiomShell quotation:
> Ok.... this one gave me some trouble hunting down...
> It's actually a MySQL bug:
>
> http://bugs.mysql.com/bug.php?id=31499

Not sure this is a MySQL bug. They claim that JDBC provides a different
call for getting the alias: getColumnLabel(). I took a look at the
Javadocs and the JDBC 4.0 spec and it doesn't make it clear what the
distinction between getColumnName() and getColumnLabel() is. If
getColumnLabel() is supported by the driver's ResultSetMetaData, it may
make sense for Groovy-SQL to call it instead of getColumnName().

-md

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[Mike Dillon-4]

begin Mike Dillon quotation:

> begin AxiomShell quotation:
> > Ok.... this one gave me some trouble hunting down...
> > It's actually a MySQL bug:
> >
> > http://bugs.mysql.com/bug.php?id=31499
>
> Not sure this is a MySQL bug. They claim that JDBC provides a different
> call for getting the alias: getColumnLabel(). I took a look at the
> Javadocs and the JDBC 4.0 spec and it doesn't make it clear what the
> distinction between getColumnName() and getColumnLabel() is. If
> getColumnLabel() is supported by the driver's ResultSetMetaData, it may
> make sense for Groovy-SQL to call it instead of getColumnName().

It looks like the Java 6 docs are actually clearer:[1]

    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.

It looks like it would probably be safe for groovy.sql.Sql to use
getColumnLabel and fall back to getColumnName, assuming it's been in
JDBC since earlier versions and wasn't added to RSMD later.

-md

[1] http://java.sun.com/javase/6/docs/api/java/sql/ResultSetMetaData.html

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[AxiomShell]

Yes, you are right.

I bypassed this problem (it's not a real solution, but it works) by
adding

?useOldAliasMetadataBehavior=true

to my JDBC connection string, and now it works ok.

All the best

On May 7, 4:47 am, Mike Dillon <[hidden email]> wrote:

> begin Mike Dillon quotation:
>
> > begin AxiomShell quotation:
> > > Ok.... this one gave me some trouble hunting down...
> > > It's actually a MySQL bug:
>
> > >http://bugs.mysql.com/bug.php?id=31499
>
> > Not sure this is a MySQL bug. They claim that JDBC provides a different
> > call for getting the alias: getColumnLabel(). I took a look at the
> > Javadocs and the JDBC 4.0 spec and it doesn't make it clear what the
> > distinction between getColumnName() and getColumnLabel() is. If
> > getColumnLabel() is supported by the driver's ResultSetMetaData, it may
> > make sense for Groovy-SQL to call it instead of getColumnName().
>
> It looks like the Java 6 docs are actually clearer:[1]
>
>     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.
>
> It looks like it would probably be safe for groovy.sql.Sql to use
> getColumnLabel and fall back to getColumnName, assuming it's been in
> JDBC since earlier versions and wasn't added to RSMD later.
>
> -md
>
> [1]http://java.sun.com/javase/6/docs/api/java/sql/ResultSetMetaData.html
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>    http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email