Firedac Sqlite Query runs in Query Editor, but not when called from code

Question

I am experiencing problems with with an In Memory SQlite Database using Firedac in Delphi 10.3.1. I have added the appropriate components to my form, set the Connection DriverID to SQLite and set the Database name to :memory: (for in memory database). I have created the FDQuery1 SQL dummy data as below:

DROP TABLE IF EXISTS dnsstats; (For debug purposes only)
create table DNSStats(Location nvarchar(30), IP_Address 
nvarchar(20),Ping_Time Integer,Index_No Integer);
insert into DNSStats values ('NoWhere', '123.234.111.112',100,1);
insert into DNSStats values ('AnyWhere', '123.234.111.113',10,2);
insert into DNSStats values ('SomeWhere', '123.234.111.114',120,3);
insert into DNSStats values ('WhatWhere', '123.234.111.115',106,4);
insert into DNSStats values ('ShareWhere', '123.234.111.116',101,5);
insert into DNSStats values ('UnderWhere', '123.234.111.117',200,6);
select * from DNSStats ORDER BY Location ASC;

This SQL works perfectly when executed in the Query Editor. I can change the ORDER BY to any of the fields and the SQL still functions correctly.

However when run from code as below:

procedure TForm1.Button1Click(Sender: TObject);
begin
datasource1.DataSet:=fdtable1;
fdquery1.execute();
fdtable1.TableName:='DNSStats';
dbgrid1.DataSource:=datasource1;
fdtable1.Active:=true;
end;

The table is populated with the appropriate data but the ORDER BY Location ASCis ignored, and this is the case when any other field is selected for ORDER BY. No error message is posted by the application.

I cannot work out why it works in the Query Editor but not from code. I am still quite new to SQlite and Firedac so any help will be much appreciated.

Your configuration doesn't make sense. What does fdQuery1 do? It isn't at all related to the fdtable chain. — nolaspeaker
if you wish FDTable data sorted you must define FDTable Index. For example fdtable1.IndexFieldNames := 'Location'; — Branko
@Branko - I came to the same conclusion as you in as much as I have now modified my SQL to create indexes for each of the fields. The table can now be sorted as you have suggested. However it still does not explain why my original SQL works in the Query Editor and not when called from code. Is this going to be one of the mysteries of life or will somebody shed some light on the issue ?? From a purely learning point of view it would be nice to have an answer. — WobblyBob
If you set your grid to a data source associated with a query, the query can define a sort order, but you have defined the grid to be associated with a table, which, in the the initial example, has no primary key or index. — JacalarRick
@MartynA Actually, it is very relevant. He wants to know why his grid is not displaying the data in a sorted order, based on a query’s Order by clause. Yet, the grid is attached to the table, not the query. The INSERT statements in that query, before the SELECT define the order of the data In the table. — JacalarRick

JacalarRick JacalarRick · Accepted Answer · 2019-07-19T15:47:59

Instead of using an FDTABLE, use two FDQueries

Set the SQL Text of FDQuery1 to

DROP TABLE IF EXISTS dnsstats; (For debug purposes only)
create table DNSStats(Location nvarchar(30), IP_Address 
nvarchar(20),Ping_Time Integer,Index_No Integer);
insert into DNSStats values ('NoWhere', '123.234.111.112',100,1);
insert into DNSStats values ('AnyWhere', '123.234.111.113',10,2);
insert into DNSStats values ('SomeWhere', '123.234.111.114',120,3);
insert into DNSStats values ('WhatWhere', '123.234.111.115',106,4);
insert into DNSStats values ('ShareWhere', '123.234.111.116',101,5);
insert into DNSStats values ('UnderWhere', '123.234.111.117',200,6);

and the SQL Text of FDQuery2 to

select * from DNSStats ORDER BY Location ASC;

Then

procedure TForm1.Button1Click(Sender: TObject);
begin
datasource1.DataSet:=fdquery2;
dbgrid1.DataSource:=datasource1;
fdquery1.execute();
fdquery2.Open();
end;

Firedac Sqlite Query runs in Query Editor, but not when called from code

1 Answers