I have a problem and a question. I am using Delphi 4.0, Windows NT 4.0, the
MSSQL native driver from the BDE to make my connection, and Microsoft SQL
Server 7.0 on the backend.

The Question: Cached updates are stored locally until I apply them, right?
When I apply the changes, is a SQL call made for each individual record or
are they "batched" to the SQL server? How exactly do cached updates reduce
network traffic between the client and the server?

The Problem: I had a TQuery component I was using to make updates on a
record-by-record basis as the records were changed. That was working fine. I
put that exact same SQL code into a TUpdateSQL component and now I get a
"Table is read only" error message when I attempt to apply the changes.
Clearly, the table is not read only because it works in the prior instance.
The SQL is the same in both cases. Does anyone know why I'm getting this
error message?

Thanks in advance for any help.

Ed,

All the changes you make while using cached updates are stored locally
in temporary files. Delphi keeps track of before and after field
values for all changed records and then discards all that when you say
CommitUpdates or CancelUpdates.

The update process starts when you say ApplyUpdates. Then cached
updates goes through the cache one record at a time and posts the
changes to the database - one record at a time. There are a number of
ways to do this. You can set it up so that it's automatic or you can
optionally write a handler in OnUpdateRecord. (Under some
circumstances, like a query that joins multiple tables, this is not an
option and must be done.)

Cached updates doesn't buy you a thing if you are querying one table
and posting updates at every record change. It's just unnecessary
overhead there.

You want to use cached updates whenever you want to change many
records and you don't want the database to know about it before all
the changes are made or you want the opportunity to cancel all the
changes easily.

Or you want to use cached updates when you have a record set that is
spread across many tables and it doesn't make sense to post part of
the set. An example is an order that has a header record and many
detail records. Cached updates allows you to enter the entire record
before actually posting any of it.

In a multi-user (or C/S) application, you also get the benefit of some
improved performance and better data management. The update process is
a true batch process because there's no human intervention required,
and so bunching up the updates eliminates the resource time taken by
mere human considerations, like answering the phone in mid
transaction. You get better management because others don't see the
work until it's ready and they are not blocked out of part of the
database while you're at lunch.

Your "Table is read only" message may be caused by the UpdateSQL and
the Query not being connected properly. You have two choices. If you
have just one UpdateSQL, then put its name in the Query.UpdateObject
property. If you have many UpdateSQLs for one Query, then leave the
query property blank and, in your code, set the UpdateSQL.Dataset
property to the name of the query.

Phil Cain
--

news:DZ4GOPZLIu9RzWR8akdTo+3rCo81@4ax.com...

Thank you for your explanation on what cached updates do and when they
should be used. It was very informative.

However, I'm still having the problem with the "table is read only" error
message. I only have one TQuery object and one TUpdateSQL object. If I use
the TQuery object to retrieve a result set, modify a record, then alter the
SQL property to send that update to the server, I have no problem. If I set
cached updates to "True", set the UpdateObject property to point to the
TUpdateSQL object, and try to use the TUpdateSQL object to send the changes
to the server, I get the read only error.

Do you have any other ideas on why this might be happening?

This is a new one on me. I've never heard of changing the SQL while
cached updates is True. I would not be surprised to learn that this
confuses the cache in some way.

Phil Cain
--

{This method sets the appropriate SQL code for the update object}

procedure TForm1.SetUpdateObject;
var
  s: String;
begin
  s := format('UPDATE Groups SET GrpName = "%s", GrpOwner = "%s",
GrpDeveloper = "%s", GrpMemCount = %d WHERE [Key] = %d',
    [DatMod2.q1.FieldByName('GrpName').AsString,
DatMod2.q1.FieldByName('GrpOwner').AsString,
     DatMod2.q1.FieldByName('GrpDeveloper').AsString,
DatMod2.q1.FieldByName('GrpMemCount').AsInteger,
     DatMod2.q1.FieldByName('Key').AsInteger]);
  DatMod2.UpdateSQL1.DeleteSQL.Add(format('DELETE FROM Groups WHERE [Key] =
%d;',[DatMod2.q1.FieldByName('Key').AsInteger]));
  DatMod2.UpdateSQL1.InsertSQL.Add(format('INSERT Groups (GrpName, GrpOwner,
GrpDeveloper, GrpMemCount) VALUES ("%s", "%s", "%s", %d);',
    [DatMod2.q1.FieldByName('GrpName').AsString,
DatMod2.q1.FieldByName('GrpOwner').AsString,
     DatMod2.q1.FieldByName('GrpDeveloper').AsString,
DatMod2.q1.FieldByName('GrpMemCount').AsInteger]));
  DatMod2.UpdateSQL1.ModifySQL.Add(s);
end;

{This method applies the updates}

procedure TForm1.Button5Click(Sender: TObject);
begin
  SetUpdateObject;
  DatMod2.db1.ApplyUpdates([DatMod2.q1]);
end;