Transaction control over Multple Server Side TDAMemDataTables

Data Abstract
1

DA Gurus,

I’ve a developed a Server side copy feature, which on the fly creates multiple TDAMemDataTables server side and iterates through the Schema.RelationShips to determine the relationships to copy. Within this iteration, the feature recursively calls itself until the Master and all Detail/Detail/Detail/ etc are copied. Because I require the OnBeforeProcess change events to fire for all this coping, I chose to use the TDAMemDataTables and a TDALocalDataAdapter on the server. vs IDADataSets.

Now for the question, I want the whole effort to roll back if any record copy fails for any of the table copies. How can I wrap a transaction around this entire WebService call. I know how to accomplish using IDADataset, but then I would not invoke the onBeforeProcessChange events.

So, what I am attempting to accomplish is something like:

function TSvcWDUtil.wsCopyEntity(myParams:MyArray); begin lLDA := TDALocalDataAdapter.Create(self); try self.Connection.BeginTransaction; try fxRoutineToCopyEntities(myParams); self.Connection.UpdateTransaction; except self.Connection.RollbackTransaction; end; finally lLDA.Free; end; end;

Please advise.

Regards,
Monte Carver

2

if I understand correctly, you have applied all changes in memory and later apply them in database?

if yes, you can reach required behavior with TDataAbstractService.ReturnUpdateFailureDelta = False and TDABusinessProcessor.RaiseExceptionAtError = True

4

you can call table.CancelUpdates;
of course, you may need to do some initial changes with table.LogChanges := False;

5

I guess I don’t understand how using CancelUpdate or LogChanges helps. Is there not some way to wrap a transaction around this entire process ?

6

you can perform some updates in one transactions with TDataAbstractService.ReturnUpdateFailureDelta := False. so after any error, transaction won’t be committed.

but if I correctly understood, you want something more, like

  1. server method was called by client
  2. data were prepared in memtables on server-side
  3. UpdateData via LDA
  4. if some problems on 3rd step is occured, you want to rollback all changes that were made in 2nd step .

is it correct?

7

Yes… with some clarification

  1. As specified (eg server method was called by client)
    2a. As specified (eg data were prepared in memtables on server-side)
    2b. Until no data to copy
  2. UpdateData via memTables.ApplyUpdates, Then do 2 again
  3. As specified.

Note: I am open to Updating data via other means. (ie. the LDA) if it offers transaction control.

8

I can recommend to handle transaction manually as you have specified in your first post.
for this, you can implement OnUpdateDataBeginTransaction / OnUpdateDataCommitTransaction / OnUpdateDataRollBackTransaction and return false for aUseDefaultTransactionLogic when this method is executed.

9

How would you recommend watching for the given webservice method. I have tried simply setting a boolean flag when the Webservice method is called then watching it for in the recommended Transaction events, but the setting is not respected.

Thanks you for the support.
Monte Carver

10

you can assign these events like

function TSvcWDUtil.wsCopyEntity(myParams:MyArray);
begin
  OnUpdateDataBeginTransaction := myevent;
  OnUpdateDataCommitTransaction := myevent;
  OnUpdateDataRollBackTransaction := myevent;
  Connection.StartTransaction;
  try
    fxRoutineToCopyEntities(myParams);
    Connection.UpdateTransaction;
  except
    Connection.RollbackTransaction;
  end;
end;
11

I have implemented what you suggest, and records still get posted on failure. So I will provide more summary detail on my approach. Of course the actual code is more complicated than provided, but it should clear up any misconceptions on my approach. I believe that what is occurring, is that the data is posted under a different instance of SvcWDUtil, and is therefore not respecting the connection transaction. I just don’t know how to work around this. The SvcWDUtil, is instantiated as
fClassFactory := TROClassFactory.Create(‘SvcWDUtil’, {$IFDEF FPC}@{$ENDIF}Create_SvcWDUtil, TSvcWDUtil_Invoker);

Please advise.
Monte Carver

function TSvcWDUtil.wsCopyEntity(myParams:MyArray);
begin
OnUpdateDataBeginTransaction := DataAbstractServiceUpdateDataBeginTransaction;
OnUpdateDataCommitTransaction := DataAbstractServiceUpdateDataCommitTransaction;
OnUpdateDataRollBackTransaction := DataAbstractServiceUpdateDataRollBackTransaction;
// NOTE: THESE EVENTS SIMPLY HAVE : aUseDefaultTransactionLogic := False;
Connection.BeginTransaction;
try
lLDA := TDALocalDataAdapter.Create(self);
lcdsDst := TDAMemDataTable.Create(nil);
try
LDA.ServiceInstance := nil;
lLDA.ServiceName := ‘SvcWDUtil’;

 lcdsDst.LogicalName := lEntityName;
 lcdsDst.RemoteFetchEnabled := True;
 lcdsDst.RemoteDataAdapter  := lLDA;
 lcdsDst.LoadSchema;
 lcdsDst.Insert;
 // DO INSERT STUFF
 lcdsDst.Post;
 lcdsDst.ApplyUpdates();
finally
lLDA.Free;
  lcdsDst.Free;
end
  Result := True;  // NO ERRORS... SO TRUE
  Connection.CommitTransaction;
except
  on E:Exception do
  begin
    Connection.RollbackTransaction;
    Result := False;
  end;
end;

end;

12

different instance of TSvcWDUtil hasn’t assigned OnUpdateDataBeginTransaction and Co event. it can be a reason for failure.

13

Do you have any recommendations on how to resolve my problem?

14

you can use session variable that can control this behavior or you can create new TSvcWDUtil instance manually and set required properties