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. — Freddie Bell
– Freddie Bell, Commented Jul 18, 2019 at 16:21
if you wish FDTable data sorted you must define FDTable Index. For example fdtable1.IndexFieldNames := 'Location'; — Branko
– Branko, Commented Jul 18, 2019 at 17:27
@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
– WobblyBob, Commented Jul 18, 2019 at 17:50
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
– JacalarRick, Commented Jul 18, 2019 at 17:59
@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, Commented Jul 19, 2019 at 20:04

JacalarRick · Accepted Answer · 2019-07-19 15:47:59Z

2

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;

answered Jul 19, 2019 at 15:47

JacalarRick

1531 silver badge8 bronze badges

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

2 Comments

WobblyBob Over a year ago

thank you for your suggestions - the code works perfectly. So, having two methods of sorting now, Indexes or Order By, I suppose the obvious question from a novice like myself is which of the two is the more elegant in coding terms. As the database will probably have no more than a couple of hundred records, I suspect that either method is as good as the other. So now onto my next challenge, Inserting variable values into the records ;) Many thanks again .

JacalarRick Over a year ago

It really comes down to how you choose to access your data. If you always want to present the entire data table, then create the tables with the indices. If you may want to be displaying subsets of the data in the grid (e.g. only sites with ping time less than 50), you will be using queries. You can also define indices for query objects, within your code. Think about what it is you want to do with the data -- not just the specifics of getting it into a specific order.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related