Connector / C ++ MySQL error code: 2014, SQLState: HY000 and commands due to synchronization error why?

Question

Hi, using Connector / C ++ and executing a simple 2 sql command, such as: the first select sql starts normally, and the second causes this exception error:

ERR: commands are not synchronized; you cannot 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 OFFERED (DOES NOT WORK)

 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 cannot have more than one active connection request at a time.

From mysql_use_result docs:

You cannot use mysql_data_seek (), mysql_row_seek (), mysql_row_tell (), mysql_num_rows () or mysql_affected_rows () with the result returned from mysql_use_result () , and you cannot issue other queries until mysql_use_resres .

This is not exactly what you are using, but the problem is the same - you need to finish processing the first ResultSet and clear it before you can issue any other request on this connection.

Answer 2

I was getting the same error until I changed my code to MySQL saying to do this .
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()); 

"do while" should always be used with returning stored procedures

Answer 3

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

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

It looks like this might be a problem with the C ++ connector, but I found a workaround and wanted to post it in case anyone else had the same problem:

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