--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 12:44 PM 1/11/2006, you wrote:
> >Hi,
> >
> >I'm working on a large application that does all of it's saving one
> >record at a time. Basically the code is like this.
> >
> > Dataset.Close;
> > Dataset.ParamByName('ACTIVITY_ID').AsInteger := ActivityId;
> > Dataset.Open;
> >
> > Dataset.Edit;
> > Dataset.FieldByName('WS_REPEAT_REASON').AsString :=

RepeatReason;

> > Dataset.Post;
> >
> >This may be called up to 12000 times in one "Save". Hence saving is
> >currently extremely slow. I am looking to speed this up and have
> >tried the following
> >
> > While not Dataset.eof do begin
> > { does not load activities which have been repeated }
> > Dataset.Edit;
> > Dataset.FieldByName('WS_REPEAT_REASON').AsString :=

RepeatReason;

> > Dataset.Post;
> > Dataset.Next;
> > break;
> > end;
> >
> >In this case "Dataset" is a TIB_Cursor containing all the records that
> >need to be modified. The trouble is this code doesn't seem to modify
> >the records in the db. Can anyone help please?
>
> Post doesn't change the state of the database - it only updates what
> your transaction can see. You have to commit the transaction to
> change the database state.
>
> This is an SQL database, dear boy! Why are you performing this
> update in this fashion? Is there some hidden reason you haven't

mentioned?

>
> All you need is an ib_cursor or ib_dsql with a parameterised update
> statement as its SQL:
>
> update aTable
> set WS_REPEAT_REASON = :aReason
> where activity_id = :activity_id
>
> In the BeforeExecute, assign values to the params of *this* statement.
>
> if not statement.Prepared then
> statement.Prepare;
> statement.Params[0].AsString := RepeatReason;
> statement.Params[1].AsInteger := <wherever you're getting it from>;
>
> Call Execute on the statement (not Open!!) and WHOOOOSH! it's done,
> all on the server. One little request instead of 12,000. Commit the
> transaction and take the rest of the afternoon off. :-)
>
> Helen
>

Hello Again,

I didn't do a very good job of explaining my problem. The code I
attached is actually some vastly simplified test code. Typically the
records would have about 12 fields being modified with about half of
them different on each record. So this is the reason why a simple
update query won't do the job.

So here is the cut down test code which still doesn't work. Request
live is set to True. Are there any other properties that might be
causing the problem.

with TCursorRestorer.Create(crHourGlass) do try
Dataset.Open;
Dataset.First;

Dataset.IB_Transaction.StartTransaction;
Dataset.IB_Transaction.Activate;

While not Dataset.eof do begin
{ does not load activities which have been repeated }
Dataset.Edit;
Dataset.FieldByName('WS_REPEAT_REASON').AsString :=
WSRepeatReason;
Dataset.Post;
Dataset.Next;
end;
finally
Dataset.IB_Transaction.Commit;
Free;
end;


Thanks for your help,
Thomas