Roll back cached updates without losing changes (Updated)
Posted: 12.10.2007, 17:35
Update: Actually this is a Zeos bug. ApplyUpdates shold NOT delete the cache buffer; that's the mission of CommitUpdates which should be used after a successful ApplyUpdates. So this patch actually implements what the ApplyUpdates/CommitUpdates combination should do. [End update]
The following two methods allow the user of a master-detail form to cancel the post and go on editing when a database error occurs during the post. See http://zeos.firmos.at/viewtopic.php?t=1475 for details.
This assumes that both Master and Detail have CachedUpdates set to True and that Master is in edit mode (either via Append, Insert or Edit).
To use them, instead of Master.ApplyUpdates and Detail.ApplyUpdates, write a fragment similar to the following:
Notes:
- Both Master and Detail should be attached to the same connection, Conn.
- Conn must have transaction isolation level of tiReadCommitted or tiSerializable.
- The DataSource property of Detail must not be set. Use MasterSource et al to link the datasets; otherwise the changes will be lost after an error. It's possible to clear DataSource before starting the transaction and set it again after the FreeUpdates calls, but not after rollback, or the changes will also be lost in case of error.
Now, in case of a database error (PK duplication, FK violation, NULL value for a NOT NULL field, etc.) everything will be more or less back to the initial state before the attempt to apply the changes. I say "more or less" because the Modified flag of Master will be lost as will the editing state and Modified flag of Detail; also, if Master.State was dsInsert now it will be dsEdit. Other than that, things should be very much like they were before trying to apply updates and the transaction can be retried after any user edits.
Here's the patch. There are two files affected: src\component\ZAbstractDataset.pas and src\dbc\ZDbcCachedResultSet.pas. Here are the changes for ZDbcCachedResultSet.pas:
After this line:
procedure PostUpdates;
add these lines:
procedure PostUpdatesNoFree;
procedure FreeUpdates;
and after this line:
procedure PostUpdates; virtual;
add these lines:
procedure PostUpdatesNoFree; virtual;
procedure FreeUpdates; virtual;
Now, after the definition of procedure TZAbstractCachedResultSet.PostUpdates add the following:
The changes to ZAbstractDataset.pas are:
After this line:
procedure ApplyUpdates;
add these lines:
procedure ApplyUpdatesNoFree;
procedure FreeUpdates;
Now add the functions themselves (place them after TZAbstractDataSet.ApplyUpdates):
Note, however, that I'm not sure of whether the Resync([]) call is properly placed. I'm afraid it should be in FreeUpdates instead. In the first place I'm not sure about its purpose; it's copied from the ApplyUpdates code. Any ideas?
Thanks in advance.
The following two methods allow the user of a master-detail form to cancel the post and go on editing when a database error occurs during the post. See http://zeos.firmos.at/viewtopic.php?t=1475 for details.
This assumes that both Master and Detail have CachedUpdates set to True and that Master is in edit mode (either via Append, Insert or Edit).
To use them, instead of Master.ApplyUpdates and Detail.ApplyUpdates, write a fragment similar to the following:
Code: Select all
Master.Post;
Conn.StartTransaction;
try
Master.ApplyUpdatesNoFree;
Detail.ApplyUpdatesNoFree;
except
Conn.Rollback;
Master.Edit;
// changes are still cached at this point
raise;
end;
Conn.Commit;
Master.FreeUpdates;
Detail.FreeUpdates;
- Both Master and Detail should be attached to the same connection, Conn.
- Conn must have transaction isolation level of tiReadCommitted or tiSerializable.
- The DataSource property of Detail must not be set. Use MasterSource et al to link the datasets; otherwise the changes will be lost after an error. It's possible to clear DataSource before starting the transaction and set it again after the FreeUpdates calls, but not after rollback, or the changes will also be lost in case of error.
Now, in case of a database error (PK duplication, FK violation, NULL value for a NOT NULL field, etc.) everything will be more or less back to the initial state before the attempt to apply the changes. I say "more or less" because the Modified flag of Master will be lost as will the editing state and Modified flag of Detail; also, if Master.State was dsInsert now it will be dsEdit. Other than that, things should be very much like they were before trying to apply updates and the transaction can be retried after any user edits.
Here's the patch. There are two files affected: src\component\ZAbstractDataset.pas and src\dbc\ZDbcCachedResultSet.pas. Here are the changes for ZDbcCachedResultSet.pas:
After this line:
procedure PostUpdates;
add these lines:
procedure PostUpdatesNoFree;
procedure FreeUpdates;
and after this line:
procedure PostUpdates; virtual;
add these lines:
procedure PostUpdatesNoFree; virtual;
procedure FreeUpdates; virtual;
Now, after the definition of procedure TZAbstractCachedResultSet.PostUpdates add the following:
Code: Select all
{**
Posts all saved updates to the server without freeing them.
}
procedure TZAbstractCachedResultSet.PostUpdatesNoFree;
var
i: Integer;
C: Integer;
begin
CheckClosed;
C := FInitialRowsList.Count;
if C > 0 then
begin
i := 0;
while i < C do
begin
OldRowAccessor.RowBuffer := PZRowBuffer(FInitialRowsList[i]);
NewRowAccessor.RowBuffer := PZRowBuffer(FCurrentRowsList[i]);
i := i + 1;
{ Updates default field values. }
if NewRowAccessor.RowBuffer.UpdateType = utInserted then
CalculateRowDefaults(NewRowAccessor);
{ Posts row updates. }
PostRowUpdates(OldRowAccessor, NewRowAccessor);
end;
end;
end;
{**
Frees the updates and marks records as unmodified. Complements
PostUpdatesNoFree.
}
procedure TZAbstractCachedResultSet.FreeUpdates;
begin
while FInitialRowsList.Count > 0 do
begin
OldRowAccessor.RowBuffer := PZRowBuffer(FInitialRowsList[0]);
NewRowAccessor.RowBuffer := PZRowBuffer(FCurrentRowsList[0]);
if NewRowAccessor.RowBuffer.UpdateType <> utDeleted then
begin
NewRowAccessor.RowBuffer.UpdateType := utUnmodified;
if (FSelectedRow <> nil)
and (FSelectedRow.Index = NewRowAccessor.RowBuffer.Index) then
FSelectedRow.UpdateType := utUnmodified;
end;
{ Remove cached rows. }
OldRowAccessor.Dispose;
FInitialRowsList.Delete(0);
FCurrentRowsList.Delete(0);
end;
end;
After this line:
procedure ApplyUpdates;
add these lines:
procedure ApplyUpdatesNoFree;
procedure FreeUpdates;
Now add the functions themselves (place them after TZAbstractDataSet.ApplyUpdates):
Code: Select all
procedure TZAbstractDataset.ApplyUpdatesNoFree;
begin
if not Active then Exit;
Connection.ShowSQLHourGlass;
try
if State in [dsEdit, dsInsert] then Post;
DoBeforeApplyUpdates; {bangfauzan addition}
if CachedResultSet <> nil then
CachedResultSet.PostUpdatesNoFree;
if not (State in [dsInactive]) then
Resync([]);
DoAfterApplyUpdates; {bangfauzan addition}
finally
Connection.HideSqlHourGlass;
end;
end;
procedure TZAbstractDataset.FreeUpdates;
begin
CheckBrowseMode;
if CachedResultSet <> nil then
CachedResultSet.FreeUpdates;
end;
Thanks in advance.