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 

Scuta

Are there any TRIGGERs on the table? – Dorset

no very simple table for testing – Scuta

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.

Transilluminate

ok but i don't get any result set from the first select . also what is the proper way to clean ResultSet if i don't use std::auto_ptr ? – Scuta

Whether you get any data or not, you need to call res->next() until it returns false (which can be on the very first call), or delete the ResultSet (with delete res;). – Transilluminate

when i try to delete res it gives me Access violation writing location i will update my code to show you – Scuta

In your updated code, if returnSet is false or no rows have been returned, you're deleteing a pointer you have not initialized (res is not set in both those cases). Don't do that. – Transilluminate

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 without 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());

Friedlander

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

Donte