Connector/C++ MySQL error code: 2014 , SQLState: HY000 and Commands out of sync error why?

Question

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 :

ERR: Commands out of sync; you can't run this comman d now (MySQL error code: 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 

Answer 1

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

From the mysql_use_result docs:

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.

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.

Answer 2

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

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:

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

Answer 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.

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.

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:

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 ***/
}