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

Total
1
Shares
$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:xampphtdocscmsindex.php:17 Stack trace: #0 C:xampphtdocscmsindex.php(17): PDO->prepare('SELECTn `p`....') #1 {main} thrown in C:xampphtdocscmsindex.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


Solution

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();
Leave a Reply

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