Hello Brian,

At 10:18 AM 31/12/2003 +0800, you wrote:

>Jason,
>
>I have found a compatibility issue upgrading from IBO3.6Cf to IBO4.3Aa.
>After the upgrade several IBOQueries did not work as before, but gave an
>error "XXXX is a required field" when inserting new records.
>
>This is what I have found will provoke the problem.
>
>SQL select * from customer c
>join sales s on c.cust_no = s.cust_no
>
>ColumnAttributes CUST_NO=REQUIRED
>
>InsertSQL INSERT INTO SALES(
> PO_NUMBER, /*PK*/
> CUST_NO,
> SALES_REP,
> ORDER_STATUS,
> ORDER_DATE,
> SHIP_DATE,
> DATE_NEEDED,
> PAID,
> QTY_ORDERED,
> TOTAL_VALUE,
> DISCOUNT,
> ITEM_TYPE)
>VALUES (
> :PO_NUMBER,
> :CUST_NO,
> :SALES_REP,
> :ORDER_STATUS,
> :ORDER_DATE,
> :SHIP_DATE,
> :DATE_NEEDED,
> :PAID,
> :QTY_ORDERED,
> :TOTAL_VALUE,
> :DISCOUNT,
> :ITEM_TYPE)
>
>Now, put the query into INSERT state and set values for all of the above
>parameters and post. An error "CUST_NO is a required field" will result.
>
>The problem appears to be due to CUST_NO appearing twice (CUST_NO and
>CUST_NO1) in the SQL statement (not the insert statement). If the SQL is
>changed so that CUST_NO appears only once, then everything works as
>expected.

What you observe here isn't a bug - though, in a sense, it is a couple of
incompatibilities arising from tightening up of things to do with joins
with the advent of bug-fixes in the Firebird servers and, to a lesser
degree, in the IB servers. Where ambiguous SQL such as your example above
used to be allowed, now it throws an exception.

Changes you will need in this style of query are:

1) to qualify *everything* in joins.

The SELECT statement above is not good SQL in any event (never was) because
it doesn't output any columns from the right-hand stream. It is more
robust to use the EXISTS() predicate when you only want to test for the
existence of a matching stream in a different table. However, until your
syntax becomes deprecated, you can make it valid by correctly qualifying
the output columns from the left-hand stream:

select c.* from customer c
join sales s on c.cust_no = s.cust_no

The more robust way to do this query would be:

select c.* from customer c
where exists(select 1 from sales s where s.cust_no = c.cust_no)

"Even more robust" is to avoid select * altogether in multi-table queries.


2) to qualify everything also in your xxxxSQL statements that are linked to
joins.

3) to use the KeyRelation property of the dataset to tell IBO which of the
underlying tables your XXXSQL is to act upon

In *this particular case* (because you have no output columns that are
present in Sales) you may get away with 3) on its own, without having to
write a fully qualified insert statement. I'm pretty sure that IBO will be
able to resolve the ambiguities provided you correctly assign the KeyRelation:

KeyRelation=Sales

If in fact you find that *neither* solves the problem - a possibility
because of the "illegal" join statement making it impossible to establish
KeyLinks accurately - then turn off RequestLive and use a separate,
parameterised TIB_DSQL for the insert operation. Manually pass the
FieldByName values from the joined structure to the ParamByName values of
the insert statement. Keep the IB_DSQL in the same transaction, though...

The old order changeth. Modernisation and cleanup of the old Borland code
is making all of us take care of sloppy SQL that we used to get away
with. Do get used to using qualifiers always in joins. Some of us never
use "select *" for anything, on principle, so that there is always a very
obvious trace path when column-related exceptions show up.

regards,
Helen