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''
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:
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 $$
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