search

RDBI / rdbi-driver-odbc

ODBC driver for RDBI
MIT License
1 stars 11 forks source link

Aggregate Column Names #1

Closed drychlik closed 13 years ago

drychlik commented 13 years ago

Hello, This issue may or may not be a bug. I query a DB2 database using the rdbi-driver-odbc. The sql query that I use in this case appears like this. "select concat(t.col1, t.col2, t.col3) from t.table left join j.table on (*clause)"

When I begin to process results, the column header for the data appears as "00001" instead of "concat(t.col1, t.col2, t.col3)"... This is significant because I use common work fields to data intersect with other hashes for a data migration utility that I am developing in ruby.

In PERL I simply pass an additional parameter to solve this.
e.g. while (my $ref = $$sth->fetchrow_hashref('NAME_lc')) NAME_lc flips the "00001" back to what you would expect.

What are you thoughts on this?

Respectfully, Daniel Rychlik

semmons99 commented 13 years ago

I'll see what I can come up with today.

semmons99 commented 13 years ago

After fixing an issue related to primary keys, this seems to be an issue with the underlying ODBC driver, not the gem itself. When I test an ODBC connection to MySQL you get the column name returned as the original aggregate query, but when testing DB2, you just get a number representing the aggregate query.

I work with DB2 quite a bit, and to fix this myself, I always just write the query as follows:

SELECT CONCAT(t.col1, t.col2) AS 'CONCAT(t.col1, t.col2)' ...

I've always had this problem, even when using DB2 direct on the mainframe. I think it's just a DB2 limitation. I'm guessing the perl version parses your select statement and overrides any driver provided names with what it thinks they should be; I'm not really sure that's the right way to do things.

If you want to contribute a patch to try and add this feature, please feel free. Though it might be better to talk to the maintainer of the ruby-odbc gem, since that's what we use to execute ODBC queries.

drychlik commented 13 years ago

Thanks so much for looking into it. I was hoping you could cup cake something together. Last night the thought hit me that I could alias the column name with a string representation. I said to myself, if Semmons found this to be true, then I'll just do exactly that.
I will look at the PERL implementation and hopefully be able to provide a patch for this. I built a SQL query builder object in ruby that simply builds a query based on hash values. Adding an alias is trivial in my case.