Board index » delphi » Cached updates & updateSQL component

Cached updates & updateSQL component

I have encountered the following problem with cached updates for which I am
seeking assistance.

Environment Note: I am running Delphi 3 C/S under Windows 95 with a db2
backend under AIX.

Problem: My cached update seems to work on one record but not on the entire
result set.

The SQL command in the Query component reads:

select * from MYTABLE1 A, MYTABLE2 B
where A.CUSTOMER = :CUSTOMER                       // :CUSTOMER is a
parameter
and A.INTID = B.INTID
order by A.ORDER, B.NAMES

When the SQL command in the (modify) UpdateSQL component reads:

update MYTABLE1
set ORDER = ORDER + 1
where CUSTOMER = :CUSTOMER                    // :CUSTOMER is a parameter
and ORDER = 5                                               // Only record
with ORDER = 5
                                                 is being affected

everything works properly.

If, however, I change the last line of the above SQL command to read:

and ORDER > 5            (in order to modify all records in the result set
with an                                     ORDER value above 5)

I get the error: raised exception class EDatabaseError with message 'update
failed'. Process stopped.

I execute the updateSQL code by the following (button) onclick event
handler:

Query1.Params[0].AsString := CustomerNumber;
UpdateSQL1.SetParams(ukModify);
UpdateSQL1.Apply(ukModify);
Query1.ApplyUpdates;
Query1.Close;
Query1.Open;

Any and all assistance with this problem is greatly appreciated.

Jim Bellis

 

Re:Cached updates & updateSQL component


Quote
Jim Bellis wrote:

> I have encountered the following problem with cached updates for which I am
> seeking assistance.

> Environment Note: I am running Delphi 3 C/S under Windows 95 with a db2
> backend under AIX.

> Problem: My cached update seems to work on one record but not on the entire
> result set.

> The SQL command in the Query component reads:

> select * from MYTABLE1 A, MYTABLE2 B
> where A.CUSTOMER = :CUSTOMER                       // :CUSTOMER is a
> parameter
> and A.INTID = B.INTID
> order by A.ORDER, B.NAMES

> When the SQL command in the (modify) UpdateSQL component reads:

> update MYTABLE1
> set ORDER = ORDER + 1
> where CUSTOMER = :CUSTOMER                    // :CUSTOMER is a parameter
> and ORDER = 5                                               // Only record
> with ORDER = 5
>                                                  is being affected

> everything works properly.

> If, however, I change the last line of the above SQL command to read:

> and ORDER > 5            (in order to modify all records in the result set
> with an                                     ORDER value above 5)

> I get the error: raised exception class EDatabaseError with message 'update
> failed'. Process stopped.

> I execute the updateSQL code by the following (button) onclick event
> handler:

> Query1.Params[0].AsString := CustomerNumber;
> UpdateSQL1.SetParams(ukModify);
> UpdateSQL1.Apply(ukModify);
> Query1.ApplyUpdates;
> Query1.Close;
> Query1.Open;

> Any and all assistance with this problem is greatly appreciated.

> Jim Bellis

I had a similiar problem. I looked down at TUpdateSQL source code and
noticed a curious line (I dont remember exactly where):

     if AffectedRows <> 1 then RaiseSomeException...

I don't get why Borland does this. Anyway, what I did was to subclass
TUpdateSQL with a class of my own and override the method with that line
(sadly declared static) and the virtual method which calls it (Probably
ExecSQL). Using my own TMultyUpdateSQL the update worked fine.

Re:Cached updates & updateSQL component


Hi Dan

What Borland did with the Cached Updates/TUpdateSQL makes total,
absolute sense.

TUpdateSQL is used when you want to update a single row of a read only
dataset. The appropriate sql is executed for each line of the dataset
depending on that rows state. Updating one row creates sql that affects
one row on the backend. It does not make sense, that by changing a
single row of a dataset that it modifies more than one row on the
backend.

If you want to update more than one record with a single SQL statement,
you would use a TQuery or TStoredProc.

If you could give me an example of where it makes perfect sense to
update more than one record on the backend by changing a single row of a
dataset, I'd like to hear it, because I cannot think of any...

Anyway, my two cents...
Regards
Clint.

Quote

>      if AffectedRows <> 1 then RaiseSomeException...

> I don't get why Borland does this. Anyway, what I did was to subclass
> TUpdateSQL with a class of my own and override the method with that
> line
> (sadly declared static) and the virtual method which calls it
> (Probably
> ExecSQL). Using my own TMultyUpdateSQL the update worked fine.

Re:Cached updates & updateSQL component


Hi !

Jim Bellis <jbel...@newsbank.com> wrote in
<01bd0689$45178f20$2c507...@jimb.newsbank.com>...

Quote
> I have encountered the following problem with cached updates for which I
am
> seeking assistance.

I guess cached updates (the TUpdateSQL class) is not able to update more
than one row !

Take a look at this VCL code (dbtables.pas) :

procedure TUpdateSQL.ExecSQL(UpdateKind: TUpdateKind);
begin
  with Query[UpdateKind] do
  begin
    Prepare;
    ExecSQL;
    if RowsAffected <> 1 then DatabaseError(SUpdateFailed);
  end;
end;

Hope this helps.

Sebastien FLAESCH (incog...@incotec.fr)

Re:Cached updates & updateSQL component


On Sun, 14 Dec 1997 20:35:43 +1000, Clint Good <cli...@vipnet.com.au>
wrote:

Quote

>If you could give me an example of where it makes perfect sense to
>update more than one record on the backend by changing a single row of a
>dataset, I'd like to hear it, because I cannot think of any...

What I wanted to do is to fill a TDBGrid with the result set of a
"join" query, where I select fields from two tables. I wanted to keep
the liveness of the query - to let the user change the data from
within the grid.
The only way I found to do this included:
* setting CachedUpdates to True.
* setting a UpdateSQL for the query.
* in that UpdateSQL's UpdateSQL property to update the relvant fields,
using two SQL update...set commands inside a single PL/SQL block.

this made the changes all right, but raise the aforementioned
exception.

THERE MUST BE A GENTLER WAY!

Re:Cached updates & updateSQL component


I couldn't help but notice your message, having "been there, tried that" and
wound up solving the problem another way.  I agree that the only way to
update more than one row with an update object will be to write your own but
that can be a lot of work and introduces new problems.

Take another look at your join (and your application) and ask yourself if
you really need to update both tables under the join.  As others have said,
TUpdateSQL is great for updating one table under a Read-Only dataset but not
more than one.

What is in the tables?

Regards,
Jim

Quote
Dan Kenigsberg wrote in message <3494f8b9.11476...@forums.borland.com>...
>On Sun, 14 Dec 1997 20:35:43 +1000, Clint Good <cli...@vipnet.com.au>
>wrote:

>>If you could give me an example of where it makes perfect sense to
>>update more than one record on the backend by changing a single row of a
>>dataset, I'd like to hear it, because I cannot think of any...

>What I wanted to do is to fill a TDBGrid with the result set of a
>"join" query, where I select fields from two tables. I wanted to keep
>the liveness of the query - to let the user change the data from
>within the grid.
>The only way I found to do this included:
>* setting CachedUpdates to True.
>* setting a UpdateSQL for the query.
>* in that UpdateSQL's UpdateSQL property to update the relvant fields,
>using two SQL update...set commands inside a single PL/SQL block.

>this made the changes all right, but raise the aforementioned
>exception.

>THERE MUST BE A GENTLER WAY!

Re:Cached updates & updateSQL component


Quote
Jim Elden wrote:

> I couldn't help but notice your message, having "been there, tried that" and
> wound up solving the problem another way.  I agree that the only way to
> update more than one row with an update object will be to write your own but
> that can be a lot of work and introduces new problems.

> Take another look at your join (and your application) and ask yourself if
> you really need to update both tables under the join.  As others have said,
> TUpdateSQL is great for updating one table under a Read-Only dataset but not
> more than one.

> What is in the tables?

> Regards,
> Jim

Well, what I've got is a single table of objects (the columns are
object_name and some other properties, and a group ID)
Every group has exactly 5 objects.
what I wanted to do is to select the 5 names and the group ID in a
single row, show them on a DBGrid, and let the user play with the names.

I would be grateful if you describe what were the problems you got wound
by. My own solution seemed fishy to me. I'd be glad to dump it for
another's.

Other Threads