#559 zeosdbo-svn + lazarus + mariadb

[DaLiV]

DB with big tables. need - therefore want to lower trafic to application.
tried to set in connection parameters
prefetch_rows=50

but still all records are fetched from server
seems no LIMIT added to statements

SqlMonitorLog:
'2022-09-18 23:02:51.089' cat: Prepare, proto: mysql, msg: Statement 5 : select * from data
'2022-09-18 23:02:51.120' cat: Execute prepared, proto: mysql, msg: Statement 5, elapsed time: '00:00:00.029'
'2022-09-18 23:02:53.121' cat: Fetch complete, proto: mysql, msg: Statement 5, fetched row(s): 3206

Driver libmariadb - FClientVersion identified as 30302
server : 10.5.16-MariaDB
Zeos : svn-7867

[marsupilami79]

Hello DaliV,

the prefetch_rows parameter isnt meant to be used to limit the amount of data. This is what the mariadb reference has to say about prefetch_rows:

STMT_ATTR_PREFETCH_ROWS: number of rows which will be prefetched. The default value is 1.

If you want to limit the number of rows that travel from the server to the client, you will have to add the limit clause yourself. Zeos cannot do that.

With best regards,

Jan

[AE Himself]

You can try with TZAbstractRODataset.FetchRow property, which is published in all major dataset component (TZReadOnlyQuery, TZQuery, TZTable).
Just tried with D11.2 on MySQL. When FetchRow is 0 (default) ZQuery1.RecordCount is above 20k during the cycle. When setting it to 1 .RecordCount increases by one each time instead:

While Not ZQuery1.Eof Do
 ZQuery1.Next; // Put a breakpoint here and check .RecordCount