在mysqli中调用两个存储过程会导致“命令不同步"错误 [英] Calling two stored procedures in mysqli causes 'Commands out of sync' error

问题描述

问题上下文

我对所使用的大多数语言都比较陌生，因此我将逐步回过头来改进旧代码.我正在重新编写一些旧的PHP，并将硬编码的SQL查询(我知道)替换为对存储过程的调用.

I'm relatively new to most of the languages I'm working in, and I am gradually going back and improving old code. I'm re-writing some old PHP and replacing hardcoded SQL queries (I know) with calls to stored procedures.

页面中的代码按以下顺序组织-

The code in my pages is organised in the following order -

$RESULT_one = $connection->query( ... my first query ... ); 
$RESULT_two = $connection->query( ... my second query ... );

if(isset($RESULT_one) & isset($RESULT_two))
{
    // Generate a form using the results from the queries 
    // Loop through $RESULT_one to populate a combo box 
    $RESULT_one->free(); 
    // Loop through $RESULT_two to populate a combo box 
    $RESULT_two->free(); 
}
else 
{
    // Display an error to the user rather than displaying the form 
} 

问题

将第一个查询和第二个查询替换为存储过程中的CALL后，我收到错误#2014 - Commands out of sync; you can't run this command now.

After replacing my first query and my second query with CALLs to stored procedures, I receive the error #2014 - Commands out of sync; you can't run this command now.

为解决该问题而采取的步骤

• 有关堆栈溢出的海报是存在类似的问题，并且可接受的答案建议调用next_result()来解决他们的问题. 但是，我想在生成表单之前检查对数据库的两次调用是否成功，我不想像以前那样半生成它.

• This poster on Stack Overflow was having a similar problem and the accepted answer suggests calling next_result() which solves their issue. However, I want to check that both calls to the database were successful before generating my form, I do not want to half-generate it, as it were.

我看到了另一个问题在哪里，提问者也遇到了同样的问题，并且next_result()似乎是不可避免的，因为这是mysqli的工作方式"-为什么此方法适用于原始" SQL而不适用于存储过程?有什么区别?在生成表单之前，是否有一种有效的方法来检查两个查询是否成功?我想了解正在发生的事情以及行为改变的原因.

I see another question where the asker was having the same issue, and it seems that next_result() is unavoidable because "this is how mysqli works" - why does this work with 'raw' SQL but not with stored procedures? What is the difference? Is there an efficient way to check both queries were successful before generating my form? I'd like to understand what is going on and why the change in behavior.

我偶然发现了 mysqli_store_result() 的文档这似乎可能是有用的.我一直在阅读本文档的评论，但并不清楚.

I stumbled across documentation for mysqli_store_result() which seems like something which may potentially be useful. I've been reading the comments for this documentation but it isn't at all clear.

似乎解决方案是在我从推荐答案

调用多个存储过程时，除非采取措施防止发生错误，否则可能会发生错误Commands out of sync; you can't run this command now.

When calling multiple stored procedures, the error Commands out of sync; you can't run this command now can occur unless steps are taken to prevent it.

一种方法是在每次存储过程调用

One way is to call next_result() on the mysqli object after each stored procedure call, as described here.

这篇关于在mysqli中调用两个存储过程会导致“命令不同步"错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！