Search code examples
sqlitedelphifiredac

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

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.

Solution

  • 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;