php - 无法在PDO中调用mysql存储过程

Question

我试图使用 php 和 PDO 依次调用两个存储过程..但是当我尝试这样做时，它会返回一个错误

 Fatal error: Uncaught exception 'PDOException' 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.'

正如您所见，我也已经使用了 MYSQL_ATTR_USE_BUFFERED_QUERY 属性，但我仍然收到错误。

$conn = new PDO("mysql:host=$dbConfig->host;dbname=".$dbConfig->name, $dbConfig->username, $dbConfig->password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $conn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

    $tables = array();
    $table_count = $conn->prepare("SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = 'milepostdb' AND table_name LIKE 'table_%' ");
    $table_count->execute();

    while ($data = $table_count->fetch(PDO::FETCH_ASSOC)) {

        $table = $data["TABLE_NAME"];

        //$result = $conn->prepare("SELECT * FROM   $table WHERE time > DATE_ADD(NOW(), INTERVAL -1 WEEK)");//this works perfectly
        $result = $conn->prepare("CALL sp_project_time_interval('$table')");

        $result->execute();// place where the error triggers

        $count = $result->rowCount();
        if($count>0){
            $exc = $conn->prepare("CALL sp_weekly_project_report('$table')");
            $exc->execute();
            while($finalRes = $exc->fetch(PDO::FETCH_ASSOC))
             {
                        $tables[] = $finalRes;
             }
        }

    }

Answer 1

您的结果集在第一个 SELECT 语句中仍然处于打开状态。在运行更多查询之前，您需要关闭游标。请注意，执行 fetchAll() 会自动为您关闭游标。

$table_count = $conn->prepare("SELECT .......");
$table_count->execute();
$resultset = $table_count->fetchAll(PDO::FETCH_ASSOC);

foreach($resultset as $data) {
    $result = $conn->prepare("CALL .......");
    $result->execute(); 
    $resultset2 = $table_count->fetchAll(PDO::FETCH_ASSOC);
}