unit uDBPagination;

{< DB pagination for mySQL - adding LIMIT M,N phrase to Select Request}

interface

uses
  System.SysUtils, System.Classes, FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Param, FireDAC.Stan.Error,
  FireDAC.DatS, FireDAC.Phys.Intf, FireDAC.DApt.Intf, FireDAC.Stan.Async, FireDAC.DApt, Data.DB, FireDAC.Comp.DataSet,
  FireDAC.Comp.Client;

type
  TDBPagination = class(TDataModule)
  private

   FFDQuery:TFDQuery;
   FSQLRequest:string;

   FCurrentRow:Int64;

   FStartRow:Int64;
   FRowsOnPage:Int64;

   FInitialSQLText:string;

   FNumberOfPages:Integer;

  public
    { Public declarations }

    procedure LoadSomeLimitedNumberOfRows(AStartRow:Integer; ACountOfRows:integer);
    procedure CountNumberOfPages();

    procedure LoadFirstPage();

    procedure LoadNextPage(AStartRow,ANumberOfRowsToLoad:Int64);
    procedure LoadPreviousPage(AStartRow,ANumberOfRowsToLoad:Int64);

    property FDQuery:TFDQuery read FFDQuery write FFDQuery;
    property SQLRequest:string read FSQLRequest write FSQLRequest;


    property  StartRow:Int64 read FStartRow write FStartRow;
    property  RowCountToLoad:Int64 read FRowsOnPage write FRowsOnPage;

    property InitialSQLText:string read FInitialSQLText write FInitialSQLText;



  end;


//var
//  DBPagination: TDBPagination;

implementation

{%CLASSGROUP 'Vcl.Controls.TControl'}

{$R *.dfm}

{ TDBPagination }

procedure TDBPagination.CountNumberOfPages;
begin

FNumberOfPages:=Ceil(FDQuery.RecordCount/FRowsOnPage);

end;

procedure TDBPagination.LoadFirstPage;
begin

LoadSomeLimitedNumberOfRows(0,3);

end;

procedure TDBPagination.LoadSomeLimitedNumberOfRows(AStartRow:Integer; ACountOfRows:integer);

var SQLTextTemp:String;

begin

    if Assigned(FDQuery) then

     begin

         FDQuery.Disconnect();

        // Attention here ! Original statement FDQuery.SQL.Text
        // should be without semicolon
        FDQuery.SQL.Text:=FInitialSQLText;
        FDQuery.SQL.Text:=FDQuery.SQL.Text+' LIMIT :StartRow,:CountOfRows';


        with FDQuery do begin

        Params.ParamValues['StartRow']:=AStartRow;
        Params.ParamValues['CountOfRows']:=ACountOfRows;


        end;



       FDQuery.Open();


     end;


 end;





procedure TDBPagination.LoadNextPage(AStartRow, ANumberOfRowsToLoad: Int64);
begin

end;

procedure TDBPagination.LoadPreviousPage(AStartRow, ANumberOfRowsToLoad: Int64);
begin

end;

end.
