1. 云栖社区>
  2. PHP教程>
  3. 正文

PHP执行MYSQL存储过程报错:Commands out of sync; you can't run this comma

作者:用户 来源:互联网 时间:2017-12-01 10:49:13

PHP执行MYSQL存储过程报错:Commands out of sync; you can't run this comma - 摘要: 本文讲的是PHP执行MYSQL存储过程报错:Commands out of sync; you can't run this comma,   CREATE PROCEDURE test1()begin drop table if exists tb1; create table tb1( val int not null )engine = innoDB; insert i


 


CREATE PROCEDURE test1()
begin
drop table if exists tb1;
create table tb1
(
val int not null
)engine = innoDB;
insert into tb1(val) values(1),(2),(3);
select * from tb1;
end
 

 


$mysqli = new mysqli("localhost","root","sbqcel","test");
if (mysqli_connect_errno())
{
printf("Connect failed: %sn", mysqli_connect_error());
exit();
}
$result = null;
$mysqli->autocommit(FALSE);
if(!($result = $mysqli->query("call test1();")))
{
echo mysqli_error($link);
$mysqli->rollback();
}
$mysqli->commit();
print 'Result1:';
while ($row = $result->fetch_row())
{
printf ("%s
", $row[0]);
}
$result->close();
mysqli_free_result($result);
echo 'result2:
';
if ($result2 = $mysqli->query("select val from tb1;"))
{
while ($row = $result2->fetch_row())
{
printf ("%s
", $row[0]);
}
$result2->close();
}
else
{
echo $mysqli->error;
}
mysqli_free_result($result2);
mysqli_close($link);

 执行上面的代码后就会出现上面的错误,消息说明MYSQL数据库认为是这一个错误的命令执行顺序。原因在于MYSQL的存储过程执行完成后除了返回实际结果集还会返回存储过程执行的转态


,而上面的代码仅处理了第一个结果集,第二个结果集并没有被释放掉。


When a stored procedure returns a resultset, MySQL returns at least two resultsets: first for the SELECT CALL inside the stored procedure. 2ndfor the call of the stored procedure itself


 (2nd usually is only an OK or ERR packet).


要解决这个问题,需要用mysqli的multi_query方法,遍历所有的结果集并释放掉掉。代码如下:


 


<?php
$mysqli = new mysqli("localhost","root","sbqcel","test");
$mysqli->multi_query("set names 'utf8'");
if (mysqli_connect_errno())
{
printf("Connect failed: %sn", mysqli_connect_error());
exit();
}
echo 'result1:
';
$mysqli->autocommit(FALSE);
if ($mysqli->multi_query("call test1();"))
{
do {
if ($result = $mysqli->store_result()) {
while ($row = $result->fetch_row()) {
printf("%sn", $row[0]);
}
$result->close();
}
} while ($mysqli->next_result());
}
$mysqli->commit();
echo"
";
echo"result2:
";
if ($result2 = $mysqli->query("select val from tb1;"))
{
while ($row = $result2->fetch_row()) {
printf ("%s
", $row[0]);
}
$result2->close();
}
else
{
echo $mysqli->error;
}
$mysqli->close();
?>
 

 

以上是PHP执行MYSQL存储过程报错:Commands out of sync; you can't run this comma的全部内容,在云栖社区的博客、问答、公众号、人物、课程等栏目也有其他相关内容,欢迎继续使用右上角搜索按钮进行搜索,以便于您获取更多的相关知识。