How to get the ID of the last record inserted in SQLite with Delphi 10?

Question

Delphi 10 with Firemonkey and SQLite: After running the code below I want to get the ID of the last record inserted into an SQLite table. How do I get the last ID?

NOTE: The ID field of Table 1 is autoincrement.

var myQr: TFDQuery;
begin

   myQr := TFDQuery.Create(Self);

   with myQr do begin
      SQL.Add('Insert into table1 values (:_id, :_name, :_dthr)');
      Params.ParamByName('_id').ParamType := TParamType.ptInput;
      Params.ParamByName('_id').DataType  := TFieldType.ftInteger;
      Params.ParamByName('_id').Value     := null;

      ParamByName('_name').AsString       := 'name test';
      ParamByName('_dthr').AsDateTime     := Now;
      ExecSQL;
   end;

   // How to get last ID?  <<<<<<<<<<<<<=================

   myQr.DisposeOf;

Answer 1

You could query last_insert_rowid if your ID column is declared as INTEGER PRIMARY KEY. In such case the column becomes alias for the ROWID. If that is your case, you can query it natively e.g. this way:

uses
  FireDAC.Phys.SQLiteWrapper;

function GetLastInsertRowID(Connection: TFDConnection): Int64;
begin
  Result := Int64((TObject(Connection.CliObj) as TSQLiteDatabase).LastInsertRowid);
end;

Or in common way by calling GetLastAutoGenValue method:

function GetLastInsertRowID(Connection: TFDConnection): Int64;
begin
  Result := Int64(Connection.GetLastAutoGenValue(''));
end;