原文地址：http://www.pczpg.com/a/2010/0507/7815.html
在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代碼:
<?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方法，遍曆所有的結果集並釋放掉掉。代碼如下：
<?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();
?>
在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方法，遍曆所有的結果集並釋放掉掉。代碼如下：
<?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();
?>
