Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while there are pending result sets

Question

$query = "CALL GetAllCategories()";
$stmt = $pdo->prepare($query);
$stmt->execute();
$categories = $stmt->fetchAll();

I use this code to get categories from DB using stored procedures, no problems with that, everything works fine.

This is GetAllCategories() procedures:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetAllCategories`()
BEGIN
    SELECT
        id, name
    FROM
        categories ;
END$$
DELIMITER ;

After that, I used regular SQL statement to get posts ( for testing, I will use stored procedure for that )

this is the query to get posts:

SELECT
    `p`.`id`,
    `u`.`name` AS `author`,
    `post_title`,
    `post_date`,
    `post_img`,
    `post_content`
FROM
    `posts` `p`
JOIN `users` `u` ON
    `p`.`author_id` = `u`.`id`;

From here the problem occurred:

error image

this error appears:

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while there are pending result sets. Consider unsetting the previous PDOStatement or calling PDOStatement::closeCursor() in C:\xampp\htdocs\cms\index.php:17 Stack trace: #0 C:\xampp\htdocs\cms\index.php(17): PDO->prepare('SELECT\n `p`....') #1 {main} thrown in C:\xampp\htdocs\cms\index.php on line 17

When trying several attempts, I change the GetAllCategories() stored procedure to reqular query statement:

$query = "SELECT * FROM `categories`";

The problem disappeared

Can anyone explain why this happed with stored procedures?

The last thing, After this issue, another problem appeared in the posts table when browsing it from PHPMyAdmin

posts table browsing error

This shouldn't happen if you use `fetchAll()`, as that's generally the way to *fix* this problem. — Barmar, May 21 '21 at 01:59
See https://stackoverflow.com/questions/17434102/causes-of-mysql-error-2014-cannot-execute-queries-while-other-unbuffered-queries for various suggestions — Barmar, May 21 '21 at 02:00
So maybe because you're trying to use procedure with regular, So you should use all of your queries from one type. — Mahmoud Mohamed Ramadan, May 21 '21 at 06:45

score 0 · Accepted Answer · answered May 21 '21 at 12:24

0

I used $stmt->closeCursor(); after fetching data from GetAllCategories() stored procedure to solve this error.

$query = "CALL GetAllCategories()";
$stmt = $pdo->prepare($query);
$stmt->execute();
$categories = $stmt->fetchAll();
$stmt->closeCursor();

answered May 21 '21 at 12:24

Mohamed Fadl

1,303
2
8
10

nasih a.kareem · Answer 2 · 2021-05-25T06:32:12.867

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while there are pending result sets. Consider unsetting the previous PDOStatement

This error is always happens while there were two executions at a same time for example:

CASE 1

if(!$query->execute())

die("SQL FAILED");

else

$query->execute(); // **DELETE THIS ONE**

Solution: Second execution is no needed, remove second one, the first execution is already executed.

if the first case is not working, USE this Solution call this function after $query=$handler->prepare($sql);

$query->closeCursor();

I hope this Solution is helpful

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while there are pending result sets

2 Answers2