headers
Eric Hochmeister | 17 Jul 22:30

ODBC, JOIN table with same column names.

Hi,

I'm using the ODBC driver for squeak and I was running into an issue
with JOIN tables where the 2 tables I'm joining have identical column
names.
ie. I have a table X and table Y.
both tables have columns ID, FIRST_NAME

So when I do the query,

SELECT c1.ID, c1.FIRST_NAME, d1.FIRST_NAME FROM X c1, Y d1 WHERE c1.ID = d1.ID

I get back rows which only have ID and FIRST_NAME (2 columns), rather than 3.

I'm just curious if this is a known bug, or if I'm missing something,
ie. not doing this correctly.  Has anyone else run in to this issue,
or do you solve it differently?

Thanks,

Eric
Permalink | Reply |
headers
| 18 Jul 01:26
Gravatar

Re: ODBC, JOIN table with same column names.

Hi:

You are right. I tried and have the same behaviour, never detected
before. (I tried with an Access DB builded fastly, but the problem is
the same you comment).

Never heard about this behaviour, but if time permit, I will investigate a bit.

Cheers.
Germán.

2008/7/17 Eric Hochmeister <erichochmeister <at> gmail.com>:
> Hi,
>
> I'm using the ODBC driver for squeak and I was running into an issue
> with JOIN tables where the 2 tables I'm joining have identical column
> names.
> ie. I have a table X and table Y.
> both tables have columns ID, FIRST_NAME
>
> So when I do the query,
>
> SELECT c1.ID, c1.FIRST_NAME, d1.FIRST_NAME FROM X c1, Y d1 WHERE c1.ID = d1.ID
>
> I get back rows which only have ID and FIRST_NAME (2 columns), rather than 3.
>
> I'm just curious if this is a known bug, or if I'm missing something,
> ie. not doing this correctly.  Has anyone else run in to this issue,
> or do you solve it differently?
>
(Continue reading)

Permalink | Reply |
headers
| 18 Jul 01:39
Gravatar

Re: ODBC, JOIN table with same column names.

Forgot to say that is an ODBC problem because the same select running
natively on Access works ok.

I will record this problem as bug to be solved on the wiki ODBC page.

Cheers.
Germán.

2008/7/17 Germán Arduino <garduino <at> gmail.com>:
> Hi:
>
> You are right. I tried and have the same behaviour, never detected
> before. (I tried with an Access DB builded fastly, but the problem is
> the same you comment).
>
> Never heard about this behaviour, but if time permit, I will investigate a bit.
>
> Cheers.
> Germán.
>
>
> 2008/7/17 Eric Hochmeister <erichochmeister <at> gmail.com>:
>> Hi,
>>
>> I'm using the ODBC driver for squeak and I was running into an issue
>> with JOIN tables where the 2 tables I'm joining have identical column
>> names.
>> ie. I have a table X and table Y.
>> both tables have columns ID, FIRST_NAME
>>
(Continue reading)

Permalink | Reply |
headers
marcelo Cortez | 18 Jul 03:10

Re: ODBC, JOIN table with same column names.

German

what odbc and database you are using?

best 
mdc

--- El jue 17-jul-08, Germán Arduino <garduino <at> gmail.com> escribió:

> De: Germán Arduino <garduino <at> gmail.com>
> Asunto: Re: [squeak-dev] ODBC, JOIN table with same column names.
> Para: "The general-purpose Squeak developers list" <squeak-dev <at> lists.squeakfoundation.org>
> Fecha: jueves, 17 de julio de 2008, 8:39 pm
> Forgot to say that is an ODBC problem because the same
> select running
> natively on Access works ok.
> 
> I will record this problem as bug to be solved on the wiki
> ODBC page.
> 
> Cheers.
> Germán.
> 
> 
> 2008/7/17 Germán Arduino <garduino <at> gmail.com>:
> > Hi:
> >
> > You are right. I tried and have the same behaviour,
> never detected
> > before. (I tried with an Access DB builded fastly, but
(Continue reading)

Permalink | Reply |
headers
| 18 Jul 12:55
Gravatar

Re: ODBC, JOIN table with same column names.

Hi Marcelo:

Tried this test with the latest ODBC (loaded from Universes, image
3.10u3 from Lex Spoon) and an Access DB (Don't sure of the version,
may be 2000).

2008/7/17 marcelo Cortez <jmdc_marcelo <at> yahoo.com.ar>:
> German
>
> what odbc and database you are using?
>
> best
> mdc
>
>
>
>
> --- El jue 17-jul-08, Germán Arduino <garduino <at> gmail.com> escribió:
>
>> De: Germán Arduino <garduino <at> gmail.com>
>> Asunto: Re: [squeak-dev] ODBC, JOIN table with same column names.
>> Para: "The general-purpose Squeak developers list" <squeak-dev <at> lists.squeakfoundation.org>
>> Fecha: jueves, 17 de julio de 2008, 8:39 pm
>> Forgot to say that is an ODBC problem because the same
>> select running
>> natively on Access works ok.
>>
>> I will record this problem as bug to be solved on the wiki
>> ODBC page.
>>
(Continue reading)

Permalink | Reply |
headers
Eric Hochmeister | 18 Jul 19:14

Re: ODBC, JOIN table with same column names.

The column names in the buffers from the ODBC client doesn't contain
the alias preceeding the column name but just the column name itself.

I made the following change, I have not optimized it or anything, so
its very very rough, I just wanted a quick working result (proof of
concept), but I believe the method should be sufficient once made
robust and to handle weird cases.

The change is in ODBCResultSet>>fetchRow
======================================

fetchRow
	"private - fetch the next row"
	| row ret querySubstrings |
	ret _ ODBCLibrary default sqlFetch: handle.
	ret == SQLNODATAFOUND
		ifTrue: [^ nil].
	connection checkSQLReturn: ret statement: handle.
	""
	row _ ODBCRow new: columns size.
	querySubstrings := statement query substrings collect:[:each | each
trimCommas ].
	columns
		do: [:each |
			( statement query isSelectQuery ) ifTrue:[
				row at:(querySubstrings at:((columns indexOf:each) + 1)) asSymbol
put:each data]
			ifFalse:[
				row at: each name put: each data]].
	^ row
(Continue reading)

Permalink | Reply |
headers
Todd Blanchard | 18 Jul 22:30

Re: ODBC, JOIN table with same column names.

I bet you can work around it with aliases though.

SELECT c1.ID as c1_ID, c1.FIRST_NAME as c1_FIRST_NAME, d1.FIRST_NAME  
as d1_FIRST_NAME FROM X c1, Y d1 WHERE c1.ID = d1.ID

-Todd Blanchard

On Jul 17, 2008, at 1:32 PM, Eric Hochmeister wrote:

> Hi,
>
> I'm using the ODBC driver for squeak and I was running into an issue
> with JOIN tables where the 2 tables I'm joining have identical column
> names.
> ie. I have a table X and table Y.
> both tables have columns ID, FIRST_NAME
>
> So when I do the query,
>
> SELECT c1.ID, c1.FIRST_NAME, d1.FIRST_NAME FROM X c1, Y d1 WHERE  
> c1.ID = d1.ID
>
> I get back rows which only have ID and FIRST_NAME (2 columns),  
> rather than 3.
>
> I'm just curious if this is a known bug, or if I'm missing something,
> ie. not doing this correctly.  Has anyone else run in to this issue,
> or do you solve it differently?
>
> Thanks,
(Continue reading)

Permalink | Reply |
headers
Eric Hochmeister | 18 Jul 23:04

Re: ODBC, JOIN table with same column names.

Great tip... and yeah it works. Thanks!

On Fri, Jul 18, 2008 at 4:30 PM, Todd Blanchard <tblanchard <at> mac.com> wrote:
> I bet you can work around it with aliases though.
>
> SELECT c1.ID as c1_ID, c1.FIRST_NAME as c1_FIRST_NAME, d1.FIRST_NAME as
> d1_FIRST_NAME FROM X c1, Y d1 WHERE c1.ID = d1.ID
>
> -Todd Blanchard
>
> On Jul 17, 2008, at 1:32 PM, Eric Hochmeister wrote:
>
>> Hi,
>>
>> I'm using the ODBC driver for squeak and I was running into an issue
>> with JOIN tables where the 2 tables I'm joining have identical column
>> names.
>> ie. I have a table X and table Y.
>> both tables have columns ID, FIRST_NAME
>>
>> So when I do the query,
>>
>> SELECT c1.ID, c1.FIRST_NAME, d1.FIRST_NAME FROM X c1, Y d1 WHERE c1.ID =
>> d1.ID
>>
>> I get back rows which only have ID and FIRST_NAME (2 columns), rather than
>> 3.
>>
>> I'm just curious if this is a known bug, or if I'm missing something,
>> ie. not doing this correctly.  Has anyone else run in to this issue,
(Continue reading)

Permalink | Reply |

Gmane