mysql存储过程错误命令不同步；您现在不能运行此命令 [英] mysql stored procedure error Commands out of sync; you can't run this command now

问题描述

我有一个存储过程，可以从PHP调用该存储过程，该存储过程在运行Maria DB的开发版本上运行良好.我转移到正在运行mysql(5.5.53)的生产环境中，命令不同步；您无法立即运行此命令"错误.我尝试过建议以确保在每次查询后都关闭连接.我也尝试过$ stmt-> store_result(); & $ stmt-> free_result();.如果我尝试$ stmt-next_result();我没有得到这个不同步的错误，但是我的脚本存在循环并且没有得到其他结果.

I have a stored procedure which I call from PHP which works fine on my dev version which is running Maria DB. I transferred to production which is running mysql (5.5.53) a 'Commands out of sync; you can't run this command now' error. I have tried suggestions to make sure the connection is closed after each query. I have also tried $stmt->store_result(); & $stmt->free_result();. If I try $stmt-next_result(); I do not get this out of sync error, but my script exists the loop and I do not get any other results.

存储过程:

CREATE DEFINER=`root`@`localhost` PROCEDURE `test3`(IN `Input_Value` INT, OUT `Out_val` VARCHAR(150))
BEGIN
    SET @sql = 'SELECT sample_name FROM sample';
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
END

PHP: `

PHP: `

foreach($locations as $location_name){
    echo $location_name.'<br>';
    $sp_query = "CALL test3(?,@Out_val)";
    $stmt = $dbc->prepare($sp_query);
    if(!$stmt){
        echo mysqli_error($dbc);
    }
    $stmt->bind_param('i',$view);

    if ($stmt->execute()){
        $params = array();
        $meta = $stmt->result_metadata();
        print_r($meta); 

        while ($field = $meta->fetch_field()){
            $params[] = &$row[$field->name]; 
        }
        print_r($params); 
        call_user_func_array(array($stmt, 'bind_result'), $params); 
        $header_ct = 0; 

        $labels = '';
        while ($stmt->fetch()) {
            foreach($row as $key => $value){
                $key = htmlspecialchars($key);
                //echo "Key:".$key."<br>";
                //echo "Value:".$value."<br>";
            }
        }
    }else{
        echo mysqli_error($dbc);
        echo "not working";
    }
    $stmt->store_result();
    //$stmt->free_result();
    $stmt->close();                 
}           

?>`

推荐答案

在只需用$select_stmt->close();分隔查询即可将它们拆分(不是同时进行的，而是过程性的. 有关此详情，请参见

In Just seperate your queries with $select_stmt->close(); to split them (not simultaneous but procedural. For more on this see

如果您使用的是codeigniter Framework， 将以下代码添加到/system/database/drivers/mysqli/mysqli_result.php 用于代码点火器

If you are using codeigniter Framework, add following code into /system/database/drivers/mysqli/mysqli_result.php For codeigniter

function next_result()
 {
     if (is_object($this->conn_id))
     {
         return mysqli_next_result($this->conn_id);
     }
}

然后在调用存储过程时进入模型

then in model when you call Stored Procedure

$query    = $this->db->query("CALL test()");
$res      = $query->result();

//add this two line 
$query->next_result(); 
$query->free_result(); 
//end of new code

return $res;

这篇关于mysql存储过程错误命令不同步；您现在不能运行此命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！