Board index » delphi » Cached Updates - Table is Read-Only Error

Cached Updates - Table is Read-Only Error

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.

 

Re:Cached Updates - Table is Read-Only Error


Quote
"Ed McSweeney" <emcswee...@mayo.edu> wrote:
>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?

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
--

Re:Cached Updates - Table is Read-Only Error


Quote
Philip Cain <philc...@orelle.com> wrote in message

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

Quote
> 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.

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?

Re:Cached Updates - Table is Read-Only Error


Quote
"Ed McSweeney" <epi...@rconnect.com> wrote:

>Philip Cain <philc...@orelle.com> wrote in message
>news:EO8HOEWF9ZTclfBxGoWRR1FBRQlT@4ax.com...

>> But here are a couple of thoughts that occurred to me from your
>> paragraph above. When you don't use cached updates but reset the
>> Query.SQL instead, is cached updates left at True? Also, when you

>I'll try to post the code on Monday. It's on my computer at work. But to
>answer your question above, yes, cached updates is left at True. If I don't
>turn cached updates on for the TQuery component, I cannot update the
>dataset.

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
--

Re:Cached Updates - Table is Read-Only Error


Re:Cached Updates - Table is Read-Only Error


{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;

Other Threads