Advice

My Delphi application uses SQLite for its database. My understanding is that the database connection control TFDConnection has a default row count of 50 records. If true, there should only be 50 records displayed in the TDBGrid control on screen at any one time. The next set of 50 (or less) would be shown once you pass the 50th record in the dataset by proceeding to the next or last record.

The problem is my application is apparently loading all the database records (currently 148) at one time which exceeds the default record count using select * from tablename. I have tried to setup a "paging" approach to solve the problem as well using the following:

data.query.last;
TotalRec:=data.query.RecordCount;
PageCnt:=TotalRec div 50;
RecRemain:=TotalRec mod 50;

Then using the following code to get the dataset based on a limit and offset:

data.query.SQL.Text:='select * FROM tablename LIMIT :limit OFFSET :ofs;';
data.query.ParamByName('limit').asInteger:=50;
data.query.ParamByName('ofs').asInteger:=ofs1;
data.query.open;

The variable ofs1 is initially zero but is incremented or decremented by 50. Even this approach is not limiting the dataset to 50.

Any help understanding why this is happening would be very helpful moving forward. Thanks.

Improve this question

5 Replies 5

Uwe Raabe
•

There a bunch of query properties to control the behavior, but calling Last for a query will always load all records.

Improve this answer
Skypilot65
•

@Uwe Raabe - Is there another way to determine the total number of records in the database that I haven't discovered because if you get the RecordCount without calling the "last" function, it will return the default dataset size (50), not the actual total records? Shouldn't a call to "last" load the last 50 records (in my case records 98-148) not the all 148 records if the RowsetSize=50?

Improve this answer
SilverWarior
•

I don't have much experience working with FireDac but isn't the number of returned records controlled using FetchOptions

Based on documentation using Fetch mode fmOnDemand in combination with RowsetSize you should force database to return records in batches based on demand.

Improve this answer
Uwe Raabe
•

FetchOptions has a RecordCountMode property to control the way RecordCount is calculated.

Improve this answer
Skypilot65
•

I agree with @SilverWarior that using the "FetchOptions" is how to control the overall dataset operation. I also agree that in the default mode using the fmOnDemand for the "mode" should force the database to return the records from a database in batches based on demand.

@Uwe Raabe Thank you for pointing that option out, it will be very useful to set it to cmTotal to get the total number of records in the database. This will hopefully prevent the need to use the Last function to get the count.

Improve this answer

Your Reply

By clicking “Post Your Reply”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.