firebird 3 identity column - return last inserted id I am using FireDAC in Delphi 101. Berlin and I want to get the last inserted id of IDENTITY DATA TYPE column after executing insert query but, the returned value is empty.

Query: INSERT INTO ACC_ACCOUNT_HEAD (ACCOUNT_HEAD_TITLE) VALUES ('TEST') returning ACCOUNT_HEAD_ID;

table structure

CREATE TABLE ACC_ACCOUNT_HEAD (
 ACCOUNT_HEAD_ID       INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  ACCOUNT_HEAD_TITLE    VARCHAR(208),
  PRIMARY KEY (ACCOUNT_HEAD_ID)
);


Note: I know it is possible with generators but I want to know if it is possible with IDENTITY column.

Complete Delphi Code:

var
aQry : TFDQuery;
begin
aQry := TFDQuery.Create(Self);
aQry.Connection := frmMain.aCon;

 aQry.SQL.Text :=
      'INSERT INTO ACC_ACCOUNT_HEAD (ACCOUNT_HEAD_TITLE) VALUES (:P1) returning ACCOUNT_HEAD_ID';
    aQry.ParamByName('P1').AsString := 'Testing';
    aQry.ExecSQL; // or Should I use aQry.Open(); to get returned value???
Have a look at Delphi docs about ExecSQL. (Bold is mine)


  ExecSQL returns an integer value reflecting the number of rows affected by the executed SQL statement.


I'm mostly a CBuilder++ (old) user, but you can try by using:

aQry.Open();
result := aQry.Fields[0].AsInteger;
aQry.Close();


And let me suggest to enclose the query inside a try-catch block.