Delphi FireDAC error when loading SQLite3.dll

Question

I already downloaded the latest SQLite.dll from SQLite Download Page and try to load it using TFDPhysDriverLink.VendorLib

But when I run the app, which contains the following code:

procedure TForm1.FormCreate(Sender: TObject);
begin
  FDConnection1.Close;
  FDPhysSQLiteDriverLink1.Release;
  FDPhysSQLiteDriverLink1.VendorLib:= 'Path\SQLite3.dll';
  FDQuery1.Open('SELECT *, ROW_NUMBER() OVER() Col FROM TableName');
end;

It throws:

[FireDAC][Phys][SQLite] ERROR: near "(": syntax error

Which means that the window function ROW_NUMBER() is not recognized.

• What I'm doing wrong?
• How can I force FireDAC to use the latest SQLite.dll?

Answer 1

SQLite do not support ROW_NUMBER. Look at the answers for this question, you'll probably find something to replace ROW_NUMBER.

Answer 2

If you get this error, then the SQlite3.dll was loaded just fine.

Just use the RowID field, which is always existing for any standard SQLite3 table - unless you explicitly created them with CREATE TABLE WITHOUT ROWID statement.

So I would just write:

 FDQuery1.Open('SELECT *, RowID FROM TableName');

Note that if there is an explicit INTEGER PRIMARY KEY column in your table, it will in fact map the internal RowID column. Check the SQLite3 documentation for how this works.