Multiple Result Sets from Stored Procedure in ZF2

If you are using stored procedures, then chances are that you will write one that returns multiple result sets at some point. This article shows how these can be accessed in Zend Framework 2. As a disclaimer, I should mention that during the research I did on the subject, I did not find a way to accomplish this while maintaining the level of abstraction that the Zend\Db namespace provides. Therefore, my solution does its work directly on PDO’s MySQL connection. It possibly works with other database vendors as well, but I have not verified this. Using PDO is required for the solution to work, though.

Below is an example of executing a stored procedure which returns three result sets. It is assumed that you have already set up your database adapter and have access to it. Otherwise, please see the documentation on this subject.


$driver = $this->dbAdapter->getDriver();
$connection = $driver->getConnection();

$result = $connection->execute('CALL sp_multiple_result_sets');
$statement = $result->getResource();

// Result set 1
$resultSet1 = $statement->fetchAll(\PDO::FETCH_OBJ);

foreach ($resultSet1 as $row) {
	// Using \PDO::FETCH_OBJ with the fetchAll() method gives us a stdClass with the columns as properties
	$something = $row->some_column;
}

// Result set 2
$statement->nextRowSet(); // Advance to the second result set
$resultSet2 = $statement->fetchAll(\PDO::FETCH_OBJ);

foreach ($resultSet2 as $row) {
	/* Do something */
}

// Result set 3
$statement->nextRowSet(); // Advance to the third result set
$resultSet3 = $statement->fetchAll(\PDO::FETCH_OBJ);

foreach ($resultSet3 as $row) {
	/* Do something */
}

If you know of a better and more generic way of doing this, please leave a comment below. Thank you.

5 Comments

  1. shubhalaxmi said:

    hiii can u tell me full procedure of store procedure .
    how to write and where we have save.
    also execution in zend framework2.

    April 4, 2013
    Reply
    • Andy said:

      Hello shubhalaxmi and thank you for your comment.

      At some point, I will write an article about stored procedures in MySQL, but until then, I suggest that you read this article. They will be saved to your MySQL database and you can use any popular database management tool to do this (e.g. PhpMyAdmin or MySQL WorkBench). These tools (or similar ones) will allow you to enter SQL statements and execute them directly, hence why you can simply enter your CREATE PROCEDURE … statements and execute them in order to create stored procedures. Then you can call them by entering CALL sp_some_name, exactly like in the example above.

      Execution in Zend Framework 2 can be done like above. However, if you do not have to access multiple result sets, then it is better to do it on the database adapter directly, because then you do not lose the level of abstraction, e.g. $result = $dbAdapter->query(‘CALL sp_some_name’);.

      If you are not sure how to access the database adapter, then please see an answer I wrote on StackOverflow.

      I hope it helped.

      April 4, 2013
      Reply
  2. Edgar said:

    Gracias me ha servido de mucho.

    June 9, 2013
    Reply
  3. [email protected] said:

    brilliant, just what the Dr ordered, stopped me beating around the bush with $db->query(‘CALL sp’); forward only, next, next next. How can you do this with doctrine2 ?

    June 21, 2013
    Reply
    • Bo Andersen said:

      Thank you for your comment. I am happy that you found this article useful. Unfortunately, I have not yet tried Doctrine2, so I am afraid that I cannot help you with that.

      June 22, 2013
      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *