我已经设置了
$GDBObj->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);连续调用两次
$pdo->query("select xxxxx");
$pdo->query("select yyyyyy");
没有问题但是如果调用存储过程
$pdo->query("call xxxx");
$pdo->query("select yyyyy");
就会报错:
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.'
$GDBObj->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);连续调用两次
$pdo->query("select xxxxx");
$pdo->query("select yyyyyy");
没有问题但是如果调用存储过程
$pdo->query("call xxxx");
$pdo->query("select yyyyy");
就会报错:
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.'
解决方案 »
- 谁知道怎么吧文本框输入的数据放到 var html2变量里
- php先删除再重新创建某文件时文件的创建日期不更新的问题
- radiobutton 如何使用
- php图像处理问题
- 请问面试时要做个什么样的网站
- PHP+MYSQL的疑难问题请教高手,谢谢
- 谁知道像这种后台制作是用什么插件的
- 100分求php5的简单配置问题
- 我的脚本都是放在服务器htdoc目录下的,太不安全了,请问有什么方法能不让别人看到你的代码?
- 怎么样有效的进行Email地址验证???Email地址必须是网络中存在的!!!
- php 执行exec/system函数 的问题
- 我想找用Zend FrameWork开发的源码来学习.请问可以哪个网站下载?
$Stmt = $pdo->query("call xxxx()");
$Info = $Stmt->fetchAll();
......
$Stmt2 = $pdo->query("select yyyyy");
$Stmt2->fetchAll();还是报同样的错
MYSQL存储过程返回的是多结果集,进行遍历时必须释放前一个结果集才能访问下一个,参考:PHP执行MYSQL存储过程报错:Commands out of sync; you can't run this command now 在PHP同一事物里调用MYSQL的存储过程后再次执行另外的一个或多个命令(或者在同一事物里执行多个存储过程),如果使用mysqli的query方法获得结果,将获得一个错误:Commands out of sync; you can't run this command now sss 先给出代码:存储过程: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
PHP代码:Code1
<?php
$mysqli = new mysqli("localhost", "root", "sbqcel", "test");if (mysqli_connect_errno())
{
printf("Connect failed: %s\n", 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 <br />", $row[0]);
}
$result->close();
mysqli_free_result($result);echo 'result2:<br />';
if ($result2 = $mysqli->query("select val from tb1;"))
{
while ($row = $result2->fetch_row())
{
printf ("%s <br />", $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方法,遍历所有的结果集并释放掉掉。代码如下:Code2
<?php
$mysqli = new mysqli("localhost", "root", "sbqcel", "test");if (mysqli_connect_errno())
{
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
echo 'result1:<br />';
$mysqli->autocommit(FALSE);
if ($mysqli->multi_query("call test1();"))
{
do {
if ($result = $mysqli->store_result()) {
while ($row = $result->fetch_row()) {
printf("%s\n", $row[0]);
}
$result->close();
}
} while ($mysqli->next_result());
}
$mysqli->commit();
echo "<br />";
echo "result2:<br />";
if ($result2 = $mysqli->query("select val from tb1;"))
{
while ($row = $result2->fetch_row()) {
printf ("%s <br />", $row[0]);
}
$result2->close();
}
else
{
echo $mysqli->error;
}
$mysqli->close();
?>