Tryiing to read multiple results sets from mariadb using c++ connector, Command "out of sync?"

Question

Using mysql c++ connector with the following stored procedure (that works by hand)

DELIMITER $$
CREATE PROCEDURE sp_getvalues()
BEGIN
    SELECT max(a) FROM A;
    SELECT max(b1), min(b2) FROM B;
    SELECT sum(x) FROM C;
END
$$
DELIMITER ;

DELIMITER $$

The following c++ code gives the error:

Commands out of sync; you can't run this command now

the first time stmt->getResultSet() is called. What should be called?

void test_multiple_query(Connection* con) {
  sql::Statement* stmt = con->createStatement();
  stmt->executeQuery("CALL sp_getvalues()");
  sql::ResultSet* res = stmt->getResultSet(); 
  cout << res->getInt(1) << '\n';
  delete res;
  res = stmt->getResultSet();
  cout << res->getInt(1) << '\t' << res->getInt(2) << '\n';
  delete res;
  res = stmt->getResultSet();
  cout << res->getDouble(1)<< '\n';
  delete res;
  delete stmt;
}

As Dan Block suggested below, the connection has to be set up with multiple_connection as an option. I was also missing res->next() to get the first (and only) row in each resultset.

The first:

res.reset(pstmt->getResultSet());

works. The second fails with the error:

Commands out of sync; you can't run this command now

What function do I need to call to close out the first and get the second?

void test_multiple_query2(Connection* con) {
  std::unique_ptr< sql::PreparedStatement >  pstmt;
  std::unique_ptr< sql::ResultSet > res;

  pstmt.reset(con->prepareStatement("CALL sp_getvalues()"));
  res.reset(pstmt->executeQuery());
  if (res->next()) {
    cout << res->getInt(1) << '\n';
  }
  res.reset(pstmt->getResultSet());
  if (res->next()) {
    cout << res->getInt(1) << '\t' << res->getInt(2) << '\n';
  }
}

int main() {
  connection_properties["CLIENT_MULTI_RESULTS"]= "true";
  connection_properties["hostName"]="tcp://127.0.0.1:3306";
    /* user comes from the unit testing framework */
  connection_properties["userName"]="testuser";
  connection_properties["password"]="mypw";
  connection_properties["useTls"]= "true";
    Connection* con = driver->connect(connection_properties);
  test_multiple_query2(con);
}

Answer 1

From CALL, "If the CLIENT_MULTI_RESULTS API flag is set, CALL can return any number of resultsets and the called stored procedure can execute prepared statements"

In MariaDB's Connector C++ this is done with a property:

connection_properties["CLIENT_MULTI_RESULTS"]= "true";

con.reset(driver->connect(connection_properties));

MySQL Documents have the example:

sql::Driver * driver = get_driver_instance();

std::auto_ptr< sql::Connection > con(driver->connect(url, user, pass));
con->setSchema(database);

std::auto_ptr< sql::PreparedStatement >  pstmt;
std::auto_ptr< sql::ResultSet > res;

pstmt.reset(con->prepareStatement("CALL get_data()"));
res.reset(pstmt->executeQuery());

for(;;)
{
  while (res->next()) {
    cout << "Name: " << res->getString("Name")
         << " Population: " << res->getInt("Population")
         << endl;
  }
  if (pstmt->getMoreResults())
  {
    res.reset(pstmt->getResultSet());
    continue;
  }
  break;
}