How to get the id of the last record inserted into SQLite using Delphi 10?

Question

Delphi 10 with Firemonkey and SQLite: after running the code below, I want to get the identifier of the last record inserted into the SQLite table. How to get the last identifier?

NOTE. The table ID field 1 is auto-incrementing.

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 can query last_insert_rowid if the ID column is declared as an INTEGER PRIMARY KEY . In this case, the column becomes an alias for the ROWID . If this is your case, you can request it initially, for example. in the following way:

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

Or in the usual way by calling GetLastAutoGenValue :

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