Board index » delphi » Cached updates, altering dataset values

Cached updates, altering dataset values

I am using a TQuery against an sql server (both Interbase and Sybase)
with CachedUpdates set to True. The data are displayed in a TDBGrid.

In the query's OnUpdateRecord event handler I execute (via a second
TQuery object) a stored procedure on the server. This sp inserts a new
record and returns the value of the IDENTITY column (i.e., an autonumber
column) for the new record.

Can anybody tell me how I can record this server-generated value into
the original dataset (so that it is displayed in the TDBGrid).

TIA,

Frits van Evert
Wageningen, The Netherlands

 

Re:Cached updates, altering dataset values


Quote
fkve <fvev...@soils.umn.edu> wrote:
>I am using a TQuery against an sql server (both Interbase and Sybase)
>with CachedUpdates set to True. The data are displayed in a TDBGrid.

>In the query's OnUpdateRecord event handler I execute (via a second
>TQuery object) a stored procedure on the server. This sp inserts a new
>record and returns the value of the IDENTITY column (i.e., an autonumber
>column) for the new record.

>Can anybody tell me how I can record this server-generated value into
>the original dataset (so that it is displayed in the TDBGrid).

Frits,

I believe it will work if you do the following:

Suppose that the query behind the grid is called GridQuery and the
stored procedure is called spIdentity. Then, in OnUpdateRecord, you
need something like this:

        GridQuery.FieldByName('identityfield').NewValue :=
                spIdentity.ParamByName('identityvalue').Value;
        {now post the change to GridQuery or use TUpdateSQL}
        UpdateAction := uaApplied;

Using NewValue before the actual update will allow the value to carry
through to the dataset and should display on your grid.

Good luck.

Phil Cain

--

Re:Cached updates, altering dataset values


Phil:

Thanks, it works - partially. The insert does give me a new record in the
database table, but I don't see the new identity value in the TDBGrid. In
fact, I don't even see it in the dataset; that is,
GridQuery.fieldByName('identityvalue') is null. Funny thing is that when I
*cancel* the insertion of yet another row, the correct identity value for the
previous row shows up. It is as if the cancel action does some synchronizing
and allows the value in the dataset to become visible. I hate to shout 'bug'
but TQuery does seem to display some strange behaviour here. What do you
think?

Regards,

Frits

Quote
Philip Cain wrote:
> fkve <fvev...@soils.umn.edu> wrote:

> >I am using a TQuery against an sql server (both Interbase and Sybase)
> >with CachedUpdates set to True. The data are displayed in a TDBGrid.

> >In the query's OnUpdateRecord event handler I execute (via a second
> >TQuery object) a stored procedure on the server. This sp inserts a new
> >record and returns the value of the IDENTITY column (i.e., an autonumber
> >column) for the new record.

> >Can anybody tell me how I can record this server-generated value into
> >the original dataset (so that it is displayed in the TDBGrid).

> Frits,

> I believe it will work if you do the following:

> Suppose that the query behind the grid is called GridQuery and the
> stored procedure is called spIdentity. Then, in OnUpdateRecord, you
> need something like this:

>         GridQuery.FieldByName('identityfield').NewValue :=
>                 spIdentity.ParamByName('identityvalue').Value;
>         {now post the change to GridQuery or use TUpdateSQL}
>         UpdateAction := uaApplied;

> Using NewValue before the actual update will allow the value to carry
> through to the dataset and should display on your grid.

> Good luck.

> Phil Cain

> --

Re:Cached updates, altering dataset values


Hi Philip!

On Mon, 07 Jun 1999 14:40:05 GMT, philc...@orelle.com (Philip Cain)
wrote:

Quote
>Suppose that the query behind the grid is called GridQuery and the
>stored procedure is called spIdentity. Then, in OnUpdateRecord, you
>need something like this:

>    GridQuery.FieldByName('identityfield').NewValue :=
>            spIdentity.ParamByName('identityvalue').Value;
>    {now post the change to GridQuery or use TUpdateSQL}
>    UpdateAction := uaApplied;

>Using NewValue before the actual update will allow the value to carry
>through to the dataset and should display on your grid.

Sorry for interrupting but I think you missed something here.

The point is that identity key is generated in the post, it is not
that he first knows what identity key the new record will receive, but
first he inserts the record and than he knows the identity key and
returns that key from the stored procedure.

Your solution can work but he have to make his own way of getting the
identity keys before iserting the records with the stored procedure.

tomi.

Re:Cached updates, altering dataset values


Quote
fkve <fvev...@soils.umn.edu> wrote:
>GridQuery.fieldByName('identityvalue') is null. Funny thing is that when I
>*cancel* the insertion of yet another row, the correct identity value for the
>previous row shows up. It is as if the cancel action does some synchronizing
>and allows the value in the dataset to become visible.

I have not seen this. But then, I've not seen everything yet <g>.

If you set NewValue in the OnUpdateRecord event and nowhere else, then
the new identity value should appear after you invoke ApplyUpdates and
CommitUpdates. The new value won't appear before then.

This has been my experience and the components don't feel buggy to me.
That is, so far, their behavior has been quite predictable.

If you post your OnUpdateRecord code and the bit of code where
ApplyUpdates/CommitUpdates takes place, I'd be happy to comment.

Phil Cain
--

Re:Cached updates, altering dataset values


Quote
sdt-sus...@zg.tel.hr (Tomislav Karda?) wrote:
>The point is that identity key is generated in the post, it is not
>that he first knows what identity key the new record will receive, but
>first he inserts the record and than he knows the identity key and
>returns that key from the stored procedure.

>Your solution can work but he have to make his own way of getting the
>identity keys before iserting the records with the stored procedure.

Since we don't have his code to look at, it's difficult to be accurate
about how it ought to look. But wether he gets the id first or gets it
as a result of inserting the record, the technique for getting it to
display is the same. That is, in OnUpdateRecord, he must assign the
new id value to the NewValue property before the end of
OnUpdateRecord.

Phil Cain
--

Re:Cached updates, altering dataset values


Hi Philip!

On Wed, 09 Jun 1999 02:05:36 GMT, philc...@orelle.com (Philip Cain)
wrote:

Quote
>>Your solution can work but he have to make his own way of getting the
>>identity keys before iserting the records with the stored procedure.

I apologise, I made mistake here. He can get identity key after the
actual insert into the database.

Quote
>Since we don't have his code to look at, it's difficult to be accurate
>about how it ought to look. But wether he gets the id first or gets it
>as a result of inserting the record, the technique for getting it to
>display is the same. That is, in OnUpdateRecord, he must assign the
>new id value to the NewValue property before the end of
>OnUpdateRecord.

I didn't know that NewValue can be modified in that way. I made an
example to test it and it works, thanks for new horizons ...

tomi.

Re:Cached updates, altering dataset values


Phil,

With your help I have been able to figure it out now. I call ApplyUpdates for the
individual datasets in my database and put all those calls inside a pair
database.StartTransaction / database.commit statements. What I DID NOT do, is call
dataset.commitUpdates after database.commit - and that's the statement that clears
the update cache and -presumably- makes the new values visible. Presumably the
dataset.cancel statement also clears the cache and using that statement negates
the error of not calling dataset.commitUpdates.

Thanks for your help,

Frits

Quote
Philip Cain wrote:
> fkve <fvev...@soils.umn.edu> wrote:

> >GridQuery.fieldByName('identityvalue') is null. Funny thing is that when I
> >*cancel* the insertion of yet another row, the correct identity value for the
> >previous row shows up. It is as if the cancel action does some synchronizing
> >and allows the value in the dataset to become visible.

> I have not seen this. But then, I've not seen everything yet <g>.

> If you set NewValue in the OnUpdateRecord event and nowhere else, then
> the new identity value should appear after you invoke ApplyUpdates and
> CommitUpdates. The new value won't appear before then.

> This has been my experience and the components don't feel buggy to me.
> That is, so far, their behavior has been quite predictable.

> If you post your OnUpdateRecord code and the bit of code where
> ApplyUpdates/CommitUpdates takes place, I'd be happy to comment.

> Phil Cain
> --

Re:Cached updates, altering dataset values


Quote
fkve <fvev...@soils.umn.edu> wrote:
> Presumably the
>dataset.cancel statement also clears the cache and using that statement negates
>the error of not calling dataset.commitUpdates.

More precisely, TQuery.CancelUpdates clears the cache and restores the
dataset to the place after the last successful update.
TQuery.RevertRecord does the same but for one record only.

Glad to hear you're making progress.

Phil Cain

--

Re:Cached updates, altering dataset values


hi. every body.
i have a question????
i have a similar problem, i'm using TTables y master detail. if my Master
Table is filtered, when i post, the master/detail lost, please could anybody
help me??.

My code is:

procedure TMasterDataModule.tblMasterTableBeforePost(
  DataSet: TDataSet);
var
   vQuery : TQuery;
begin
   gInserting := (tblMovimientoBanco.State = dsInsert);

  datMain.Database.StartTransaction;

  tblDetailTable.DisableControls;
end;

procedure TMasterDataModule.tblMasterTableAfterPost(
  DataSet: TDataSet);
begin

  if gInserting then
  begin
    gId := GetIdentity( datPrincipal.Contab);  // Function (SELECT
@@Identity) = SQL SERVER

    if tblDetailTable.UpdatesPending  then
      tblDetailTable.UpdateRecordTypes := [rtInserted, rtModified];

    tblDetailTable.First;
    while not (tblDetailTable.Eof) do
    begin
      tblDetailTable.Edit;
      tblDetailTableId.AsInteger := gId;
      tblDetailTable.Post;
    end;
    tblDetailTable.UpdateRecordTypes := [rtInserted, rtModified,
rtUnmodified, rtDeleted];
    tblDetailTable.ApplyUpdates ;
    datMain.Database.Commit;
end;

Im using SQL Server 7, DELPHI 4. and Nt 4.00

Thanks a lot.

Re:Cached updates, altering dataset values


Carlos,

I do not manage cached updates as you do and so I cannot say that your
method works or not. You have cached update commands mixed in with
methods for record handling and I never do that.

BeforePost and AfterPost are events for handling a single record.
Cached updates is a feature for handling multiple records. In
AfterPost (a method for one record) you are saying ApplyUpdates (a
method for many records). I don't think this works well.

With cached updates, I have noticed that I almost never have to use
record level events like BeforePost, AfterPost, BeforeInsert, and
AfterInsert. These events are for handling things when the
Dataset.State changes. Cached updates does a lot of this state change
business automatically.

When I update in cached updates, I make a separate procedure in this
form:

procedure UpdateTheDatabase:
begin
        MyDatabase.StartTransaction;
        try
                MyQuery.ApplyUpdates;
                MyDatabase.Commit:
                MyQuery.CommitUpdates;
        except  
                MyDatabase.RollBack;
        end;
end;

I call this procedure when I'm ready to update. I don't do anything in
BeforePost, AfterPost, etc.

With master/detail, you must update every time there is a change in
the master. That way, cached updates can handle one master record and
many detail records at once.

I never use the built-in master/detail features of Delphi when I use
cached updates. I always code master and detail separately. That way,
I can decide the order of events. If I am adding one master and many
details, I can update the master first and then the details. If I am
deleting, I can delete the details first and then the master. Delphi
is not always smart enough to do this automatically.

So far, I have never had to use the UpdateRecordTypes property to
filter records in the cache. Delphi documents say that this can be
used in OnUpdateError, but I haven't had a need yet. I recommend you
try to do your updates without it.

A final note: be careful how you use EnableControls and
DisableControls. Delphi wants these used as matched sets and counts
them when you use them. If you say DisableControls twice and
EnableControls only once, then the controls are still disabled.

Good luck.

Phil Cain

Quote
"carlos perez" <capa1...@hotmail.com> wrote:
>hi. every body.
>i have a question????
>i have a similar problem, i'm using TTables y master detail. if my Master
>Table is filtered, when i post, the master/detail lost, please could anybody
>help me??.

>My code is:

>procedure TMasterDataModule.tblMasterTableBeforePost(
>  DataSet: TDataSet);
>var
>   vQuery : TQuery;
>begin
>   gInserting := (tblMovimientoBanco.State = dsInsert);

>  datMain.Database.StartTransaction;

>  tblDetailTable.DisableControls;
>end;

>procedure TMasterDataModule.tblMasterTableAfterPost(
>  DataSet: TDataSet);
>begin

>  if gInserting then
>  begin
>    gId := GetIdentity( datPrincipal.Contab);  // Function (SELECT
>@@Identity) = SQL SERVER

>    if tblDetailTable.UpdatesPending  then
>      tblDetailTable.UpdateRecordTypes := [rtInserted, rtModified];

>    tblDetailTable.First;
>    while not (tblDetailTable.Eof) do
>    begin
>      tblDetailTable.Edit;
>      tblDetailTableId.AsInteger := gId;
>      tblDetailTable.Post;
>    end;
>    tblDetailTable.UpdateRecordTypes := [rtInserted, rtModified,
>rtUnmodified, rtDeleted];
>    tblDetailTable.ApplyUpdates ;
>    datMain.Database.Commit;
>end;

>Im using SQL Server 7, DELPHI 4. and Nt 4.00

>Thanks a lot.

--

Other Threads