SavePoint in SDAC
SavePoint in SDAC
Hello I would like to take a snapshot of a dataset.
The scenario is:
0) I open a TMSQuery
1) The user makes some changes to the dataset
2) The dataset is still not posted and the user makes more changes
3) The user decide not to post the changes made at (2) but would like to keep those made at (1)
How to achieve that?
Somehow I should use a SavePoint and restore it, how to perform this with SDAC?
The scenario is:
0) I open a TMSQuery
1) The user makes some changes to the dataset
2) The dataset is still not posted and the user makes more changes
3) The user decide not to post the changes made at (2) but would like to keep those made at (1)
How to achieve that?
Somehow I should use a SavePoint and restore it, how to perform this with SDAC?
Hello,
before posting here I also posted on Stack Overflow, I give you the link so it will explain my need even better: http://stackoverflow.com/questions/7810 ... -a-dataset
I hope you can reply soon.
before posting here I also posted on Stack Overflow, I give you the link so it will explain my need even better: http://stackoverflow.com/questions/7810 ... -a-dataset
I hope you can reply soon.
You can use savepoints that are provided by SQL Server. Using savepoints you can roll back portions of transactions. Here is an example:You can learn more information about savepoints at http://msdn.microsoft.com/en-us/library/ms188378.aspx
Code: Select all
MSQuery.SQL.Text := 'select * from emp';
MSQuery.Open;
MSQuery.Connection.StartTransaction;
MSQuery.Edit;
MSQuery.FieldByName('sal').AsFloat := 900; // this change will go to the server
MSQuery.Post;
MSQuery.Connection.ExecSQL('SAVE TRANSACTION SP1', []);
MSQuery.Edit;
MSQuery.FieldByName('comm').AsFloat := 500; // this change will not go to the server
MSQuery.Post;
MSQuery.Connection.ExecSQL('ROLLBACK TRANSACTION SP1', []);
MSQuery.Connection.Commit;
Ok, but could you please suggest me what to do in my case?
Is there a trick you can suggest?
Imagine this case:
How can I go to go back to SAVEPOINT?
Cna you suggest a trick?
(A trick can involve using another TMSQuery in parallel, a TVirtualTable... or any other idea)
Is there a trick you can suggest?
Imagine this case:
Code: Select all
MSQUery1.CachedUpdates := True;
MsQuery1.Open; (INITIALSTATE)
MSQuery1.Edit;
-- I do some changes (append lines, modify values in fields)
-- Here I would like to "save" (SAVEPOINT)
-- I do some more Changes
-- I would like to go back to SAVEPOINT, if I call MSQUery1.CancelUpdates I will go back to INITIALSTATE.
Cna you suggest a trick?
(A trick can involve using another TMSQuery in parallel, a TVirtualTable... or any other idea)
You can use the following code:
Code: Select all
procedure TMainForm.BitBtnClick(Sender: TObject);
var
oldUpdateRecordTypes: TUpdateRecordTypes;
begin
MSQuery.CachedUpdates := True;
MSQuery.Open;
//changes
//savepoint using two TVirtualTable
oldUpdateRecordTypes := MSQuery.UpdateRecordTypes;
MSQuery.UpdateRecordTypes := [rtDeleted];
VirtualTable1.Assign(MSQuery);
MSQuery.UpdateRecordTypes := [rtModified, rtInserted];
VirtualTable2.Assign(MSQuery);
MSQuery.UpdateRecordTypes := oldUpdateRecordTypes;
//more changes
//restoring savepoint
MSQuery.CancelUpdates;
CRBatchMove1.Mode := bmDelete;
CRBatchMove1.Source := VirtualTable1;
CRBatchMove1.Destination := MSQuery;
CRBatchMove1.Execute;
CRBatchMove1.Mode := bmAppendUpdate;
CRBatchMove1.Source := VirtualTable2;
CRBatchMove1.Destination := MSQuery;
CRBatchMove1.Execute;
end.
Hello thanks for the suggestion, I don't think I totally understand your example, anyway to optain what I need I modified it by trial and error until I found a solution that works for me.
But can you comment on this and compare with your solution?
To summarize comparing to your example I did:
1) remove CAncelUpdates
2) add the loop for deleting all records
3) remove the bmDelete part (that by the way seems useless after changes 1 and 2)
4) add trUnmodified on top
I ask you to comment so I can better understand.
But can you comment on this and compare with your solution?
Code: Select all
procedure TMainForm.BitBtnClick(Sender: TObject);
var
oldUpdateRecordTypes: TUpdateRecordTypes;
begin
MSQuery.CachedUpdates := True;
MSQuery.Open;
//changes
//savepoint using two TVirtualTable
oldUpdateRecordTypes := MSQuery.UpdateRecordTypes;
MSQuery.UpdateRecordTypes := [rtDeleted];
VirtualTable1.Assign(MSQuery);
// I had to add here UNMODIFIED !!!
MSQuery.UpdateRecordTypes := [rtModified, rtInserted, rtUnmodified];
VirtualTable2.Assign(MSQuery);
MSQuery.UpdateRecordTypes := oldUpdateRecordTypes;
//more changes
//restoring savepoint
// I commented this because if not i don't have a real savepoint
// because it will show the records that where in the dataset just after Open
// not just after SAving the save point
// MSQuery.CancelUpdates;
//Instead I wrote this that really clears all the data
while not MSQuery.Eof do
MSQuery.Delete;
// this is not usefule at all
// CRBatchMove1.Mode := bmDelete;
// CRBatchMove1.Source := VirtualTable1;
// CRBatchMove1.Destination := MSQuery;
// CRBatchMove1.Execute;
CRBatchMove1.Mode := bmAppendUpdate;
CRBatchMove1.Source := VirtualTable2;
CRBatchMove1.Destination := MSQuery;
CRBatchMove1.Execute;
end.
1) remove CAncelUpdates
2) add the loop for deleting all records
3) remove the bmDelete part (that by the way seems useless after changes 1 and 2)
4) add trUnmodified on top
I ask you to comment so I can better understand.
Your way is not effective, because in this case all records will be deleted on the server and inserted again. You can check it using SQL Server Profiler or by setting the TMSQuery.Debug property to True. That's why the performance of your approach is lower than of the one I wrote above. Here is our example again with more detailed explanation:Using this approach, SQL statements (DELETE, INSERT, UPDATE) will be executed only for records which were deleted, inserted, or updated.
Code: Select all
procedure TMainForm.BitBtnClick(Sender: TObject);
var
oldUpdateRecordTypes: TUpdateRecordTypes;
begin
MSQuery.CachedUpdates := True;
MSQuery.Open;
//changes
//savepoint using two TVirtualTable
oldUpdateRecordTypes := MSQuery.UpdateRecordTypes;
MSQuery.UpdateRecordTypes := [rtDeleted];
VirtualTable1.Assign(MSQuery); // VirtualTable1 contains only the records which were deleted
MSQuery.UpdateRecordTypes := [rtModified, rtInserted];
VirtualTable2.Assign(MSQuery); // VirtualTable1 contains only the records which were inserted or updated
MSQuery.UpdateRecordTypes := oldUpdateRecordTypes;
// here VirtualTable1 and VirtualTable2 contain all changes which were made in MSQuery, so both of them represent a savepoint
//more changes
//restoring savepoint
MSQuery.CancelUpdates; // all changes are undone here
CRBatchMove1.Mode := bmDelete;
CRBatchMove1.Source := VirtualTable1;
CRBatchMove1.Destination := MSQuery;
CRBatchMove1.Execute; // this will remove all records from MSQuery that were deleted before you created savepoint
CRBatchMove1.Mode := bmAppendUpdate;
CRBatchMove1.Source := VirtualTable2;
CRBatchMove1.Destination := MSQuery;
CRBatchMove1.Execute; // this will update or insert records from MSQuery that were updated or inserted before you created savepoint
end.
Thanks for the explanation, anyway with your Approach there is a problem that with my is not there.
Imagine in the table I have initially 2 records, i mean doing MSQuery.Open initially returns: (let's do the case of a simple query with name and age)
John, 34
Mary, 23
now if in DBGrid I Replace John with Johnny so MSQuery contains now:
Johnny, 34 -- I MODIFIED THIS!
Mary, 23
So now I take the savepoint:
At this moment VirtualTable1 contains NOTHING and VirtualTable2 contains:
Johnny, 34
Now by running your code to restore savepoint:
Now MSQUery contains the original values:
John, 34
Mary, 23
And now I use CRBatchMove for deleted records:
but this does nothing. TO WORK IT SHOULD DELETE John, 34 but it doesn't. -- THIS IS THE PROBLEM
Now MSQuery (UNFORTUNATELY) still has:
John, 34
Mary, 23
When i use CRBatchMove for updated records I got the record I modified but the final result is:
John, 34 -- THIS SHOULD NOT BE HERE!!! (savepoint should contain Johnny and Mary only)
Mary, 23
Johnny, 34
So my code is terrible in performance, but it is a workaround I found.
The problem is that the record I modified from John to Johnny is not considered a deleted record.
How your example should be modified to take care of this so that I can discard my workaround? Thanks.
Imagine in the table I have initially 2 records, i mean doing MSQuery.Open initially returns: (let's do the case of a simple query with name and age)
John, 34
Mary, 23
now if in DBGrid I Replace John with Johnny so MSQuery contains now:
Johnny, 34 -- I MODIFIED THIS!
Mary, 23
So now I take the savepoint:
Code: Select all
//savepoint using two TVirtualTable
oldUpdateRecordTypes := MSQuery.UpdateRecordTypes;
MSQuery.UpdateRecordTypes := [rtDeleted];
VirtualTable1.Assign(MSQuery); // VirtualTable1 contains only the records which were deleted
MSQuery.UpdateRecordTypes := [rtModified, rtInserted];
VirtualTable2.Assign(MSQuery); // VirtualTable1 contains only the records which were inserted or updated
MSQuery.UpdateRecordTypes := oldUpdateRecordTypes;
Johnny, 34
Now by running your code to restore savepoint:
Code: Select all
MSQuery.CancelUpdates; // all changes are undone here
Now MSQUery contains the original values:
John, 34
Mary, 23
And now I use CRBatchMove for deleted records:
Code: Select all
CRBatchMove1.Mode := bmDelete;
CRBatchMove1.Source := VirtualTable1;
CRBatchMove1.Destination := MSQuery;
CRBatchMove1.Execute
Now MSQuery (UNFORTUNATELY) still has:
John, 34
Mary, 23
When i use CRBatchMove for updated records I got the record I modified but the final result is:
John, 34 -- THIS SHOULD NOT BE HERE!!! (savepoint should contain Johnny and Mary only)
Mary, 23
Johnny, 34
So my code is terrible in performance, but it is a workaround I found.
The problem is that the record I modified from John to Johnny is not considered a deleted record.
How your example should be modified to take care of this so that I can discard my workaround? Thanks.
This problem is caused by the fact that your table doesn't have Primary Key. You should create your table in the following way:With this table you will not have such problem.
Code: Select all
CREATE TABLE TEST_SAVEPOINT(
ID INT PRIMARY KEY IDENTITY NOT NULL,
NAME VARCHAR(20),
AGE INT
)
Yes, thanks. Finally I got it all and now it works. The prolem was lack of PK in my test table.
I created this unit that exposes 2 functions. Somehow if you could integrate this in SDAC would be good and may be safer since I am not managing exceptions and all other things you could do. Anyway just for reference I post here so in case you can comment
THanks for the support.
I created this unit that exposes 2 functions. Somehow if you could integrate this in SDAC would be good and may be safer since I am not managing exceptions and all other things you could do. Anyway just for reference I post here so in case you can comment
THanks for the support.
Code: Select all
unit uSavePoint;
interface
uses
Classes, DBAccess, MemDS, VirtualTable, CRBatchMove;
type
TMSSavePoint = class(TComponent)
private
FMemDataSet: TMemDataSet;
FvtDeleted: TVirtualTable;
FvtUpdatedAppended: TVirtualTable;
procedure StoreCurrentData;
procedure RestoreSavedData;
public
constructor Create(aMemDataSet: TMemDataSet);
end;
function CreateSavePoint(aMemDataSet: TMemDataSet): TMSSavePoint;
procedure RestoreSavePoint(aSavePoint: TMSSavePoint);
implementation
function CreateSavePoint(aMemDataSet: TMemDataSet): TMSSavePoint;
var
MySavePoint: TMSSavePoint;
begin
MySavePoint := TMSSavePoint.Create(aMemDataSet);
MySavePoint.StoreCurrentData;
Result := MySavePoint;
end;
procedure RestoreSavePoint(aSavePoint: TMSSavePoint);
begin
aSavePoint.RestoreSavedData;
end;
{ TMSSavePoint }
constructor TMSSavePoint.Create(aMemDataSet: TMemDataSet);
begin
FMemDataSet := aMemDataSet;
FvtDeleted := TVirtualTable.Create(Self);
FvtUpdatedAppended := TVirtualTable.Create(Self);
end;
procedure TMSSavePoint.RestoreSavedData;
var
MyCRBatchMove: TCRBatchMove;
begin
FMemDataSet.CancelUpdates;
MyCRBatchMove := TCRBatchMove.Create(Self);
try
MyCRBatchMove.Mode := bmDelete;
MyCRBatchMove.Source := FvtDeleted;
MyCRBatchMove.Destination := FMemDataSet;
MyCRBatchMove.Execute;
MyCRBatchMove.Mode := bmAppendUpdate;
MyCRBatchMove.Source := FvtUpdatedAppended;
MyCRBatchMove.Destination := FMemDataSet;
MyCRBatchMove.Execute;
finally
MyCRBatchMove.Free;
end;
end;
procedure TMSSavePoint.StoreCurrentData;
var
oldUpdateRecordTypes: TUpdateRecordTypes;
begin
//savepoint using two TVirtualTable
oldUpdateRecordTypes := FMemDataSet.UpdateRecordTypes;
FMemDataSet.UpdateRecordTypes := [rtDeleted];
FvtDeleted.Assign(FMemDataSet);
FMemDataSet.UpdateRecordTypes := [rtModified, rtInserted];
FvtUpdatedAppended.Assign(FMemDataSet);
FMemDataSet.UpdateRecordTypes := oldUpdateRecordTypes;
end;
end.