连接器/ c++ MySQL错误代码:2014,SQLState: HY000和命令不同步错误为什么?

Hi im using Connector/C++ and executing simple 2 sql commands like this : the first select sql run ok but the second one cause this exception error :

你好，我使用连接器/ c++执行简单的2个sql命令:第一个选择sql运行正常，第二个选择sql会导致这个异常错误:

ERR: Commands out of sync; you can't run this comman d now (MySQL error code: 2014, SQLState: HY000 )

ERR:命令不同步;您现在不能运行这个comman d (MySQL错误代码:2014,SQLState: HY000)

here is the code :

这里是代码:

 //member of the class 
 ResultSet *temp_res;
 // in different method 
 m_driver = get_driver_instance();
 m_con = m_driver->connect(m_DBhost,m_User,m_Password); 
 m_con->setSchema(m_Database);

//here i excute the querys :
vector<string> query;
query.push_back("SELECT * FROM info_tbl");
query.push_back("INSERT INTO info_tbl (id,name,age)VALUES (0,foo,36)");
query.push_back("SELECT * FROM info_tbl");

ResultSet *res;
Statement *stmt;     
bool stmtVal = false;

    try{
        stmt = m_con->createStatement();
        for(size_t i = 0;i < querys.size();i++)
        {
            string query = querys.at(i);
            stmtVal = stmt->execute(query);

            if(!stmtVal)
            {

                string error_log ="sql statment:";
                error_log.append(query);
                error_log.append(" failed!");

                cout << error_log << endl;
                break;

            }
        }
        if(stmtVal)
        {
            if(returnSet)
            {
                    res = stmt->getResultSet();
                    temp_res = res;              
            }
        }



        delete stmt;
        //close connection to db 
        m_con->close();
} catch (sql::SQLException &e) {
    ......
}

UPDATE NEW CODE AS SUGGESTED ( NOT WORKING )

按照建议更新新代码(不工作)

for(size_t i = 0;i < querys.size();i++)
        {
            string query = querys.at(i);
            stmtVal = stmt->execute(query);
            if(stmtVal)
            {
                if(returnSet)
                {
                    if(stmt->getResultSet()->rowsCount() > 0)
                    {
                        res = stmt->getResultSet();
                        temp_res = res;              
                    }
                    else
                    {       

                        delete res;
                    }
                }
                else 
                {
                    delete res;
                }
            }
            if(!stmtVal)
            {

                string error_log ="sql statment:";
                error_log.append(query);
                error_log.append(" failed!");

                cout << error_log << endl;
                break;

            }
        }

this is my simple table :

这是我的简单表:

Column  Type        Null     
id          int(10)     No           
name    varchar(255)    No           
age     int(10)     No

3 个解决方案

#1

You can't have more than one active query on a connection at a time.

一个连接不能同时有多个活动查询。

From the mysql_use_result docs:

从mysql_use_result文档:

You may not use mysql_data_seek(), mysql_row_seek(), mysql_row_tell(), mysql_num_rows(), or mysql_affected_rows() with a result returned from mysql_use_result(), nor may you issue other queries until mysql_use_result() has finished.

您不能使用mysql_data_seek()、mysql_row_seek()、mysql_row_tell()、mysql_num_rows()或mysql_affected_rows()返回mysql_use_result()的结果，也不能发出其他查询，直到mysql_use_result()结束。

That's not exactly what you're using, but the problem is the same - you'll need to finish processing the first ResultSet and clean it up before you can issue any other query on that connection.

这并不是您正在使用的，但是问题是相同的——您需要完成对第一个ResultSet的处理，并在对该连接发出任何其他查询之前对其进行清理。

#2

I was getting the same error until I changed my code to how MySQL says to do it.
Old code:

我得到了同样的错误，直到我把代码改成MySQL的方式。旧的代码:

res.reset(stmt->getResultSet());
if (res->next())
{
    vret.push_back(res->getDouble("VolumeEntered"));
    vret.push_back(res->getDouble("VolumeDispensed"));
    vret.push_back(res->getDouble("Balance"));
}

new code w/o error:

新代码w / o错误:

do
{
    res.reset(stmt->getResultSet());
    while(res->next()) 
    {
        vret.push_back(res->getDouble("VolumeEntered"));
        vret.push_back(res->getDouble("VolumeDispensed"));
        vret.push_back(res->getDouble("Balance"));
    }
} while (stmt->getMoreResults());

"do while" must always be used with Stored Procedures' returns

“do while”必须始终与存储过程的返回一起使用。

#3

I ran into this problem also and took me a little while to figure it out. I had even set the "CLIENT_MULTI_RESULTS" and "CLIENT_MULTI_STATEMENTS" with no avail.

我也遇到了这个问题，花了我一点时间来解决它。我甚至还设置了“CLIENT_MULTI_RESULTS”和“CLIENT_MULTI_STATEMENTS”，但都没有用。

What is happening is MySql thinks that there is another result set waiting to be read from the first call to the Query. Then if you try to run another Query, MySql thinks that it still has a ResultSet from last time and sends the "Out of Sync" Error.

正在发生的是，MySql认为有另一个结果集等待从第一个调用到查询读取。然后，如果您尝试运行另一个查询，MySql认为它仍然有一个上次的ResultSet，并发送“不同步”错误。

This looks like it might be a C++ Connector issue but I have found a workaround and wanted to post it in case anyone else is having this same issue:

这看起来可能是一个c++连接器的问题，但我找到了一个解决方案，我想把它发布出来，以防其他人也有同样的问题:

sql::PreparedStatement *sqlPrepStmt;
sql::ResultSet *sqlResult;
int id;
std::string name;

try {

    //Build the Query String
    sqlStr = "CALL my_routine(?,?)";

    //Get the Result
    sqlPrepStmt = this->sqlConn->prepareStatement(sqlStr);
    sqlPrepStmt->setInt(1, itemID);
    sqlPrepStmt->setInt(2, groupId);
    sqlPrepStmt->executeUpdate();

    sqlResult = sqlPrepStmt->getResultSet();

    //Get the Results
    while (sqlResult->next()) {
        id = sqlResult->getInt("id");
        name = sqlResult->getString("name");
    }

    //Workaround: Makes sure there are no more ResultSets
    while (sqlPrepStmt->getMoreResults()) {
        sqlResult = sqlPrepStmt->getResultSet();
    }

    sqlResult->close();
    sqlPrepStmt->close();

    delete sqlResult;
    delete sqlPrepStmt;
}
catch (sql::SQLException &e) {
    /*** Handle Exception ***/
}

#1