Unable to iterate over a database ResultSet twice

[zfbot]

Jira Information

Original Issue:	ZF2-482
Issue Type:	Bug
Reporter:	akrabat
Created:	08/20/12
Assignee:	ralphschindler
Components:	Zend\Db

Description

When you try to iterate over a result set a second time, you get an exception:

This result is a forward only result set, calling rewind() after moving forward is not supported' in /www/zendframework/zf2/library/Zend/Db/Adapter/Driver/Pdo/Result.php on line 149

[zfbot]

(Originally posted by: maks3w on 08/28/12)

Fixed with PR #2247

[zfbot]

(Originally posted by: ricardomaia on 09/02/12)

I see the same error here. That issue persists, I believe.
I got a fresh copy from Git master repository, downloaded in september 2.

I made tests with a database like this:

1;"The Tell-Tale Heart"
2;"Little Women"
3;"The Velveteen Rabbit"
4;"Goodnight Moon"
5;"Bartholomew and the Oobleck"
6;"The Cat in the Hat"
7;"Dynamic Anatomy"
8;"2001: A Space Odyssey"
9;"Dune"
10;"The Shining"
11;"Franklin in the Dark"
12;"Practical PostgreSQL"
13;"Programming Python"
14;"Learning Python"
15;"Perl Cookbook"

$table = new BooksTable($adapter);
$resultSet = $table->fetchAll();

$current = $resultSet->current();

echo "After \$resultSet->current() <br />";
echo "Current key: {$resultSet->key()} <br />";
echo "Current value: {$current->title} <br />";

$resultSet->next();
$current = $resultSet->current();

echo "After \$resultSet->next() <br />";
echo "Current key: {$resultSet->key()} <br />";
echo "Current value: {$current->title} <br />";

$resultSet->rewind();
$current = $resultSet->current();

echo "After \$resultSet->rewind() <br />";
echo "Current key: {$resultSet->key()} <br />";
echo "Current value: {$current->title} <br />";

$resultSet->next();
$current = $resultSet->current();

echo "After \$resultSet->next() <br />";
echo "Current key: {$resultSet->key()} <br />";
echo "Current value: {$current->title} <br />";

$resultSet->next();
$current = $resultSet->current();

echo "After \$resultSet->next() <br />";
echo "Current key: {$resultSet->key()} <br />";
echo "Current value: {$current->title} <br />";

$resultSet->rewind();
$current = $resultSet->current();

echo "After \$resultSet->next() <br />";
echo "Current key: {$resultSet->key()} <br />";
echo "Current value: {$current->title} <br />";

  After $resultSet->current()Current key:  
  Current value: The Tell-Tale Heart 
  After $resultSet->next()Current key: 1 
  Current value: Little Women 
  After $resultSet->rewind()Current key: 0 
  Current value: The Velveteen Rabbit 
  After $resultSet->next()Current key: 1 
  Current value: Goodnight Moon 
  After $resultSet->next()Current key: 2 
  Current value: Bartholomew and the Oobleck 

  Fatal error: Uncaught exception 'Zend\Db\Adapter\Exception\RuntimeException' with message 'This result is a forward only result set, calling rewind() after moving forward is not supported' in /home/xxxxxxxxxxxxx/zend-framework/zf2/library/Zend/Db/Adapter/Driver/Pdo/Result.php on line 159

  Zend\Db\Adapter\Exception\RuntimeException: This result is a forward only result set, calling rewind() after moving forward is not supported in /home/xxxxxxxxxxxxx/zend-framework/zf2/library/Zend/Db/Adapter/Driver/Pdo/Result.php on line 159

  Call Stack:
      0.0003     349320   1. {main}() /var/www/zf-2.0.0rc7/index.php:0
      0.1675    1595740   2. Zend\Db\ResultSet\AbstractResultSet->rewind() /var/www/zf-2.0.0rc7/index.php:85
      0.1675    1595740   3. Zend\Db\Adapter\Driver\Pdo\Result->rewind() /home/xxxxxxxxxxxxx/zend-framework/zf2/library/Zend/Db/ResultSet/AbstractResultSet.php:228

At the first time the key value is NULL.
So, changes on keys almost working, but the values are moving forward only, aparently caused by cursor settings.

[zfbot]

(Originally posted by: Koen Pieters on 09/07/12)

I've did a try to fix this error when you use a buffer. That also still had an error in it.

You have to change your test code to:

$table = new BooksTable($adapter);
$resultSet = $table->fetchAll();

$resultSet->buffer();

$current = $resultSet->current();

pull request: #2314

[zfbot]

This issue was ported from the ZF2 Jira Issue Tracker at
http://framework.zend.com/issues/browse/ZF2-482

Known GitHub users mentioned in the original message or comment:
@akrabat, @ralphschindler, @Maks3w, @ricardomaia

[kirkmadera]

I get this same error with a \Zend\Db\ResultSet\ResultSet initialized with a PDO statement running ZF2 at commit 700e799. I am not sure how to mock the statement itself, but here is a test case that should fail with any database table.

    public function testResultSetDoubleIteration()
    {
        $dbName = 'mydb';
        $dbHost = 'localhost';
        $dbUsername = 'myusername';
        $dbPassword = 'mypassword';
        $table = 'mytable';

        $connection = new \Zend\Db\Adapter\Driver\Pdo\Connection(array(
            'driver' => 'pdo',
            'dsn' => 'mysql:dbname=' . $dbName . ';host=' . $dbHost,
            'database' => $dbName,
            'username' => $dbUsername,
            'password' => $dbPassword,
            'hostname' => $dbHost,
        ));
        $driver = new \Zend\Db\Adapter\Driver\Pdo\Pdo($connection);
        $adapter = new \Zend\Db\Adapter\Adapter($driver);

        $sql = new \Zend\Db\Sql\Sql($adapter, $table);
        $select = $sql->select();

        $statement = $sql->prepareStatementForSqlObject($select);

        $resultSet = new \Zend\Db\ResultSet\ResultSet();
        $resultSet->initialize($statement->execute());

        foreach ($resultSet as $result) {
        }

        foreach ($resultSet as $result) {
        }

    }

The error should be as follows:

1. ZendTest\Db\Adapter\AdapterTest::testForwardResultSet
   Zend\Db\Adapter\Exception\RuntimeException: This result is a forward only result set, calling rewind() after moving forward is not supported

/var/www/sites/example.com/vendor/ZF2/library/Zend/Db/Adapter/Driver/Pdo/Result.php:159
/var/www/sites/example.com/vendor/ZF2/library/Zend/Db/ResultSet/AbstractResultSet.php:228
/var/www/sites/example.com/vendor/ZF2/tests/ZendTest/Db/Adapter/AdapterTest.php:182

[ralphschindler]

Once you get a ResultSet try calling $resultSet->buffer() before iterating.
This was added at: 7842414

[ralphschindler]

Please reopen if its still an issue.

[Freeaqingme]

Reopening as per request of Jesse Taylor.

[j-r-t]

I'm still getting the same exception after updating to ZF2.1 - I have tried $resultSet->buffer(); eg:

in my table class:

$statement = $this->adapter->createStatement($sql);
$resultSet = $statement->execute();
$resultSet->buffer();
return $resultSet;

My table class is Extending AbstractTableGateway eg:

public function __construct(Adapter $apapter)
{
    $this->adapter = $apapter;

    $this->resultSetPrototype = new ResultSet();
    $this->resultSetPrototype->setArrayObjectPrototype(new SaleItem());

    $this->initialize();
}

config:

'service_manager' => array(
    'factories' => array(
        'ZXY\Model\SalesItemsTable' => function($sm) {
            $dbAdapter  = $sm->get('Zend\Db\Adapter\Adapter');
            $table      = new SalesItemsTable($dbAdapter);
            return $table;
        },
    ),

[ralphschindler]

What driver are you using, @jessertaylor?

[j-r-t]

hi @ralphschindler,

'driver' => 'pdo',

[ralphschindler]

pdo_?, but what platform, postgres?

[j-r-t]

mysql

[ThaDafinser]

Same problem here (also with MySQL).

\Zend\Db\Adapter\Driver\Pdo\Result

Haven't iterated through it....just giving the ResultSet directly to a view and then tried to do "foreach" loop.

[ThaDafinser]

            $sql = new \Zend\Db\Sql\Sql($this->getServiceLocator()->get('dbLisp'));
            $select = $sql->select();
            // $select = new \Zend\Db\Sql\Select();
            $select->from(array(
                'mpp' => 'macMachineProductOperation'
            ));
            $select->join(array(
                'pro' => 'macProduct'
            ), 'pro.proId = mpp.macProduct_proId');

            $select->order('pro.proNumber');
            $select->order('mpp.mppOperation');

            $where = new \Zend\Db\Sql\Where();
            $where->equalTo('mpp.macMachine_macId', (int) $dataMachine->macId);
            $where->equalTo('mpp.mppDeleted', 'n');
            $select->where($where);

            $statement = $sql->prepareStatementForSqlObject($select);
            $result = $statement->execute();

(Also tried with "buffer()"

[issambellemallem]

you can use

$resultSet->buffer();
$resultSet->next();

[ThaDafinser]

Same result:

This result is a forward only result set, calling rewind() after moving forward is not supported

Even if that would work....this is a little bit to much, to just iterate through a result in a view....

[ralphschindler]

The Result object cannot perform buffering if the underlying driver does not support it, in that case, what you want is Zend\Db\ResultSet (userland) buffering.

There are two ways to buffer result sets:

• pass in the option at adapter creation time:

            $dbconfig = array(
                'driver'   => 'Mysqli',
                'hostname' => 'localhost',
                'username' => 'developer',
                'password' => 'developer',
                'database' => 'zend_db_example',
                'options' => array('buffer_results' => true)
            );
            break;

• or, call ->buffer() on the ResultSet prototype (Result itself cannot do userland buffering), like:
  https://github.com/ralphschindler/Zend_Db-Examples/blob/master/example-21.php

[bridge-rabish]

getting same exception, from rewind() function of
\library\Zend\Db\Adapter\Driver\Pdo\Result.php

if ( $this->statementMode == self::STATEMENT_MODE_FORWARD && $this->position > 0) {
throw new Exception\RuntimeException(
'This result is a forward only result set, calling rewind() after moving forward is not supported'
);
}

tried both options for, buffer_results = true

[ThaDafinser]

The way ralph posted it works.

I've solved it "indirectly" over a fetch abstraction (like it was in ZF1).
https://gist.github.com/ThaDafinser/4952542

I just fetch the result complete and have it in an array.

[ralphschindler]

@bridge-rabish buffering is a feature of Zend\Db\ResultSet\ResultSet, which you wrap a driver Result with.

$rs = new Zend\Db\ResultSet\ResultSet();
$rs->initialize($zendDbPdoDriverResult);
$rs->buffer(); // if you must
foreach ($rs as $r) {}
foreach ($rs as $r) {}
foreach ($rs as $r) {}

I am closing this as its not an issue.

[lakshmana]

For paginatorAdapter, you should add buffering to the Iterator, eg.
$paginatorAdapter = new DbSelect(......);
$paginator = new Paginator($paginatorAdapter);

//buffering set for 'This result is a forward only result set, calling rewind() after moving forward is not supported '
$paginator -> getIterator()->buffer();

[mbn18]

@ralphschindler , I use the PDO/MySQL driver and buffer does not work.

I checked the code and noticed that the buffer method does nothing in the Pdo Result file:

public function buffer()
{
     return;
}

Is it something that should be implanted or I need to pass some kind of flag?