[fpc-pascal] TSQLQuery and buffering.

[Michael Van Canneyt]

On Mon, 27 Mar 2017, Gary Doades wrote:

>>>> Indeed, that's why I can't currently see where the problem lies. It 
>>> shouldn't buffer the rows/records, but it does... or at least 
>>> something does and I'm pretty sure it's not my program.
>
>> The DB-client library maybe?
>
> Yup!
>
> I was running some other tests and copied the table contents over to both postgres and MS SQL Server.
>
> Postgres has the same issue, all rows are (seemingly) buffered on the
> TSQLQuery.Open and huge amounts of RAM used.  However, using MS SQL Server
> almost no memory is used at all!  All program results are identical and
> all I did in the program was swap out the TSQLConnection with different
> "drivers".
>
> The problem therefore lies in either the Pascal layer on top of the native client libs or in the way the client libs themselves work :( I suspect the latter.

As far as I know, the DB-Specific pascal layer does not buffer anything, it just fetches the result.
There is of course little to no control over how the client lib fetches the result.

If memory serves well, mySQL has 2 separate calls: mysql_use_result and mysql_store_result
where the difference is exactly how it fetches the result set.

Postgres probably fetches everything; 
I don't see how PQGetValue can work index-based otherwise.

Fore interbase/firebird, I don't know.

>
> I will need to do some more investigation in a different way if I am to work round this one.
>
> I apologise for potentially wasting people's time. It might be good for people to know the above issues (limitations?) anyway.

Please report your findings, if any :)

Michael.