mySQL C++ connector “Commands out of sync” while calling stored procedure multiple times

manpreet Best Answer 2 years ago

I have a stored procedure, which inserts one record into a table and returns the last inserted id. When I call it com/tag/multiple">multiple com/tag/times">times in the C++ connector, on the 2nd time, it always fails with "Commands out of sync; you can't run this command now". The first insertion is successful and the inserted id can be fetched back.

I have closed and deleted the "sql::ResultSet" object after the first call, but it has no use. In some other posts, I saw that you need to call com/tag/statement">statement's "next_result()" before the 2nd call, but in the C++ connector's Statement class, there is no such function.

stored procedure:

PROCEDURE `insert_into_table`(IN ...)
BEGIN
INSERT INTO MyTable (...) VALUES (...);
SELECT LAST_INSERT_ID() AS id;
END

C++:

sql_query = "call insert_into_table(...);";
{
    auto res = com/tag/statement">statement->executeQuery(sql_query);
    res->close();
    delete res;
    // the second call always fails 
    res = com/tag/statement">statement->executeQuery(sql_query);
}

manpreet 2 years ago

Try to flush the ResultSet before calling executeQuery again, failing which, the command-out-of-sync nightmare may come. Deleting the Resultset will not serve the purpose. In your case since the stored procedure is not returning anything, you can try the following snippet:

res = statement->executeQuery(sql_query);
while (res->next())
{       
}

Suppose, your stored procedure returned an integer, you could have used like so:

res = statement->executeQuery(sql_query);
while (res->next())
{
   return res->getInt(1);       
}