Bug #30073 Simple select stored procedures return mutiple results

• Submitted: 26 Jul 2007 15:04
• Modified: 30 Jul 2007 23:03
• Reporter: Jeff Ward
• Status: Not a Bug
• Category: MySQL Server: C API (client library)
• Severity: S3 (Non-critical)
• Version: 5.1.20
• OS: Windows
• CPU Architecture: Any

[26 Jul 2007 15:04] Jeff Ward

Description:
Calling a simple Select stored procedure through the prepared statement API sets the server flag of having multiple results available, even though multiple select statements were not executed and the second result is empty.

How to repeat:
The following program demonstrates this problem:

#include <iostream>

#include <windows.h>
#include <mysql.h>

const char* pSelectTestString = "call SelectStarTest";

void HandleError(MYSQL* psql)
{
	std::cout << mysql_error(psql) << std::endl;
}

int main()
{
	int ivar = 5;
	bool trueVar = true;

	MYSQL mysql;
	mysql_init(&mysql);
	mysql_real_connect(&mysql, "localhost", "connect_test", "connect_test", "ConnectionTestDb", 
		MYSQL_PORT, NULL, CLIENT_MULTI_RESULTS | CLIENT_MULTI_STATEMENTS);
	mysql_autocommit(&mysql, false);

	MYSQL_STMT* pselectStatement = mysql_stmt_init(&mysql);
	mysql_stmt_prepare(pselectStatement, pSelectTestString, strlen(pSelectTestString));

	if(mysql_stmt_execute(pselectStatement) == 0)
	{
		int irows = 0;
		mysql_stmt_store_result(pselectStatement);
		irows = mysql_stmt_num_rows(pselectStatement);
		while(!mysql_stmt_fetch(pselectStatement))
			irows++;

		std::cout << irows << std::endl;

		mysql_stmt_free_result(pselectStatement);
		//mysql_next_result(&mysql); // Adding this line fixes the issue.
	}
	else
		HandleError(&mysql);

	if(mysql_stmt_close(pselectStatement))
		HandleError(&mysql);	// Causes command out of sync
	
	mysql_rollback(&mysql);
	mysql_autocommit(&mysql, true);

	mysql_close(&mysql);

	char myString;
	std::cin >> myString;

	return 0;
}

----------------
The SelectStarTest procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS 'SelectStarTest` $$
CREATE PROCEDURE `SelectStarTest`()
BEGIN
	SELECT * FROM DefaultTable;
END $$

DELIMITER ;

[30 Jul 2007 23:03] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read carefully about CALL syntax at http://dev.mysql.com/doc/refman/5.1/en/call.html and especcially about "C API Handling of Multiple Statement Execution" at http://dev.mysql.com/doc/refman/5.1/en/c-api-multiple-queries.html:

Multiple-result processing also is required if you execute CALL statements for stored procedures: A stored procedure returns a status result when it terminates, but it may also produce result sets as it runs (for example, if it executes SELECT statements). For any stored procedure that produces result sets in addition to the final status, you must be prepared to retrieve multiple results.