How can I execute multiple procedures continuously?

Question

Here is my code:

$query = "CALL user_top_categories_score(?, 'ALL', 0, 1)";
$sth = $this->dbh->prepare($query);
$sth->execute([$user_id]);
$category = $sth->fetchAll(PDO::FETCH_ASSOC);

$query = "CALL user_top_tags_score(?, 'ALL', 0, 3)";
$sth = $this->dbh->prepare($query);
$sth->execute([$user_id]);
$tags = $sth->fetchAll(PDO::FETCH_ASSOC);

It throws this error:

Fatal error: Uncaught PDOException: 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. in C:\xampp\htdocs\myweb\others\user.php:71 Stack trace: #0 C:\xampp\htdocs\myweb\others\user.php(71): PDO->prepare('CALL user_top_t...') #1 C:\xampp\htdocs\myweb\application\other.php(24): user->index() #2 C:\xampp\htdocs\myweb\index.php(152): require_once('C:\xampp\htdocs...') #3 {main} thrown in C:\xampp\htdocs\myweb\others\user.php on line 71

Also I've used closeCursor() right after fetchAll(), based on this solution. But sadly it throws a new error:

Warning: Packets out of order. Expected 1 received 9. Packet size=7 in C:\xampp\htdocs\myweb\others\user.php on line 72

Warning: PDO::prepare(): MySQL server has gone away in C:\xampp\htdocs\myweb\others\user.php on line 72

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away in C:\xampp\htdocs\myweb\others\user.php:72 Stack trace: #0 C:\xampp\htdocs\myweb\others\user.php(72): PDO->prepare('CALL user_top_t...') #1 C:\xampp\htdocs\myweb\application\other.php(24): user->index() #2 C:\xampp\htdocs\myweb\index.php(152): require_once('C:\xampp\htdocs...') #3 {main} thrown in C:\xampp\htdocs\myweb\others\user.php on line 72

Any idea how can I fix the problem?

Noted1: Each of queries above work separately. I mean, when I call a single procedure, it works as well.

Noted2: Each procedure returns a result set. I mean there is a SELECT statement in those procedures.

Did you try to run this procedure directly on database ? If no, then try and send feedback here. — Artur Poniedziałek
@ArturPoniedziałek I just can run them one by one directly in the database. When I execute both in one query, it throws #2014 - Commands out of sync; you can't run this command now. — stack
Does your procedures return more than one resultset (e.g., is there more than one select in your procedure)? You have to go through all of them first before you can execute the next query. — Solarflare
@Solarflare Yes, my procedure returns multiple rows. What you mean exactly by "You have to go through all of them first" ? — stack
No I meant multiple result sets, not multiple rows. If you have multiple sets (e.g. 2 selects in your procedure), you have to fetchall once for every set (and go to the next set with nextRowset()). — Solarflare

Bill Karwin Bill Karwin · Accepted Answer · 2017-08-20T16:51:06

You may have just one SELECT in your procedure, but the API doesn't know that. It has to assume you might have multiple result sets returned from your procedure, therefore fetchAll() alone doesn't close the cursor.

You have to keep calling nextRowset() until it has returned a false result, indicating that all results have been returned from this statement.

See also my answer to How can I use a stored procedure in a MySql database with Zend Framework? (I answered this in 2009!).

How can I execute multiple procedures continuously?

1 Answers