If you want to display only a range of results from a recordset you can using paging (otherwise known as pagination) to do so. It is a common pattern when doing web development to give the user 20 results for example and then the ability to move to the next page. If you are implementing a REST API in RAD Server you might also implement this functionality instead of delivering 2 million rows for the query you only deliver the first 20 rows and the next 20 can be requested in the next call. Obviously this works with Delphi FireMonkey applications on Android, iOS, macOS, and Windows that use FireDAC.
You can do paging manually with SQL and usually each different server like MSSQL or MySQL has a different SQL command to do paging. A paging query for MySQL might look like this: SELECT * FROM TABLE_NAME LIMIT 0,25 Whereas in SQL Server it would be different and might use an SQL syntax containing TOP or OFFSET. However, FireDAC allows you to do paging within a result set without changing your SQL (it handles the SQL behind the scenes). This means you code is database server independent. The two properties on a TFDQuery that allow paging are FetchOptions.RecsSkip and FetchOptions.RecsMax. Check out the DocWiki which shows a simple example of paging.
You can combine paging with the TGrid or TStringGrid controls using LiveBindings. Once you LiveBind a TStringGrid to your TFDQuery control only the results from the current page will show up in the grid. Here is an example of the DFM/FMX code showing the TFDQuery LiveBinded to a Grid control.
//
//
object BindSourceDB1: TBindSourceDB
DataSet = FDQuery1
ScopeMappings = <>
Left = 304
Top = 224
end
object BindingsList1: TBindingsList
Methods = <>
OutputConverters = <>
Left = 20
Top = 5
object LinkGridToDataSourceBindSourceDB1: TLinkGridToDataSource
Category = 'Quick Bindings'
DataSource = BindSourceDB1
GridControl = StringGrid1
Columns = <>
end
end
Once you have LiveBindings set up between the TFDQuery and the TStringGrid you simply need to change the RecsSkip property and reload the SQL query to get the new result set. In the example code below the Active property of the LinkGridToDataSourceBindSourceDB1 component is set to False and then True again to refresh the data in the grid. In the example code RecsMax is set to 2 but you can change that to however many results you want per page (like 20, 25, 50, 100, etc).
// // procedure TForm1.BackBTNClick(Sender: TObject); begin FDQuery1.Close; FDQuery1.FetchOptions.RecsSkip := FDQuery1.FetchOptions.RecsSkip-1; FDQuery1.open; LinkGridToDataSourceBindSourceDB1.Active := False; LinkGridToDataSourceBindSourceDB1.Active := True; end; procedure TForm1.NextBTNClick(Sender: TObject); begin FDQuery1.Close; FDQuery1.FetchOptions.RecsSkip := FDQuery1.FetchOptions.RecsSkip+1; FDQuery1.open; LinkGridToDataSourceBindSourceDB1.Active := False; LinkGridToDataSourceBindSourceDB1.Active := True; end; procedure TForm1.FormCreate(Sender: TObject); begin FDQuery1.FetchOptions.RecsMax := 2; FDQuery1.FetchOptions.RecsSkip := 0; end;
And that is all there is to paging using FireDAC. You could pass in the RecsMax and RecsSkip properties on a REST API call in RAD Server and get similar results.