DB Pagination and TListView question

[sjordi 39]

Hi All,

I try to implement pagination from a SQLite DB that is bound to a TListView.

After following the docwiki examples using RecsMax, and RecsSkip on my TFDQuery, I can't find a way to actually have the TListView refresh.

If I get only 20 record chunks, I always the same 20 items on my ListView no matter what I do.
The problem is that I'd like the user to be able to navigate the TListView up and down transparently without noticing that the TFDQuery is loading the previous/next chunk of data, this for all platforms, so I won't use a PullToRefresh.

 

I tried another example that seems to work on TGrid and TStringGrid... but TListView?
 

Anybody succeeded in getting pages of records and have TListView correctly react?

Thanks for any clue

Steve

Edited December 27, 2020 by sjordi

[Serge_G 87]

Hi,

I never tried this approach but curious I am 😉

 

I think you simply forget to refresh the link. I wrote a little program to check (Firebird database for instance, further I will write one with SQLite to be sure)

 

here is my thougts

uses System.Math;

procedure TForm3.btnFirstClick(Sender: TObject);
begin
FDQuery1.Open();
LinkListControlToField1.Active:=true;
end;


procedure TForm3.btnPriorPageClick(Sender: TObject);
begin
fdQUery1.Close;
fdQuery1.FetchOptions.RecsSkip:=MaxIntValue([FDQuery1.FetchOptions.RecsSkip-FDQuery1.FetchOptions.RecsMax,-1]);
fdquery1.Open;
LinkListControlToField1.Active:=False;
LinkListControlToField1.Active:=true;
end;



procedure TForm3.btnNextPageClick(Sender: TObject);
var skiprec : Integer;
begin
if fdQuery1.EOF OR (fdQuery1.RecordCount<FDQuery1.FetchOptions.RecsMax) then exit;
skipRec:=FDQuery1.FetchOptions.RecsSkip+FDQuery1.FetchOptions.RecsMax;
fdQUery1.Close;
fdQuery1.FetchOptions.RecsSkip:=skipRec;
fdquery1.Open;
LinkListControlToField1.Active:=False;
LinkListControlToField1.Active:=true;
end;

Even if I am a little disappointed by my EOF management (in my sample  RecordCountCodeMode need to to be cmVisible) this little test works. 

[Serge_G 87]

Ok, so with a SQLite  the same code  does not work 

 

But changing

fdQuery1.Open

to

FdQuery1.Disconnect 

Do the trick. I was not aware of this "disconnect" function 

[sjordi 39]

Ok I did exactly as you did, but it didn't work.

Now instead of FDQuery.Close I set FDQuery.Disconnect instead and now it works...

Just like you, I wasn't aware of Disconnect. 

 

Now I'll implement this when using the TListView scrolls... looks like OnScrollViewChange will do. Except it doesn't tell in which direction you scroll.
But I'll figure this out. Otherwise I'll start a new thread of TListView scrolling events (mouse + finger) 🙂 

 

Thanks a lot for your help.

Steve

[Guest]

On 12/27/2020 at 6:02 AM, sjordi said:

Anybody succeeded in getting pages of records and have TListView correctly react?

hi @sjordi

• RAD Studio 10.3.3 Arch
• FireMonkey project for test
• Interbase Employee DB for test
• pay attention on:
  • FDQuery1.GetNextPacket.ToString  <--- is in action here!

 

   

 

type
  TfrmFormMain = class(TForm)
    FDGUIxWaitCursor1: TFDGUIxWaitCursor;
    FDPhysIBDriverLink1: TFDPhysIBDriverLink;
    FDConnection1: TFDConnection;
    ListView1: TListView;
    FDQuery1: TFDQuery;
    FDQuery1EMP_NO: TSmallintField;
    FDQuery1FIRST_NAME: TStringField;
    FDQuery1LAST_NAME: TStringField;
    FDQuery1PHONE_EXT: TStringField;
    FDQuery1HIRE_DATE: TSQLTimeStampField;
    FDQuery1DEPT_NO: TStringField;
    FDQuery1JOB_CODE: TStringField;
    FDQuery1JOB_GRADE: TSmallintField;
    FDQuery1JOB_COUNTRY: TStringField;
    FDQuery1SALARY: TFMTBCDField;
    FDQuery1FULL_NAME: TStringField;
    BindingsList1: TBindingsList;
    BindSourceDB1: TBindSourceDB;
    LinkListControlToField1: TLinkListControlToField;
    btn_NEXT: TButton;
    btn_PRIOR: TButton;
    Grid1: TGrid;
    LinkGridToDataSourceBindSourceDB1: TLinkGridToDataSource;
    Memo1: TMemo;
    procedure FormCreate(Sender: TObject);
    procedure btn_NEXTClick(Sender: TObject);
    procedure btn_PRIORClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmFormMain: TfrmFormMain;

implementation

{$R *.fmx}
{ // on Help System say to "FDQuery1.FetchOptions.Mode  := TFDFetchMode.fmManual;"

  fmManual
  The records are not fetched automatically by FireDAC. A programmer must write the code to fetch records at appropriate moments, using the following methods:

  FetchAll to fetch all records.
  GetNextPacket to fetch the next record set (packet), containing RowsetSize records.
  -------------------------

  //GetNextPacket:
  -- Fetches the next rowset from the DBMS.
  -- Call GetNextPacket to fetch the next rowset from a DBMS cursor.

  To specify the maximum number of records to return in a rowset, set the FetchOptions.RowsetSize property before calling GetNextPacket.
  A rowset is appended to the records that are already stored in the internal data storage.

  GetNextPacket returns the number of records fetched.
  If the return value is less than FetchOptions.RowsetSize, the dataset has already received all available records from the DBMS cursor.

  Use GetNextPacket for manual fetching mode when FetchOptions.Mode is set to fmManual.


}

var
  lMyMaxRecordsByPage: integer = 10;

procedure TfrmFormMain.btn_NEXTClick(Sender: TObject);
begin
  if (FDQuery1.RecordCount < lMyMaxRecordsByPage) then
    exit;
  //
  FDQuery1.Close;
  FDQuery1.FetchOptions.RecsSkip := FDQuery1.FetchOptions.RecsSkip + lMyMaxRecordsByPage;
  FDQuery1.Open();
  // FDQuery1.FetchAll;
  Memo1.Lines.Add('FDQuery1.GetNextPacket = ' + FDQuery1.GetNextPacket.ToString + ' -  ' + DateTimeToStr(now));
  Memo1.Lines.Add('FDQuery1.FetchOptions.RecsSkip = ' + FDQuery1.FetchOptions.RecsSkip.ToString);
  Memo1.Lines.Add('FDQuery1.RecordCount = ' + FDQuery1.RecordCount.ToString);
end;

procedure TfrmFormMain.btn_PRIORClick(Sender: TObject);
begin
  if (FDQuery1.FetchOptions.RecsSkip - lMyMaxRecordsByPage) < 0 then
    exit;
  //
  FDQuery1.Close;
  FDQuery1.FetchOptions.RecsSkip := FDQuery1.FetchOptions.RecsSkip - lMyMaxRecordsByPage;
  FDQuery1.Open();
  // FDQuery1.FetchAll;
  Memo1.Lines.Add('FDQuery1.GetNextPacket = ' + FDQuery1.GetNextPacket.ToString + ' -  ' + DateTimeToStr(now));
  Memo1.Lines.Add('FDQuery1.FetchOptions.RecsSkip = ' + FDQuery1.FetchOptions.RecsSkip.ToString);
  Memo1.Lines.Add('FDQuery1.RecordCount = ' + FDQuery1.RecordCount.ToString);
end;

procedure TfrmFormMain.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  FDConnection1.Connected := false;
end;

procedure TfrmFormMain.FormCreate(Sender: TObject);
begin
  // ListView1.PullToRefresh   := false; // by default
  // ListView1.PullRefreshWait := false; // by default
  //
  FDQuery1.FetchOptions.RecsMax    := lMyMaxRecordsByPage;
  FDQuery1.FetchOptions.RecsSkip   := 0;
  FDQuery1.FetchOptions.Mode       := TFDFetchMode.fmManual;
  FDQuery1.FetchOptions.RowsetSize := lMyMaxRecordsByPage;
  //
  FDConnection1.Connected := True;
  FDQuery1.Open();
  //
  // FDQuery1.FetchAll;
  Memo1.Lines.Add('FDQuery1.GetNextPacket = ' + FDQuery1.GetNextPacket.ToString + ' -  ' + DateTimeToStr(now));
  Memo1.Lines.Add('FDQuery1.FetchOptions.RecsSkip = ' + FDQuery1.FetchOptions.RecsSkip.ToString);
  Memo1.Lines.Add('FDQuery1.RecordCount = ' + FDQuery1.RecordCount.ToString);
end;

end.

 

hug

Edited December 30, 2020 by Guest

[Serge_G 87]

Hi,

 

@Emailx45 Test have to be done on a SQlite Database, surprisingly   the sequence

FDQuery1.Close;
  FDQuery1.FetchOptions.RecsSkip := FDQuery1.FetchOptions.RecsSkip - lMyMaxRecordsByPage;
  FDQuery1.Open();

 

Does not work, but if you use FDQuery1.Disconnect it is ok. Note I prefer your EOF management to mine 😉, done quickly.

 

@sjordi Now you put this in my mind I am thinking on how pages number can be displayed like what can be seen on the web (the  <1 2 3 .. x > thing) and how search can be implemented (certainly a firedac macro ) .

Quote

Now I'll implement this when using the TListView scrolls... looks like OnScrollViewChange will do. Except it doesn't tell in which direction you scroll.

I think it's a more a gesture management sort of than a ScrollViewChange 

Edited December 30, 2020 by Serge_G

[Guest]

5 hours ago, Serge_G said:

Does not work, but if you use FDQuery1.Disconnect it is ok. Note I prefer your EOF management to mine 😉, done quickly.

Thanks @Serge_G 

I'll go try SQLite for test!

[sjordi 39]

Thanks all,
I'll try this in the coming days. Was too busy... snow shoeing ! 🙂 
I'll let you know if I discover some new things.

[sjordi 39]

Ok I tried the code above and confirm that it works fine.

Disconnect has to be used instead of Close for SQLite.

 

Now with that kind of load, we don't control the vertical scroll bar though. 
It is just to navigate the current view (nn records we have decided to use for pagination)

And the cursor size and position are proportional to those nn records, not the entire set.

Maybe I should find  a way to better optimize the SQL queries as a workaround.

 

Thanks

[Serge_G 87]

Quote

Disconnect has to be used instead of Close for SQLite. 

I was surprised I can use also this Disconnect with Firebird the same way

Quote

It is just to navigate the current view (nn records we have decided to use for pagination) 

And the cursor size and position are proportional to those nn records, not the entire set

I just calculate the Recsmax to be function of ListViewSize and ItemsHeight  (in the onResize of the form so I detect rotation)

FetchOptions.Recsmax:=Round(ListView1.Height/ListView1.ItemAppearance.ItemHeight);

And no more cursors

see my first attempts here https://www.developpez.net/forums/d2099624/environnements-developpement/delphi/composants-fmx/tlistview-pagine-j-ai-besoin-quelques-suggestions/#post11666050

Not updated because I prefered to make a test on Firebird AND SQlite (on windows) .

Added the navigation to a page number (problem ok virtual keyboard solved).

 

Last but not least : implementing the search is the last goal I fixed but not code yet

 

Edited January 4, 2021 by Serge_G

[Guest]

On 12/30/2020 at 4:52 AM, Serge_G said:

@Emailx45 Test have to be done on a SQlite Database, surprisingly   the sequence

hi @Serge_G

Tested with SQLite DB and really "Disconnect( FALSE )" it should be used!

// unit FireDAC.Comp.Client;
procedure TFDCustomCommand.AbortJob(AWait: Boolean = False);
...

Works ok!

 

NOTE:

• IF last records = next-records-to-Fetch  (in my verification) -- can generate the last page in "blank" - because the "GetNextRecord" would needs to be verify again for "exist more records in last page"
• not my fault, but the behavior for this "procedure"
  • calling "GetNextRecord" we have "n" records in table based on "FetchOptions.RecsSkip" value
  • then, if the "RecordCount < lMyMaxRecordByPage" not founded yet, or because you dont passed the "EOF", then, the verification think that exist more records. you see?
    • to avoid this, you would need:
      • 1) Call 2x "GetNextRecord" -- not prefered!!!
      • 2) Verify how many records exist yet -- but "FetchOptions.RecsSkip" value will go hinder verification, because there will only be "n" records ... not "the last records" - you see?
      • 3) or do a manual calculation page by page --- I also find it unnecessary.
    • Anyway, perhaps the best option would be for the component to check if the rows (or the first row) of data is empty and exit the "Next Records" procedure.

  // possible last page can be "empty", because we need verify if exist more records... EOF or not... but is necessary forward next "n" records for that!
  if (FDQrySQLite.RecordCount < lMyMaxRecordsByPage) then
    exit;

 

hug

Edited January 4, 2021 by Guest