How to execute unbuffered query

[rolek]

Hi!

In my application, there is one query that I need to execute unbuffered, because it returns too many results to fit in memory. I would like to execute this one query unbuffered, while leaving the rest of the queries as buffered. I’m using laminas-db with PDO to MySQL.
There doesn’t seem to be a way to run

setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

on the PDO resource after the database connection is established, and I don’t want to configure the entire connection as unbuffered. Ideally, I would like to avoid using two database connections as well.

Is there any way to do this?

Thanks in advance, Roel

[froschdesign]

Hello and welcome to our forums!

@rolek

…on the PDO resource after the database connection is established…

This means to fetch the resource.

Here is an example for an adapter:

$adapter = new Laminas\Db\Adapter\Adapter([
    'driver'   => 'Pdo_Sqlite',
    'database' => 'sqlite::memory:',
]);
/** @var PDO $resource */
$resource = $adapter->getDriver()->getConnection()->getResource();
$resource->setAttribute(…, …);

Introduction - laminas-db - Laminas Docs

Here is an example for a table gateway:

$tableGateway = new Laminas\Db\TableGateway\TableGateway(
    'table_name',
    new Laminas\Db\Adapter\Adapter([
        'driver'   => 'Pdo_Sqlite',
        'database' => 'sqlite::memory:',
    ])
);
/** @var PDO $resource */
$resource = $tableGateway->getAdapter()->getDriver()->getConnection()->getResource();
$resource->setAttribute(…, …);

Table Gateways - laminas-db - Laminas Docs

[rolek]

Thanks, this should definitely get me going!

[rolek]

A quick follow-up question: Can I initialize a ResultSet with an unbuffered result to process it?

Like this, I mean:

$result = $statement->execute();
$resultSet = new ResultSet(ResultSet::TYPE_ARRAY);
$resultSet->initialize($result);

Or does this mean that the entire result is read into the ResultSet at once?

Thanks!

[rolek]

And answering my own question: Yes, this works.