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?

Did you check the documentation to see if SQLite supports that syntax at all? And if it does, I believe that it's OVER() that is causing the issue. — Ken White
– Ken White, Commented Oct 13, 2020 at 14:17
SELECT *, ROW_NUMBER() OVER() Col FROM TableName is not valid SQL for sure. — Arnaud Bouchez
– Arnaud Bouchez, Commented Oct 13, 2020 at 15:27
How about SELECT *, ROW_NUMBER() OVER (ORDER BY Col) FROM TableName? See Built-in Window Functions. — Peter Wolf
– Peter Wolf, Commented Oct 13, 2020 at 16:33
Nope, SELECT *, ROW_NUMBER() OVER() Col FROM TableName is valid SQL @ArnaudBouchez, and even SELECT *, ROW_NUMBER() OVER( ORDER BY ColumnName) Col FROM TableName is not working — Ilyes
– Ilyes, Commented Oct 13, 2020 at 17:06
It seem you have to recompile FireDAC, which is really lame. See answer to Firedac not recognizing new ALTER feature in sqlite3. — Peter Wolf
– Peter Wolf, Commented Oct 13, 2020 at 19:04

fpiette · Accepted Answer · 2020-10-13 14:28:33Z

1

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

answered Oct 13, 2020 at 14:28

fpiette

12.5k1 gold badge34 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peter Wolf Over a year ago

That statement is wrong. SQLite supports ROW_NUMBER since version 3.25.0. This is also what the most voted answer to the question you linked to says.

Ilyes Over a year ago

Read the docs sqlite.org/releaselog/3_25_0.html

Arnaud Bouchez · Accepted Answer · 2020-10-13 15:31:43Z

0

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.

answered Oct 13, 2020 at 15:31

Arnaud Bouchez

43.1k3 gold badges78 silver badges165 bronze badges

1 Comment

Ilyes Over a year ago

No, I don't want to use the ROWID, instead I want to use the window function. ROW_NUMBER() OVER() is just an example of window functions.

Collectives™ on Stack Overflow

Delphi FireDAC error when loading SQLite3.dll

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related