Background:
Let's say I have a table Owners where records can be of type "person" or
"company", one table Persons with one record for each person-type owner
record, and finally a table Companies with one record for each
company-type owner record. The purpose is of course that while both
persons and companies can own things, there require quite different
column sets themselves.

Now, at one point I need to work with persons only, but need the columns
from both Owners and Persons. So I create a view that joins all
person-type Owner records with their corresponding Person records
(one-to-one).

The problem:
In short - I have a dataset (TQuery) which joins two tables, and I need
to update both underlying tables.

This recordset cannot be "live" because it joins two tables. (Btw,
Access handles this fine with live recordsets - why can't BDE?) So, to
be able to update, I use cached updates and an UpdateSQL component. But,
each updateSQL can update one table only, so I need two of them. Fine!
But the dataset (TQuery) can only use one UpdateSQL component!

How do I solve this? I'm guessing I could set the DataSet property of
each UpdateSQL component at runtime. But then I would have to manually
trigger them in the appropriate event of the dataset, right? Isn't there
a better way?

Kjell

Hi,
so use OnUpdateRecordEvent
for example,

procedure TForm.Query1UpdateRecord(DataSet: TDataSet;
  UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);
begin
  case UpdateKind of
    ukDelete: ...
    ukInsert: ...
    ukModify: ...
  end;
end;

and replace ... with queries that updates your 2 tables

Mark

You could also call a stored procedure from the updatesql component and update
your two tables from there..

You are right to set the Dataset property of each UpdateSQL. After
that, you must consider two things.

1. Set the value back to nil when you close the form or destroy the
data module that the UpdateSQL components are on. If you do not, then
Delphi can run into access violations.

2. Because the "Dataset" is a join, then Delphi will think that all
changes to the underlying tables are modifications only. If all the
changes are indeed modifications (no deletes or inserts) then you
don't have to do anything: Delphi will handle both UpdateSQL
components automatically.

But if an UpdateSQL table change can be an insert or a delete, then
you must write some code in the Dataset's OnUpdateRecord event. That
event will always start with ukModify (because of the join). You must
then figure out (in your code) if there is an insert to be done and
then say:
        UpdateSQL.Apply(ukInsert);
where that kind of action is appropriate.

Phil Cain
--

OK, can't see why, but even the VCL isn't perfect. ;)  OnDestroy should
be a good place.

Not what I'm seeing! What I've done is, in the AfterPost handler of
MyQuery (transaction stuff left out for brevity):

procedure TMyDM.MyQueryAfterPost(DataSet: TDataSet);
  begin
    MyQuery.ApplyUpdates;
    MyQuery.CommitUpdates;
  end;

This is because I want all updates to be posted to the server right
away. Finally, in the OnUpdateRecord handler of MyDataSet, I have
(transaction stuff left out for brevity):

procedure TMyDM.MyQueryUpdateRecord(DataSet:TDataSet;
    UpdateKind:TUpdateKind;
    var UpdateAction:TUpdateAction);
  begin
    FirstUpdateSQL.DataSet:=MyQuery;
    SecondUpdateSQL.DataSet:=MyQuery;
    FirstSQLUpdate.Apply(UpdateKind);
    SecondSQLUpdate.Apply(UpdateKind);
    UpdateAction:=uaApplied;
  end;

(With a few extra operations to handle id fields but that's irrelevant.)
UpdateKind correctly contains ukInsert on inserts, and so forth.

To sum up, I think I've solved the problem, but what I was trying to
find out (with your kind help) is whether there is a way to do it
without having to code an OnUpdateRecord event handler. I take it there
isn't. Thank you for your replies!

Kjell

There's a bug in the VCL regarding TUpdateSQL datasets. If you set the
connection, say, in TQuery.UpdateObject, then the VCL does all the
right things on destroy. That is, it finds the UpdateObject and deals
with it properly. But if you set the connection in TUpdateSQL.Dataset,
the VCL doesn't check for that properly. If it then destroys the
TQuery first, it tries to destroy the TQuery again when it sees the
reference in TUpdateSQL and that's an access violation. You could
manage this by setting the creation order of the components, but I
find it easier to reset the TUpdateSQL.Dataset property in the data
module's destroy method.

And you are right. My explanation was too simple. You can still get
inserts and deletes, but consider this:

Suppose you join two tables this way: Table A left join TableB. What
you get is a dataset that thinks that it's fully populated. You can
insert and delete and OnUpdateRecord will show insert or delete.

But suppose that all you do is to change a value in one field from
TableB. Given the way you joined the tables, there may be a TableB
record and there may not. The database will actually create a dummy
record of all nulls if it can't find a matching TableB record.

But cached updates doesn't know this. All it can see is a record with
nulls in it. When you change a TableB value, you will always get a
ukModify in OnUpdateRecord. In OnUpdateRecord, you have to test the
OldValue on  the key fields of TableB to check if the record actually
exists. If the OldValues of the key fields are Null, then you must 1)
populate those fields and 2) use TUpdateSQL.Apply(ukInsert).
Otherwise, you can accept the ukModify and proceed normally.

Phil Cain
--

[Explanation of why TUpdateSQL.DataSet must be set to nil if assigned
directly instead of via TQuery.UpdateSQL]
[Explanation of when and why OnUpdateRecord received ukModify when it
should be ukInsert]

Thank you Phil, for your very enlightening post!

Kjell