Hello,

do you know of any component/dataset combo that works like a TDBGrid but doesn't load all the records from the db when doing a refresh, just the ones that are currently visible, yet offering seamless scrolling? I looked in the wiki but I couldn't find any.

Bye

El 23/2/23 a les 9:04, Luca Olivetti via lazarus ha escrit:

Something like this:

https://docwiki.embarcadero.com/RADStudio/Rio/en/Browsing_Tables_(FireDAC)#Live_Data_Window_Mode

Bye

This is not something that the grid handles. This is a dataset feature.

At this moment I know of no FPC/Lazarus dataset component that handles this automatically.

Note the remark on that page:

"Although FireDAC minimizes the number of generated and executed SQL commands in LDW mode, it still produces a heavier DB load than TFDQuery. So, application developers should carefully choose when to use TFDTable and LDW mode. "

This is exactly why FPC does not have a database table component. It's horribly inefficient.

Michael.

May be making a custom component based on VirtualTreeView ?

El 23/2/23 a les 9:43, Michael Van Canneyt via lazarus ha escrit:

Yes, I supposed so

At this moment I know of no FPC/Lazarus dataset component that handles this automatically.

bummer :-(

OTOH a TDBGrid is a very convenient way to display data. I my application I just use it to display (in read-only mode) an sqlite table that holds less than 1000 records and it could be updated quite frequently (hence the need for the frequent TDataset.refresh), and it works fast enough with that limited amount of records. Now I need to expand the table to 10000 records and the performance is unacceptable.

Bye

The best way is to use "limit N offset M" in your query and use a paging mechanism. Make sure you sort the records, obviously.

But IMO:
in the first place you should ask yourself if displaying 10.000 records is what you actually want to do.

Maybe your application is better off with some filtering ?

Michael.

Maybe upgrade to a DBMS (like firebird, mssql,postgres) that supports notifications so that you don't have to poll, but get a notification if something changes?

El 23/2/23 a les 11:11, Michael Van Canneyt via lazarus ha escrit:

But IMO: in the first place you should ask yourself if displaying 10.000 records is what you actually want to do.

No, I just need to display 20 or so records, the problem is I'd like the user to be able to scroll over the whole table, so I'd have to hook a lot of events in the TDBGrid (if that's even possible, otherwise I'd have to implement a replacement) and get rid of the TDBNavigator and implement a custom one in its place.

Bye

El 23/2/23 a les 11:25, Marco van de Voort via lazarus ha escrit:

Maybe upgrade to a DBMS (like firebird, mssql,postgres) that supports notifications so that you don't have to poll, but get a notification if something changes?

No, that's not possible, in any case polling isn't a problem: it's the same application that modifies the table, and it only does the "refresh" when necessary.
The problem is that it happens quite frequently (mind me, in a test environment, in the field the updates happen once or twice a minute, but I'm still not comfortable with the refresh taking too long).

Bye

Le 23/02/2023 ? 11:37, Luca Olivetti via lazarus a ?crit?:

No, that's not possible, in any case polling isn't a problem: it's the same application that modifies the table, and it only does the "refresh" when necessary.

That's exactly a use case for my orm (
https://github.com/jsuzineau/pascal_o_r_mapping/tree/TjsDataContexte ) It's working with the Observer design pattern, no need to poll

Unfortunately, I don't have written any documentation.

It works with SQLite, there is even a source code generator that can create all the boiler plate code from the SQLite database. All the templates of the source code generator can be customized.

Two of my last releases that uses this orm:

https://github.com/jsuzineau/pascal_o_r_mapping/releases/tag/2023_02_20_jsWorks

https://github.com/jsuzineau/pascal_o_r_mapping/releases/tag/2023_02_17_jsCompta

El 23/2/23 a les 12:24, Jean SUZINEAU via lazarus ha escrit:

Thank you, but from a quick look it doesn't seem to solve the problem at hand.

Bye

The LCL could do with a pager component. Seems to be standard when Web pages display grids/tables, so users should be used to it :-)

Michael.

El 23/2/23 a les 13:52, Michael Van Canneyt via lazarus ha escrit:

Even if such a component existed, I don't think it would fit my use-case.

Maybe I should just write a TDataset descendant tailor made for this application, any pointer on how to write a minimal descendant? I'm looking at the TMemDataset implementation as an example, is it a good one?
The idea is to substitute open and scrolling (next/prior/locate/etc.) with the appropriate sql statements to just retrieve a small buffer of data around the current record (I can do that easily enough), though I'm not sure that would be enough to fool the TDBGrid.

Bye

Why do you think so ? Paging is exactly what you want to do.

Maybe I should just write a TDataset descendant tailor made for this application, any pointer on how to write a minimal descendant? I'm looking at the TMemDataset implementation as an example, is it a good one?

As good as any other.

The idea is to substitute open and scrolling (next/prior/locate/etc.) with the appropriate sql statements to just retrieve a small buffer of data around the current record (I can do that easily enough), though I'm not sure that would be enough to fool the TDBGrid.

I don't know what the grid exactly does, so I cannot advise.

Michael.

El 23/2/23 a les 17:16, Michael Van Canneyt ha escrit:

> Even if such a component existed, I don't think it would fit my use-case. Why do you think so ? Paging is exactly what you want to do.

Not exactly, I mean, yes, it is paging but it has to be "transparent", I don't want to add "previous page" and "next page" buttons.

Bye

El 23/2/23 a les 17:16, Michael Van Canneyt ha escrit:

write a TDataset descendant tailor made for this

I did some tests and the grid is not a problem (it only fetches the records that are visible), the TDataset is: either I am blind or the only way for the TDataset to realize there is data is to fetch every record and allocate the corresponding buffers.
That's because FRecordCount/FBof/FEof are private

procedure TDataSet.DoInternalOpen;
begin
   InternalOpen;
   FInternalOpenComplete := True;
{$ifdef dsdebug}
   Writeln ('Calling internal open');
{$endif}
{$ifdef dsdebug}
   Writeln ('Calling RecalcBufListSize');
{$endif}
   FRecordCount := 0;
   RecalcBufListSize; <-- this will fetch all the records
   FBOF := True;
   FEOF := (FRecordCount = 0); <--- FRecordCount must be set
end;

There's no point overriding
GetNextRecords/GetNextRecord/GetPriorRecords/GetPriorRecord (called by RecalcBufListSize) since I cannot directly modify the private fields of the TDataSet.

Any idea?

Bye

FRecordCount is the number of records in internal buffers, and has nothing to do with the total number of records.

The total RecordCount must be given by TDataset descendents, but defaults to FRecordCount in TDataset itself.

The terminology is confusing, I know.

We should probably rename FRecordCount to FBufferRecordCount.

No.

RecalcBufListSize does not necessarily fetch all the records. It fetches as much records as needed: 10 by default, but classes such as TDBGrid may set the number of needed records to the number of visible grid lines.

It can be that TBufDataset fetches all records on open, there is a property that controls this. But it can also fetch on an as-neede basis.

FEOF is simply initialized from the first batch.
But it is maintained based on the result of GetNextRecord. Check the MoveBy procedure.

Only when GetNextRecord returns false, is FEOF set to True (line 2005).

There's no point overriding GetNextRecords/GetNextRecord/GetPriorRecords/GetPriorRecord (called by RecalcBufListSize) since I cannot directly modify the private fields of the TDataSet.

You don't need to modify them. They are automatically maintained based on the result of GetNextRecord. TDataset is responsible for maintaining a correct state based on what descendants return in the methods they override.

Exposing these internal state fields for modification means TDataset cannot guarantee the correct state, and therefore that will not happen.

Any idea?

Personally, I believe you are on the wrong path by wanting to modify TDataset or its descendants.

But if you want to pursue that track, study TDataset a little more and ask questions. It is a complex class, one of the most complex in the whole FCL.

Michael.

El 24/2/23 a les 11:10, Michael Van Canneyt via lazarus ha escrit:

Thank you!

With these insights (and a lot of sweat, blood and tears ;-)) I managed to write a descendant dataset that does what I want. Not 100% complete yet but getting there.

Bye