Smalltalk › Squeak › Squeak - Dev

[squeak-dev] ODBC, JOIN table with same column names.

‹ Previous Topic Next Topic ›
 
classic Classic list List threaded Threaded
♦
♦
 8 messages Options
Eric Hochmeister-2
Reply | Threaded
Open this post in threaded view
♦
♦
|

[squeak-dev] ODBC, JOIN table with same column names.

Eric Hochmeister-2
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

garduino
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: [squeak-dev] ODBC, JOIN table with same column names.

garduino
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 <[hidden email]>:

> 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
>
>

garduino
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: [squeak-dev] ODBC, JOIN table with same column names.

garduino
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 <[hidden email]>:

> 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 <[hidden email]>:
>> 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
>>
>>
>



--
Germán S. Arduino
http://www.arsol.biz
http://www.arsol.net
marcelo Cortez
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: [squeak-dev] ODBC, JOIN table with same column names.

marcelo Cortez
German

what odbc and database you are using?

best
mdc




--- El jue 17-jul-08, Germán Arduino <[hidden email]> escribió:

> De: Germán Arduino <[hidden email]>
> Asunto: Re: [squeak-dev] ODBC, JOIN table with same column names.
> Para: "The general-purpose Squeak developers list" <[hidden email]>
> 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 <[hidden email]>:
> > 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
> <[hidden email]>:
> >> 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
> >>
> >>
> >
>
>
>
> --
> Germán S. Arduino
> http://www.arsol.biz
> http://www.arsol.net


      ____________________________________________________________________________________
¡Buscá desde tu celular!

Yahoo! oneSEARCH ahora está en Claro

http://ar.mobile.yahoo.com/onesearch
garduino
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: [squeak-dev] ODBC, JOIN table with same column names.

garduino
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 <[hidden email]>:

> German
>
> what odbc and database you are using?
>
> best
> mdc
>
>
>
>
> --- El jue 17-jul-08, Germán Arduino <[hidden email]> escribió:
>
>> De: Germán Arduino <[hidden email]>
>> Asunto: Re: [squeak-dev] ODBC, JOIN table with same column names.
>> Para: "The general-purpose Squeak developers list" <[hidden email]>
>> 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 <[hidden email]>:
>> > 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
>> <[hidden email]>:
>> >> 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
>> >>
>> >>
>> >
>>
>>
>>
>> --
>> Germán S. Arduino
>> http://www.arsol.biz
>> http://www.arsol.net
>
>
>      ____________________________________________________________________________________
> ¡Buscá desde tu celular!
>
> Yahoo! oneSEARCH ahora está en Claro
>
> http://ar.mobile.yahoo.com/onesearch
>
>

Eric Hochmeister-2
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: [squeak-dev] ODBC, JOIN table with same column names.

Eric Hochmeister-2
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

======================================


On Fri, Jul 18, 2008 at 6:55 AM, Germán Arduino <[hidden email]> wrote:

> 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 <[hidden email]>:
>> German
>>
>> what odbc and database you are using?
>>
>> best
>> mdc
>>
>>
>>
>>
>> --- El jue 17-jul-08, Germán Arduino <[hidden email]> escribió:
>>
>>> De: Germán Arduino <[hidden email]>
>>> Asunto: Re: [squeak-dev] ODBC, JOIN table with same column names.
>>> Para: "The general-purpose Squeak developers list" <[hidden email]>
>>> 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 <[hidden email]>:
>>> > 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
>>> <[hidden email]>:
>>> >> 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
>>> >>
>>> >>
>>> >
>>>
>>>
>>>
>>> --
>>> Germán S. Arduino
>>> http://www.arsol.biz
>>> http://www.arsol.net
>>
>>
>>      ____________________________________________________________________________________
>> ¡Buscá desde tu celular!
>>
>> Yahoo! oneSEARCH ahora está en Claro
>>
>> http://ar.mobile.yahoo.com/onesearch
>>
>>
>
>

tblanchard
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: [squeak-dev] ODBC, JOIN table with same column names.

tblanchard
In reply to this post by Eric Hochmeister-2
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,
>
> Eric
>


Eric Hochmeister-2
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: [squeak-dev] ODBC, JOIN table with same column names.

Eric Hochmeister-2
Great tip... and yeah it works. Thanks!

On Fri, Jul 18, 2008 at 4:30 PM, Todd Blanchard <[hidden email]> 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,
>> or do you solve it differently?
>>
>> Thanks,
>>
>> Eric
>>
>
>
>

Free forum by Nabble Edit this page