1

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?
Ilyes
  • 14,640
  • 4
  • 29
  • 55
  • 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 Oct 13 '20 at 14:17
  • 2
    `SELECT *, ROW_NUMBER() OVER() Col FROM TableName` is not valid SQL for sure. – Arnaud Bouchez Oct 13 '20 at 15:27
  • How about `SELECT *, ROW_NUMBER() OVER (ORDER BY Col) FROM TableName`? See [Built-in Window Functions](https://sqlite.org/windowfunctions.html#built_in_window_functions). – Peter Wolf Oct 13 '20 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 Oct 13 '20 at 17:06
  • What is the SQLite version? `SELECT sqlite_version()` – Peter Wolf Oct 13 '20 at 17:11
  • @PeterWolf FireDAC will returns `3.8.7.4` and that's why I'm trying to load the `SQLite.dll` – Ilyes Oct 13 '20 at 17:14
  • After setting up the physical driver link open the connection in the IDE to the **Info** tab and see what it is loading and why. In the IDE it needs the 32bit version. – Brian Oct 13 '20 at 17:47
  • The IDE will load the statically linked .dll file @Brian `DLL = ` – Ilyes Oct 13 '20 at 18:10
  • Did you specify an absolute path? – Olivier Oct 13 '20 at 18:33
  • Yup @Olivier I did – Ilyes Oct 13 '20 at 18:38
  • @moskito-x You really ask such question? The `TFDConnection.Connected` property will be `True` automatically when you call `TFDQuery.Open()` method. Sorry, but that is not related to the question. – Ilyes Oct 13 '20 at 18:55
  • just try : `FDPhysSQLiteDriverLink1.VendorLib:= 'Path\SQLite3.dll'; FDConnection1.Open; ..... ` – moskito-x Oct 13 '20 at 19:02
  • 1
    It seem you have to recompile FireDAC, which is really lame. See answer to [Firedac not recognizing new ALTER feature in sqlite3](https://stackoverflow.com/a/57192897/11562188). – Peter Wolf Oct 13 '20 at 19:04
  • It's simpler with [Sydney](http://docwiki.embarcadero.com/RADStudio/Sydney/en/Connect_to_SQLite_database_(FireDAC)): no need to change FireDAC.inc anymore. – Olivier Oct 13 '20 at 19:18
  • The trick didn't work too @PeterWolf – Ilyes Oct 13 '20 at 19:55
  • Did you put the DLL next to the executable, as mentioned in the [doc](http://docwiki.embarcadero.com/RADStudio/Seattle/en/Connect_to_SQLite_database_(FireDAC))? Don't use `VendorLib`. – Olivier Oct 13 '20 at 20:15
  • Also consider using [this alternative library](https://github.com/plashenkov/SQLite3-Delphi-FPC). – Olivier Oct 13 '20 at 20:18
  • I don't see how `OVER()` could be a valid syntax, from https://www.sqlite.org/windowfunctions.html point of view. You need some expression within OVER. – Arnaud Bouchez Oct 14 '20 at 20:42
  • Read the docs again @ArnaudBouchez and especially the examples provided. `OVER` and `OVER()` are valid. – Ilyes Oct 14 '20 at 21:15
  • So perhaps FireDAC is validating the SQL before executing it, and doesn't know about Windowed functions. There are alternatives libraries with direct access to SQLite3. – Arnaud Bouchez Oct 15 '20 at 12:53
  • The problem is that FireDAC keep using the [static licking](https://stackoverflow.com/questions/64336237/delphi-firedac-error-when-loading-sqlite3-dll?noredirect=1#comment113773911_64336237) @ArnaudBouchez – Ilyes Oct 15 '20 at 12:56

2 Answers2

1

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

fpiette
  • 11,983
  • 1
  • 24
  • 46
  • That statement is wrong. SQLite *supports* `ROW_NUMBER` since version [3.25.0](https://www.sqlite.org/releaselog/3_25_0.html). This is also what the most voted answer to the question you linked to says. – Peter Wolf Oct 13 '20 at 15:41
  • Read the docs https://www.sqlite.org/releaselog/3_25_0.html – Ilyes Oct 13 '20 at 17:07
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.

Arnaud Bouchez
  • 42,305
  • 3
  • 71
  • 159
  • 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. – Ilyes Oct 13 '20 at 17:09