MySQL Bugs: #72480: Prepared statement + Stored Procedure + Multiple resutsets

Bug #72480	Prepared statement + Stored Procedure + Multiple resutsets
Submitted:	29 Apr 2014 10:01	Modified:	9 Jan 2015 12:53
Reporter:	kokos kokou	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / C++	Severity:	S3 (Non-critical)
Version:	1.1.2	OS:	Windows (7)
Assigned to:	Hemant Dangi	CPU Architecture:	Any
Tags:	error code 0, multiple resultsets, prepared statement, stored procedure

Description:
Using a prepared statement to execute a stored procedure that returns multiple results sets, fails with "ERROR: '' (MySQL error code: 0, SQLState: 00000)" when calling getResultset() for the first time after execute()

SQL server v 5.6.11
libMySQL v 6.1.3
mySQLConnectorC++ 1.1.2
Visual studio 2012

How to repeat:
void main(int argc, char* argv[]){
    bool myTrue;
    try{
        Driver *driver = get_driver_instance();
        Connection *con= driver->connect("127.0.0.1", "user", "pass");
        con->setClientOption("OPT_RECONNECT", &myTrue);
        con->setClientOption("CLIENT_MULTI_RESULTS", &myTrue);
        con -> setSchema("db");

        //This fails
        PreparedStatement *ps = con->prepareStatement("CALL tworesults()");
        ps->execute();

        //This works
        /*Statement *ps = con->createStatement();        
        ps->execute("CALL tworesults()");*/    

        try{
            do{
                printf("resultset:\n");   
                ResultSet  *rs = ps->getResultSet();
                while (rs->next()){
                    printf("%d\n", rs->getInt(1));
                }                    
                delete rs;
            }while(ps->getMoreResults());
        }catch (SQLException &e) {	       
        printf("ERROR: getting result set: %s (MySQL error code: %d, SQLState: %s)\n",
               e.what(), e.getErrorCode(), e.getSQLState().c_str());
        }
        con->close();
        delete con;
    }catch (SQLException &e) {
        printf("ERROR: %s (MySQL error code: %d, SQLState: %s)\n",
               e.what(), e.getErrorCode(), e.getSQLState().c_str());	
    }
}

CREATE PROCEDURE tworesults()
BEGIN
 select * from clientstatus;
 select * from clientcategories;
END

Hello kokos,

Thanks for your interest in MySQL software.

It is not a bugs as prepared statement doesn't support multiple statement and results. 

Please check below text from link 
http://dev.mysql.com/doc/refman/5.6/en/c-api-multiple-queries.html

"The multiple statement and result capabilities can be used only with mysql_query() or mysql_real_query(). They cannot be used with the prepared statement interface. Prepared statement handles are defined to work only with strings that contain a single statement."

Above statement was for multiple queries and not for stored procedure.

MySQL_Prepared_Statement::getMoreResults() functionality is implemented in rev#983 C/C++ 1.1.5 version, so now multiple resultset can be fetched using prepared statement.