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);
}
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;
}