Multi Result Set From Stored Procedure - Mysql Error Cannot execute queries while other unbuffered queries are active.

I looked for away to get all the resultset that a procedure is return from my mysql database and did it in this way:


 $commande = Yii::$app->db->createCommand("call heure(:pid,:pij)");

            $commande->bindValue(':pij',$pij);

            $commande->bindValue(':pid',Yii::$app->user->isGuest?null:Yii::$app->user->identity->getId());

            $resultat = $commande->query();


                return $this->render('partialo', ['first'=>$resultat->getRowCount(),'second'=>$resultat->nextResult(),'third'=>$resultat->nextResult(),

                    'forth'=>$resultat->nextResult(),'fifth'=>$resultat->nextResult()]);

I want to send the each resultset separatly in the view that’s why I am calling


 $resultat->nextResult();

But when I execute the action of the controller that has the above codes; I am getting this error down:


Exception (Database Exception) 'yii\db\Exception' with message 'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

The SQL being executed was: SELECT `t1`.`message` AS `message`, `t2`.`translation` AS `translation` FROM `source_message` `t1`, `message` `t2` WHERE t1.id = t2.id AND t1.category = 'app' AND t2.language = 'en'' 

How can I solve this?

have you get any solution ?




$resultat->nextResult();



This will return either true on success or false on failure, this function will advance the pointer to the next resultset, do not read data.

Then you should use




$rowSet=$resultat->readAll();



To read all the rows of the statement.

Then about mysql error. The MySQL client protocol doesn’t allow more than one query to be open. Normally query are buffered in the mysql client library to avoid php memory overflow in case of large result.

In this case the statement is still open.

the solution is to assign the single statement to a var and close the statement, something like:




$resultat->nextResult();

$rowSet_1=$resultat->readAll();


$resultat->nextResult();

$rowSet_2=$resultat->readAll();


$resultat->nextResult();

$rowSet_3=$resultat->readAll();


$resultat->close();



I found solution link here.

Might help others,


Method 1: 


        $command = Yii::app()->db->createCommand("CALL sp_getstudentdetails('view')");

        $resultSet = $command->query();


        // retrieving all rows at once in a single array

        $array1 = $resultSet->readAll();

        var_dump($array1); //Dumps information about a variable

        

        $resultSet->nextResult(); //Return if there is another result 


        // retrieving all rows at once in a single array

        $array2 = $resultSet->readAll();

        var_dump($array2); //Dumps information about a variable




Method 2: 


        $command = Yii::app()->db->createCommand("CALL sp_getstudentdetails('view')");

        $resultSet = $command->query();

        

        // calling read() repeatedly until it returns false

        while (($row = $resultSet->read()) !== false) {

            var_dump($row);

        }


        $resultSet->nextResult();


        while (($row = $resultSet->read()) !== false) {

            var_dump($row);

        }




Mysql Stored Procedure:


drop procedure if exists sp_getstudentdetails;

DELIMITER $$


CREATE PROCEDURE sp_getstudentdetails(

in  p_mode varchar(100))

BEGIN


IF p_mode = 'view' THEN

   select * from student; /* first select query  */

   select * from studentdetails;  /* second select query */

END IF;


END$$


DELIMITER $$

You still miss the




$resultSet->close();



If for any reason you didn’t empty the buffer you will get the error of the original post.

How do I change the prefix on all generated stored procs from sp to dsm. There is a performance consideration on SQL2K when you prefix a stored proc with sp

https://stackoverflow.com/questions/1098585/mass-renaming-of-tables-and-stored-procedures

Then in the PHP code is just matter of search and replace