错误出现在调用了存储过程之后继续调用了返回结果集的命令。
这个错误的官方解释为:
If you get Commands out of sync; you can’t run this command now in your client code, you are calling client functions in the wrong order.
This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result().
It can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() in between.
执行了一个存储过程, 需要将结果集取完,然后才能调用下一个返回结果集的命令。
示例如下(Connector/C++中的处理办法):
存储过程:
BEGIN
select * from gamebase where Accountid = AccID;
END
代码片段1:
newConnection.SetStatement("CALL LoadGameBase(1)");
newConnection.Execute();
auto resultSet = newConnection.GetResultSet();
while (resultSet.Next())
{
int accountID = resultSet.GetInt(1);
std::string accountName = resultSet.GetString(2);
double curGold = resultSet.GetDouble(3);
std::cout << accountID << " " << accountName << " " << curGold << std::endl;
}
上面这种执行方式就会报错为"Commands out of sync…"
正确的方式为:
代码片段2:
newConnection.SetStatement("CALL LoadGameBase(1)");
newConnection.Execute();
do
{
auto resultSet = newConnection.GetResultSet();
while (resultSet.Next())
{
int accountID = resultSet.GetInt(1);
std::string accountName = resultSet.GetString(2);
double curGold = resultSet.GetDouble(3);
std::cout << accountID << " " << accountName << " " << curGold << std::endl;
}
}
while (newConnection.GetMoreResults());
使用成员函数GetMoreResults遍历结果集。
注意上面GetMoreResults的调用方式,
只能使用do、while,这是因为调用GetMoreResults之前,"结果集指针"就已经指向了第一个结果集,
调用之后,将会指向下一个。
下面是错误示例:
代码片段3:
newConnection.SetStatement("CALL LoadGameBase(1)");
newConnection.Execute();
while (newConnection.GetMoreResults())
{
auto resultSet = newConnection.GetResultSet();
while (resultSet.Next())
{
int accountID = resultSet.GetInt(1);
std::string accountName = resultSet.GetString(2);
double curGold = resultSet.GetDouble(3);
std::cout << accountID << " " << accountName << " " << curGold << std::endl;
}
}
执行后不会报错,但也不会得到任何数据。
还有一点需要注意:
如果执行完存储过程后,完全不取结果集,是可以直接执行下一个返回结果集的语句。
可以理解为:存储过程返回的结果集,要么不取,要么取完。