--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:

> >The problem is that there is no way to access the correct field in
> >a dataset, when the query returns two (or more) columns with the
> > same name.

> Exactly. That's why SQL provides aliasing - to resolve such
> redundancies.

I traced into IBO's code and apparently IBO *does* support access to
fields using fully qualified names. So there's no ambiguity and I set
the value of the correct field.

However the value given to the (correct) field isn't passed to the API
call. Values of other fields are passed just fine.

> The point I was making was that a joined set is not
> naturally updatable. Therefore, you are up the creek if you refuse
> to use aliases to resolve redundancies and simplify what you do
> with IBO.
>
> SELECT aaa.num,
> aaa.name,
> ppp.num AS pp_num,
> ppp.mod_date
> FROM aaa JOIN ppp
> ON ppp.aaa_id=aaa.id // ppp is a SP
>
> That's all that is needed for your InsertSQL to work:
>
> INSERT INTO aaa VALUES (:id, :num, :name)

Yes, I understand and I tried it and it works. But *why* does it work
with aliases, and doesn't work when I use fully qualified names?

In both cases IBO accesses the correct field, but only in the aliased
version the value I store in the field actually gets to the table.

> However, if you insist on avoiding the aliasing

I don't "insist" for the fun of it. I want to find the simplest
solution and I want to understand how things work.

The example I've given is a very simplified test case to demonstrate
the problem. The actual situation is a bit more complicated - the
queries are bigger and generated at run time, so I have to figure out
(in code) which columns need aliases, etc.

I assumed that the most logical and efficient solution is to use fully
qualified names everywhere. No ambguity whatsoever. Only it doens't
work...

Between the postings here, I've implemented support for aliases -
instead of generating SQL with fully qualified columns (eg. "aaa.id"),
now I also generate an alias for each column (eg. "aaa.id AS aaa_id").

It works, but I still wonder what's wrong with "aaa.id" and why it's
less unique than "aaa_id".

Thanks (as usual) for your help.

Eyal.