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, Aug 20 '17 at 14:46
@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, Aug 20 '17 at 14:49
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, Aug 20 '17 at 15:00
@Solarflare Yes, my procedure returns multiple rows. What you mean exactly by *"You have to go through all of them first"* ? — stack, Aug 20 '17 at 15:00
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, Aug 20 '17 at 15:12
@Solarflare No, There is just one `SELECT` statement in each procedure. — stack, Aug 20 '17 at 15:12

score 2 · Accepted Answer · answered Aug 20 '17 at 16:51

2

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!).

answered Aug 20 '17 at 16:51

Bill Karwin

538,548
86
673
828

Oh .. interesting .. I just have added `$sth->nextRowset()` after the first `fetchAll()` and my code works. – stack Aug 20 '17 at 17:07
Also I'm curious to know, what are you doing now? Are you a programmer and work in a company? Or do you have your own company? I really like to visit an experienced programmer. – stack Aug 20 '17 at 17:15
I am a programmer. I work in a company. I'm just like most of the people on Stack Overflow. – Bill Karwin Aug 20 '17 at 17:23
I see. Nice! Just for my information, have you think about starting your on business so far? I guess someone in your lever can simply manage a nice project and earn lots of money and the fame. You know, I'm trying to implement my own project *(I've done the most of it actually)* and I like to know why others don't do that? *(specially someone in your level)* – stack Aug 20 '17 at 17:34
I want to do software engineering, I don't want to run a business. – Bill Karwin Aug 21 '17 at 04:15
Wow. That nextRowset() method returns '1', and subsequent calls crash the PHP binary on my virtual web hosting platform.... calling closeCursor() also returns '1' but subsequent execute() calls fail with the unbuffered query error, but calling closeCursor() and then nextRowset() crashes the binary. Wow. So yeah, no stored procedures that return resultsets. Just going to do it a different way since this is clearly super brittle. – Jon Marnock Apr 29 '19 at 07:04
Frankly, I never use stored procedures with MySQL, regardless of the client interface. – Bill Karwin Apr 29 '19 at 13:50

score 0 · Answer 2 · answered Sep 04 '21 at 06:24

Well, in the case of Stored Procedure, the $result object is more complex. In the case of applying MVC, multiple source can load multiple call statements, and the cursor from the first $result can be in the next to the last element of the array (In thise case, the Result has no more elements, in other way, is set to false). For me, worked clear all stored result.

Code:

function clearStoredResults($mysqli_link){ #------------------------------------
  while($mysqli_link->next_result()){ if($l_result = $mysqli_link->store_result()){ $l_result->free(); } } }

And finally:

function execQuery($sql){
    global $conexion;
    try {    
      $result=$conexion->query($sql);
      while($row = $result->fetch_assoc()){
        $new_array[] = $row; // Inside while loop
      }
    return $new_array; 
    } catch (Exception $e) {
       echo $e->getMessage();
    }
    finally {
       clearStoredResults($conexion);
   }
 }

I recomend to explore with care. You can use administrative tools form mysql or mariabd and php, to explore the effects.

How can I execute multiple procedures continuously?

2 Answers2

Code:

And finally:

Linked