At 11:41 AM 20/06/2005 +0000, you wrote:

>Hi,
>
>I use an IB_Query to compare table rows to an external source and
>insert rows that don't exists in the table. I generate the query in
>code and provide separate SQLs for select and insert. Here is a
>simplified version of those queries (the actual version is too long):
>
>SELECT aaa.num, aaa.name, ppp.num, ppp.mod_date
>FROM aaa
>JOIN ppp ON ppp.aaa_id=aaa.id // ppp is a SP
>
>INSERT INTO aaa VALUES (:id, :num, :name)
>
>I read data from the external source, and use Locate to check if it's
>already in the dataset. If not, I use the following code to insert the
>new row (again simplified):
>
>query.Insert;
>query['id']:=query.GeneratorValue('gen_aaa_id', 1);
>query['num']:=...;
>query['name']:=...;
>query.Post;
>
>Problems/questions:
>
>
>1. When I looked at the resulting table I noticed that the 'num'
>column was empty in all new rows . I was puzzled at first because I
>explicitly set the value of the num field.
>
>Then I realized that I have two fields named 'num', one from table
>'aaa' and one from the SP 'ppp'. So I intended to set 'aaa.id' but
>ended up setting 'ppp.id'.

That isn't possible. You can't "set" values for a stored procedure, since
it has no existence as a table object in the database.

>How do I access the correct field when there are several fields with
>the same name?

The way you are doing this (which IMO is inherently messy!!) you should
write an executable stored procedure for the insert operation and use that
code to determine whether the row you are considering already
exists. Don't use client-side looping methods for this, *ESPECIALLY* when
the rows in the dataset are virtual.

>2. I tried to be clever and use the fully qualified name -
>query['aaa.num']:=... only to discover that IBO ignores the table name
>and only uses the field name.

IBO can't do DSQL that the engine doesn't support. SQL does not support
referring to multiple tables in an insert statement, other than the
straight INSERT INTO ATABLE (f1, f2, ...)
select blah1, blah2, ... from BTABLE

Not to mention the more problemmatical fact that some of the fields in your
set are virtual and so have no table to be identified by!

>Is this a bug or an intended behavior? Is there any way to access
>fields using the fully qualified name?

No. If you intend to proceed with your current approach, then move the
INSERT operation out of the dataset altogether and make it a stand-alone
execute DSQL with its own params. Note also that parameter assignment
needs a casting method (AsInteger, AsString, etc.)

>A possible solution is to use aliases for all fields, but this makes
>query building very cumbersome, and really pointless as "table name +
>field name" already provides a unique identifier.

Since no joined set is naturally updatable, the use of aliases won't make
any difference to the "updatability" of the set. In SQL joining, aliases
resolve ambiguity. It's ironic that you object to column aliasing as
cumbersome, when you have chosen just about the most cumbersome approach
imaginable to solving the overall problem!

Helen