ZF2 already active query prevents execution

Question

I have something like the following, in a function that deletes both the files and db entries:

$adapter = $this->getAdapter();
$query = $adapter->query("call find_results_by_job_id(?)", array($jobId));
$items = array();
while (($current = $query->current()) !== false)
{
    $id = $current['id'];
    $items[] = $id;
    $query->next();
}
$this->deleteFromDataStore($items);
$result = $adapter->query("call delete_results_by_job_id(?)", array($jobId), \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);

(Some of that might not look like the best way to do it, because I simplified it for this example)

I'm getting this error on the last line: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.

I'm assuming that the problem is because the query/adapter hasn't closed the connection from iterating results yet when I try to execute another statement. If that is the case, how can reuse the adapter, or close the query, or whatever I have to do before the last line?

The strange part is that code following almost exactly this same pattern works in another method.

---

Answer:

When using the PDO driver, $query->getDataSource()->getResource()->closeCursor(); fixes it

Answer 1

Assuming you have the result of a query in your hands and you dont know whether it is a ResultSet or a Result, the following will do the job. Tested as of Zend Framework 3.

use Zend\Db\ResultSet\ResultSet;
...

public function closeResult( $result )
{
    if( is_a($result, ResultSet::class) )
    {
        $stmt = $result->getDataSource()->getResource();
    }
    else
    {
        $stmt = $result->getResource();
    }
    $stmt->closeCursor();
}

Answer 2

$this->adapter
->query($sql)
->execute()
->getResource()
->fetchAll(\PDO::FETCH_ASSOC);

Answer 3

Seems like you are using an unbuffered query in MySQL.

If it is so, you will either have to turn buffering on or break execution of previous query which seems to hang?

Something like $query->close()

EDIT:

If $query is instance of StatementInterface, then there is getResource() which returns mysqli_stmt and you can call close() on it.

EDIT2: (to incorporate final resolution)

In case it uses PDO, you can get PDOStatement and call closeCursor()