How to set a PDO statement attribute?

[aliechti]

I have to use the each method to select in batches. The problem is, that I have to delete some columns in this foreach, which generates a table lock. Now I have to set the cursor PDO::ATTR_CURSOR to forward only PDO::CURSOR_FWDONLY. Is this even possible by now?

$simpleQuery = SimpleObject::find();
// need something like: $simpleQuery->setPdoStmtAttr(\PDO::ATTR_CURSOR, \PDO::CURSOR_FWDONLY);
foreach ($simpleQuery->each() as $simpleObject) {
    // fancy things...
}

Funding

• You can sponsor this specific effort via a Polar.sh pledge below
• We receive the pledge once the issue is completed & verified

[samdark]

Yes. You can access PDO instance via connection class.

[aliechti]

Yes but the PDO::ATTR_CURSOR attribute must be set on the pdo statement object.

Exception 'PDOException' with message 'SQLSTATE[IMSSP]: The given attribute is only supported on the PDOStatement object.'

[samdark]

Command::$pdoStatement is public.

[aliechti]

And how do I use it in my snipped?

[samdark]

Same as you'd use it with PDO.

[aliechti]

So it is not possible with the each statement?

[samdark]

Currently not.

[aliechti]

Hmm I just looked into it, but I don't know where I should put the change in.

1.Loop the Attributes through the Query

$query->setPdoAttributes([\PDO::ATTR_CURSOR => \PDO::CURSOR_FWDONLY])->each();

2.Make the method Query::createCommand return the same Command for every call, save it in a private/protected variable and just return it if called twice.

$query->command->setPdoAttributes([\PDO::ATTR_CURSOR => \PDO::CURSOR_FWDONLY]);
$query->each();

[samdark]

#1 looks better.

[samdark]

https://github.com/yiisoft/yii2/pull/9973

[terabytesoftw]

yiisoft/yii2#9973

We implement this change?

[samdark]

Yes. That's useful.