Hi All,

How does one update several tables from one query?

In design time, Am I supposed to set the Query's update object to "upd1"
 and pick a table and the then generate & modify the sql
AND THEN set the Query's update object ot a different updateSQL, say
"upd1," edit that object using a different table

AND THEN create an OnUpdateRecord event handler that calls both of the
objects? If so this fells a little "rinky-dink" why cant I have a comma
or semi-colon delimited series of objects in the query's update object
property? or ...

Any help appreciated

ed

Ed,

You can do it by using the unpublished TUpdateSQL.Dataset property.

If you main query is named MyQuery and if MyQuery returns data from a
join (two or more tables) then.

when you create the form:
        UpdateSQL1.DataSet := MyQuery;
        UpdateSQL2.DataSet := MyQuery;

Do this instead of setting the MyQuery.UpdateObject, which you must
leave blank.

use each UpdateSQL for one of the tables you need to update.

Then when closing or destroying the form:
        UpdateSQL1.DataSet := nil;
        UpdateSQL2.DataSet := nil;
{this bit avoids an AV on close}

Good luck.

Phil Cain

--

Thanks Philip,

But this means that I still have to perform my little subtrefuge to get
the statements generated in the first place. And then, does the
component really need to "know" the name of the dataset. The example in
the manual implies not. (developer's guide , p 25-23).

ed

Ed,

I don't know what version of Delphi you are using (D3 has the cached
updates discussion in Chapter 13), but all the documents on this are
notoriously weak.

The truth is that TUpdateSQL is tied at the hip to the dataset. That
is, there is no way to use TUpdateSQL without the connection. The
reason is that TUpdateSQL creates TQuery components on the fly to
execute the SQL you put there and the parameter list for those queries
is controlled by the select list of the connected dataset. So, for
example, if you write a query that says:
        select name, address from customerlist......
there is no possible way to get a TUpdateSQL to make any sense of a
parameter called :PHONE. The only ones allowed for your query are
:NAME and :ADDRESS. (and :OLD_NAME and :OLD_ADDRESS, of course).

Another limitation is that an instance of TUpdateSQL is meant for
updating one table only. That is, each query in the TUpdateSQL is
meant for one table. This may not be a physical limitation; I've heard
people say that they've successfully put multiple SQL statements in a
TUpdateSQL query  or done some things there with stored procedures,
but I find all that too fussy. It's much easier and clearer to limit a
TUpdateSQL to one table and that's the design intent anyway.

But I do use a trick at design time to help things along. One by one,
I set the TQuery.UpdateObject to one of the TUpdateSQL components and
use design time RTTI to make the TUpdateSQL find the table, fields,
and keys and produce the default SQL. Then getting to the right SQL is
a bit easier than writing on a blank page. (I have to note here that
all the tables in the query do not show up on TUpdateSQL's drop down
list. This is a bug in TUpdateSQL. But you work around it by simply
typing one of the table names in the box and the other buttons on the
editor will respond properly for that table.)

Then, of course, after I've done that, I set the TQuery.UpdateObject
back to blank and use code to set up the connection at run time.

You have to fuss with the SQL anyway. TUpdateSQL was not designed to
be an automatic thing. It's default SQL is not elegant, does not
ensure success, and will work only with the simplest of problems.

BTW, OnUpdateRecord is not a required process. If you leave it blank
(and OnUpdateError), the updates should happen automatically when you
ApplyUpdates. In that case, you would never have to use
TUpdateSQL.Apply at all. OnUpdateRecord is there so that you can
manipulate the process when the automatic default won't do, like when
trying to keep foreign keys in synch when adding and deleting
master/detail records.

Phil Cain

--

Phillip

see below.

I am using D5 w/update.

This is not my understanding... I believe that the tquery components (sql statemenst,
at least) are generated at design time, but I have never thought trough where the
parameters come from.. hmmm From the documentation it seems that they come from the
dataset in a different manner.

The "un-dataset-bound" UpdSQL component is called from the OnUpdateRecord event
handler for a dataset AND the event passes the name of the dataset and (presumably)
the field components that will then match the parameters...

I haven't had time to try this, but it is what the documentation implies.

(in both D4 and D5 this section is titled "Creating an OnUpdate event handler."

  see above ..

  se above again.. the manual is pretty clear on using the OnUpdateRecord event
handler. It is NOT clear on how to get the SQL code into the update component..

This is where I began this thread.. I was wondering if there was an easier way to do
this ... AND I suggest that a comma or semi-colon list in the Dataset's update
component property would help.

  I think this is done for you when you call it form the updaterecord event

--

Edward V. Malloy
BBL Leasing, Inc.

Ed,

The SQL statement is a TStrings object, not a TQuery. IOW, the SQL in
TUpdateSQL is just text. TUpdateSQL uses this when it creates the
TQuery.

This is not exactly true. I have no idea what the docs mean by
"un-dataset-bound" but the queries activated there are handled by
ApplyUpdates. OnUpdateRecord is called only if there is code in it. If
OnUpdateRecord and OnUpdateError are empty (not assigned), TUpdateSQL
defaults to an automatic handling mode. It's easy enough to
demonstrate this to yourself. Make a one table application with one
query and one TUpdateSQL. Make a change and do ApplyUpdates,
CommitUpdates. You'll get the change without OnUpdateRecord.

Normally, you do this at design time. If you want to do it at run
time, do this:
        MyTUpdateSQL.ModifySQL.Add('put SQL here');
There are also properties for InsertSQL and DeleteSQL.
No easier way and I don't know what you mean by " comma or semi-colon
list"

No. You can set the TQuery.UpdateObject or you can set the
TUpdateSQL.Dataset object, but not both. The VCL can become confused
at that.

Concerning your angst over the documentation, I can say only that I've
been there, done that and have about two years experience with this
construct. Unless I indicate that I am speculating, what I say comes
directly from my own experience and working code.

You should also know that since D3, cached updates has undergone only
trivial changes. The whole construct is deprecated by Borland and will
be replaced by TClientDataset in D6 in the Pro edition. TClientDataset
is a more mature rendering of cached updates and you should expect
that to be better than you have now.

Phil Cain
--

The documentation show how to enter code into the OnUpdateRecord event
handler.  It works!

 I know that Phillip, what I meant is that it is much easier to write
the code when the framework is generated for you, and this is only done
when the UpdateSQl is linked to a dataset..

  What I mean is a list delimited by commas or semicolons.. a pretty
standard way of entering multiple values, (e.g. creating a lookup
variable and linking to a table with a segmented primary ket).

  Of course you can... and then set it back.
  I used cached updates quite often, I have never had the need to update
several tables from a single query though ... it smacks of poor design.

  Thanks this is good to know..

ed

Just to clarify: Setting the UpdateSQL.Dataset at run time does not
mean that you can't use the automatic features of the UpdateSQL
property editor. If I have several UpdateSQLs for one query then, at
run time, I set the Query.UpdateObject property to the UpdateSQLs one
at a time. Then I use the design time features of the UpdateSQL to lay
in the default SQL. When I am finished with that, I blank out the
Query.UpdateObject property and let the code handle the connections
during run time.

Good luck.

Phil Cain
--

Thanks Philip,

that is what I originally asked: was there any way OTHER than the way
that you described,

Take care,

ed