At 10:30 AM 24/12/2002 +0100, Gerard J.M.Houtenbos wrote:

>Does the fact that knowbody has followed this thread, mean that
>it is not possible?

I saw your question about the same time my Windows box was attacked by a
Trojan. I hope I'm not too late to offer some pointers now.

> >I do have two tables in an Interbase 6 database accessed by
> >IB-Objects:
> >
> >Players
> >P_Key: Integer;
> >Name: String;
> >CaptainKey: Integer;
> >
> >Captains
> >P_Key: Integer;
> >Name: String;
> >
> >I use a TwwDbGrid in which I need two columns:
> >Players.Name
> >Captains.Name
> >
> >There are three necessities:
> >1. The Players.Name must be editable.
> >2. The Captains.Name must be selectable from a LookUpCombo
> >3. The grid must be indexed on either column by means of the
> >TitleButtonClick-method.
> >
> >I tried a join-TIBOquery, but then the Captains.Name isn't
> >editable.
> >
> >I tried to use a TIBOquery with a lookupfield for the
> >Captains.Name, but then I can't index on the Captains.Name using
> >OrderingItems.
> >
> >I tried a ClietDataSet - DataSetProvider combination connected to
> >the same query with the lookupfield, but then the Captains.Name
> >is not editable.
> >
> >How do I solve this problem?

You can implement this using either a subquery or a join to get the
captain's name. You have to realise that neither joined nor computed
outputs are "naturally updatable" in SQL. In the case of the joined set,
NO fields are naturally updatable; in the case of the subquery, the
subqueried column is not updatable.

Now, IBO can make a joined set "live" in one of two ways, either:

by indicating, in KeyRelation, one table which is to be editable; or

by providing SQL statements in the xxxxSQL properties, which are
parameterised calls to stored procedures which you have written to provide
the necessary updates, inserts or deletes to the underlying tables.

In all cases, after updates, the IBOQueries will have to be refreshed,
because the existing rows in the buffer may no longer comply with the join
criteria.

If you take the join approach, your main dataset's query would be:

SELECT Players.P_Key, Players.Name, Players.CaptainKey
Captains.P_Key, Captains.Name
FROM Players
JOIN Captains
ON Players.CaptainKey = Captains.P_Key

The KeyLinks here would be:

Players.P_Key
Captains.P_Key

For the lookup dataset, your query would be

Select P_Key, Name
from Captains
Order by Name

Just use KeyLinksAutoDefine and Requestlive for this query.

I think for the wwDbGrid, to do what you want, you will need to have both
ascending and descending indexes on Captain.Name and Players.Name. I don't
have these components but presumably you can nominate named indexes to use
for the reordering on TitleBarClick.

If you are permitting the Captains data in the lookupcombo to be editable,
then you will have to do several things to make the main dataset
respond. For example, you will have to
-- perform a positioned update on the whole underlying Players table (not
just the records apparent in the main dataset) if you delete a Captains
row; and invalidate and refresh the main dataset
-- provide the means capture the change event of the lookup dataset, pass
the Captains P_Key of that set across to the UpdateSQL of the main dataset,
post the update (Edit call) and invalidate the current record.
-- and probably other things I don't know about.

If you use a TDBLookupComboBox, you can get Delphi to perform some parts of
these tasks.

If you go the correlated subquery route, you still need to invalidate and
requery in response to changes, either in the Captains table or in the
foreign key relationship. You won't be able to order the main dataset by
the subselected column, because it is not a database column, but a computed
column. You can still use it to order the set but each reordering event's
SQL must resolve to

SELECT Players.P_Key, Players.Name,
(select Captains.Name from Captains
where Captains.P_Key = Players.CaptainKey) as CaptainName
FROM Players
ORDER BY 3 (alternating with ORDER BY 3 DESC)

In this case, KeyLinksAutoDefine and RequestLive will work. You need to
tell IBO that CaptainName is a computed field - do this by instantiating
the field object and setting its FieldKind to fkInternalCalc - if Delphi
doesn't do it for you.

Of course, the Captains.Name (CaptainName) won't be editable in this
dataset. You would still need to provide the change mechanisms via the
lookup dataset.

I think the correlated subquery route might be less efficient than the
join, especially if you aren't limiting the rows with a where clause.

regards,
Helen