Business processor, capture out param of InsertCommand

Data Abstract
1

When using a business processor, when the InsertCommand is excuted, is there a way to get access to the output of the command that excuted? Without executing the command manually? A perfect example is to get bact the identity of the inserted record and put it back in the delta to send back to the client.

The AfterProcessChange event does not have access to the IDASQLCommand that was executed during the insert.
There is an internal AfterExecuteCommand event, but it is not exposed externally, and it also does not have access the DeltaChange object.

I am trying to do this using the autocreated business processors so I don’t have to code manual executes and updates for all the datatables…

2

Thanks, logged support request as bugs://47833
Posted by Bugs for Mac

3

There is an internal AfterExecuteCommand event, but it is not exposed externally

Logged as #47893, will be published in the next release. You can make it published yourself, add in uDABusinessProcessor.pas:

property AfterExecuteCommand: TDABeforeExecuteCommandEvent read fAfterExecuteCommand write fAfterExecuteCommand;

after the same line with BeforeExecuteCommand

4

It would be great o have that I got the same problem with identity

5

In order for the AfterExecuteCommand event to be really helpful, we need to have access to both the IDASQLCommand and the DeltaChange record. That is extremely important.

6
It would be great o have that I got the same problem with identity

OK, I could not wait to have this fixed properly, so I fixed it my self. I have implemented a NEW AfterExecuteCommand event that is exposed and includes both the ISQLCommand and the DeltaChange row.

My fix actually works for ANY output parameters on the insert and update commands. I can see that there may be a wierd condition if both this and another event try to set CanRemove to alternating values, but I will leave that for the RO guys to address.

A better way to do this might be to add a property to the BusinessProcessor to specify if you want it to compare and copy any ouput values from the insert/update/delete commands back to the delta. That part is only 5 lines of code. I have implemented it in my server, but it would be better off in the processor itself I think.

-scherb

------- Changes to unit uDABusinessProcessor ---------------

//After this line… about line #60
TDAAfterProcessChangeEvent = procedure(Sender: TDABusinessProcessor; aChange: TDADeltaChange; Processed: boolean; var CanRemoveFromDelta: boolean) of object;
//Insert this line
TDAAfterExecuteCommand2Event = procedure(Sender: TDABusinessProcessor; aChangeType: TDAChangeType; aChange: TDADeltaChange;
const aCommand: IDASQLCommand; var CanRemoveFromDelta: boolean) of object;

//After this line … about line #189
fAfterExecuteCommand: TDABeforeExecuteCommandEvent;
//Insert this line
fAfterExecuteCommand2: TDAAfterExecuteCommand2Event;

//After this line … about line #232
procedure DoAfterExecuteCommandEvent(const ACommand: IDASQLCommand; CommandName: String);
//Insert this line
procedure DoAfterExecuteCommand2Event(aChangeType: TDAChangeType; aChange: TDADeltaChange; const aCommand: IDASQLCommand; var CanRemoveFromDelta: boolean);

//After this line … about line #315
property BeforeExecuteCommand: TDABeforeExecuteCommandEvent read fBeforeExecuteCommand write fBeforeExecuteCommand;
//Insert this line
property AfterExecuteCommand2: TDAAfterExecuteCommand2Event read fAfterExecuteCommand2 write fAfterExecuteCommand2;

//Add this procedure
procedure TDABusinessProcessor.DoAfterExecuteCommand2Event(aChangeType: TDAChangeType; aChange: TDADeltaChange;
const aCommand: IDASQLCommand; var CanRemoveFromDelta: boolean);
begin
if Assigned(fAfterExecuteCommand2) then
fAfterExecuteCommand2(Self, aChangeType, aChange, aCommand, CanRemoveFromDelta);
end;

//In the procedure: TDABusinessProcessor.ProcessDelta
//After this line … about line #1757
DoAfterExecuteCommandEvent(currcmd,‘Process Delta’);
//Insert this line
DoAfterExecuteCommand2Event(change.ChangeType, change, currcmd, canremove);

-------In The DataService of My Custom DA Server -----------------

procedure TDataService.DataAbstractServiceBusinessProcessorAutoCreated(aSender: TRORemoteDataModule;
BusinessProcessor: TDABusinessProcessor);
begin
with BusinessProcessor do
begin
AfterExecuteCommand2 := DABusinessProcessor1AfterExecuteCommand2;

//Check for SQLCommands that match the ReferencedDataset
if InsertCommandName = '' then
  if ServerModule.Schema.Commands.FindItem(ReferencedDataset + '_Insert') <> nil then
  begin
    InsertCommandName := ReferencedDataset + '_Insert';
    ProcessorOptions := ProcessorOptions - [poAutoGenerateInsert];
  end;
if UpdateCommandName = '' then
  if ServerModule.Schema.Commands.FindItem(ReferencedDataset + '_Update') <> nil then
  begin
    UpdateCommandName := ReferencedDataset + '_Update';
    ProcessorOptions := ProcessorOptions - [poAutoGenerateUpdate];
  end;
if DeleteCommandName = '' then
  if ServerModule.Schema.Commands.FindItem(ReferencedDataset + '_Delete') <> nil then
  begin
    DeleteCommandName := ReferencedDataset + '_Delete';
    ProcessorOptions := ProcessorOptions - [poAutoGenerateDelete];
  end;

//We need to disable the refresh on datasets that come from stored procs beacuse DA can not properly handle the sql generation..
if (InsertCommandName <> '') or (UpdateCommandName <> '') or (DeleteCommandName <> '') then
begin
  ProcessorOptions := ProcessorOptions - [poAutoGenerateRefreshDataset];
end;

end;
end;

procedure TDataService.DABusinessProcessor1AfterExecuteCommand2(Sender: TDABusinessProcessor; aChangeType: TDAChangeType;
aChange: TDADeltaChange; const aCommand: IDASQLCommand; var CanRemoveFromDelta: boolean);
var
i : Integer;
begin
if (aChangeType <> ctInsert) or (aChangeType <> ctUpdate) then
begin
for I := 0 to aCommand.Params.Count -1 do
begin
if (aCommand.Params[i].ParamType = daptOutput) or (aCommand.Params[i].ParamType = daptInputOutput) then
begin
aChange.NewValueByName[aCommand.Params[i].Name] := aCommand.Params[i].Value;
CanRemoveFromDelta := False;
end;
end;
end;
end;

7

logged as 48009