Delphi FireDac - Get auto-incremented value after overriding post Updates

2
votes

I use FireDac with an Oracle 12c Database and Delphi Seattle.

I have a table Employee

EmployeeID - NUMBER GENERATED ALWAYS AS IDENTITY
HireDate - Date

On my form i have a FDQuery with the following statement:

select * from Employee

After FDQuery1.Insert and FDQuery1.Post i can get the generated EmployeeID with the following code:

  FDQuery1.Insert;
  ...
  FDQuery1.Post;
  ShowMessage(IntToStr(FDQuery1.FieldByName('EMPLOYEEID').AsInteger));

This is working great.

The problem:

I now want to override the FDQuery Posting Updates like described here: http://docwiki.embarcadero.com/RADStudio/XE8/en/Overriding_Posting_Updates_%28FireDAC%29

So i override the posting updates:

procedure TForm3.FDQuery1UpdateRecord(ASender: TDataSet;
  ARequest: TFDUpdateRequest; var AAction: TFDErrorAction;
  AOptions: TFDUpdateRowOptions);
begin
  FDUpdateSQL1.ConnectionName := FDQuery1.ConnectionName;
  FDUpdateSQL1.DataSet := FDQuery1;
  FDUpdateSQL1.Apply(ARequest, AAction, AOptions);

  AAction := eaApplied;
end;

But now the EMPLOYEEID shows as -1 :(

  ShowMessage(IntToStr(FDQuery1.FieldByName('EMPLOYEEID').AsInteger));

It doesn't matter what statement i enter in FDUpdateSQL1.InsertSQL. I can even leave it blank. The result is always the same.

(This was also asked on https://forums.embarcadero.com/thread.jspa?messageID=778896&#778896)

1
delphifiredac

1 Answers

0
votes

By Dmitry Arefiev from the Embarcadero Discussion Forum:

Try to use INSERT ... RETURNING EmployeeID INTO :NEW_EmployeeID in your FDUpdateSQL1.InsertSQL. You will need also setup :NEW_EmployeeID parameter like that:

with FDUpdateSQL1.Commands[arInsert].ParamByName('NEW_EmployeeID') do begin
  ParamType := ptOutput;
  DataType := ftInteger;
end;