Do we need AbstractResultSet::fetchAll()

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Do we need AbstractResultSet::fetchAll()

Greg Roach
Database:
=========

DELIMITER //
CREATE TABLE foo (n INTEGER) //
INSERT INTO foo (n) VALUES (1),(2),(3),(4),(5),(6),(7),(8) //
CREATE PROCEDURE bar (IN m INTEGER)
BEGIN
        SELECT n FROM foo WHERE n MOD m = 0;
END //

Controller:
===========

public function someAction() {
        $db = $this->getServiceLocator()->get('db');
        return new ViewModel(array(
                'mod2' => $db->query("CALL bar(2)", array()),
                'mod3' => $db->query("CALL bar(3)", array()),
        ));
}

View:
=====

<? foreach ($mod2 as $data) echo $data->n; ?>
<? foreach ($mod3 as $data) echo $data->n; ?>

This gives the error "SQLSTATE[HY000]: General error: 2014 Cannot
execute queries while other unbuffered queries are active."

It can be resolved by fetching the rows from the first query before
calling the second.
However, (unless I've missed something), the only way to fetch the
rows is to call the
toArray() function.  e.g.

public function someAction() {
        $db = $this->getServiceLocator()->get('db');
        return new ViewModel(array(
                'mod2' => $db->query("CALL bar(2)", array())->toArray(),
                'mod3' => $db->query("CALL bar(3)", array())->toArray(),
        ));
}

But this requires changing the view from object-notation to
array-notation (yuck).

<? foreach ($mod2 as $data) echo $data['n']; ?>
<? foreach ($mod3 as $data) echo $data['n']; ?>

It shouldn't be necessary to convert each row to an array.  We need a function
like AbstractResultSet::toArray() but which doesn't do the conversion.  Perhaps
AbstractResultSet::fetchAll()

    public function fetchAll()
    {
        return $this->toArray();
    }

With this, the controller can become

public function someAction() {
        $db = $this->getServiceLocator()->get('db');
        return new ViewModel(array(
                'mod2' => $db->query("CALL bar(2)", array())->fetchAll(),
                'mod3' => $db->query("CALL bar(3)", array())->fetchAll(),
        ));
}

--
List: [hidden email]
Info: http://framework.zend.com/archives
Unsubscribe: [hidden email]