Devart Forums

Posted: **Thu 20 Oct 2011 14:01**

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?

Posted: **Mon 24 Oct 2011 07:57**

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.

Posted: **Mon 24 Oct 2011 14:11**

You can use savepoints that are provided by SQL Server. Using savepoints you can roll back portions of transactions. Here is an example:

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;

You can learn more information about savepoints at http://msdn.microsoft.com/en-us/library/ms188378.aspx

Posted: **Mon 24 Oct 2011 14:57**

Thanks for the reply. Since I need to implement this in several places I would start only with the most important, in the mean time do you plan to add a SavePoint property as in AnyDay (as discussed in Stack Overflow)?

Posted: **Tue 25 Oct 2011 12:22**

We will investigate the possibility of adding such functionality to SDAC.

Posted: **Wed 26 Oct 2011 12:58**

Ok, but could you please suggest me what to do in my case?

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.

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)

Posted: **Thu 27 Oct 2011 09:30**

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.

Posted: **Thu 27 Oct 2011 16:23**

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?

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.

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.

Posted: **Fri 28 Oct 2011 11:31**

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:

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.

Using this approach, SQL statements (DELETE, INSERT, UPDATE) will be executed only for records which were deleted, inserted, or updated.

Posted: **Fri 28 Oct 2011 12:15**

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:

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;

At this moment VirtualTable1 contains NOTHING and VirtualTable2 contains:

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

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.

Posted: **Fri 28 Oct 2011 13:04**

This problem is caused by the fact that your table doesn't have Primary Key. You should create your table in the following way:

Code: Select all

CREATE TABLE TEST_SAVEPOINT(
ID INT PRIMARY KEY IDENTITY NOT NULL,
NAME VARCHAR(20),
AGE INT
)

With this table you will not have such problem.

Posted: **Fri 28 Oct 2011 15:07**

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.

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.

Posted: **Mon 31 Oct 2011 12:09**

We will investigate this question.

Devart Forums

SavePoint in SDAC

SavePoint in SDAC