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.
Here is what you will learn:
- Understand the theory of Zend Framework in details
- How to implement an enterprise-ready architecture
- Develop professional applications in Zend Framework
- Proficiently work with databases in Zend Framework
- ... and much more!
5 comments on »Multiple Result Sets from Stored Procedure in ZF2«
hiii can u tell me full procedure of store procedure .
how to write and where we have save.
also execution in zend framework2.
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.
Gracias me ha servido de mucho.
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 ?
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.