MySQL Bugs: #64695: about Prepared Statements

Bug #64695	about Prepared Statements
Submitted:	19 Mar 2012 16:59	Modified:	24 Jan 2013 18:48
Reporter:	seo twey	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S3 (Non-critical)
Version:	any version	OS:	Any
Assigned to:	Matthew Lord	CPU Architecture:	Any
Tags:	exp

Description:
with stored programs condition:
why mysql_stmt_free_result() can't release the result?
i must use "while ( (0 == mysql_next_result(mysql_)) );" a function not in mysql_stmt_xxx .......

How to repeat:
with stored programs condition:
why mysql_stmt_free_result() can't release the result?
i must use "while ( (0 == mysql_next_result(mysql_)) );" a function not in mysql_stmt_xxx .......

Suggested fix:
fix mysql_stmt_free_result()!

Please, provide complete example of the code that looks problematic for you.

// SP
CREATE DEFINER=`root`@`localhost` PROCEDURE test( in t1 int)
BEGIN
select t2 from test from id=t1;
END

void test()  
{   
	// connect ...

	int t1,t2;
	MYSQL_STMT *hStmt = mysql_stmt_init(m_hSql);
	char sql[] = "call test(?)";
	if ( mysql_stmt_prepare( hStmt,sql,static_cast<unsigned long>(strlen(sql))) != 0 )
		printf(mysql_stmt_error(hStmt);

	ibind.buffer_type = MYSQL_TYPE_LONG;
	ibind.buffer = t1;

	if ( mysql_stmt_bind_param(hStmt,&ibind) != 0 )
		printf(mysql_stmt_error(hStmt));

	if ( mysql_stmt_execute(hStmt) != 0 )
		printf(mysql_stmt_error(hStmt));

	obind.buffer_type= MYSQL_TYPE_LONG;
	obind.buffer= (char *)&t2;

	if (mysql_stmt_bind_result(hStmt, &obind) !=0 )
		printf(mysql_stmt_error(hStmt));

	if (mysql_stmt_store_result(hStmt) !=0 )
		printf(mysql_stmt_error(hStmt));  

	rows = mysql_stmt_num_rows(hStmt);
	for( my_ulonglong i=0; i<rows; ++i )
	{
		nRet = mysql_stmt_fetch(hStmt);
	}

	// here : if call test() twice,
	// in mysql_stmt_prepare() comes error：2014 Commands out of sync; you can't run this command now
	// and if add followed code :
	//
	// int result = mysql_next_result(m_hSql);
	// while (result != -1)
	// {
	//		result = mysql_next_result(m_hSql);
	// }
	//
	// after mysql_stmt_free_result(hStmt); it works!
	// so with the description of mysql_next_result(),
	// i think mysql_stmt_free_result() didn't free the result.

	mysql_stmt_free_result(hStmt);
	mysql_stmt_close(hStmt);
}

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php

I don't even understand what I'm trying to repeat and verify here. Your incomplete test case shows you trying to call mysql_stmt_prepare again without first calling mysql_stmt_free_result. Then saying that if you iterate through the results and call mysql_stmt_free_result again, then it works? Are you attempting to show that when you *don't* iterate through the resultset, that the mysql_stmt_free call fails? I don't see where you've demonstrated, or even attempted to demonstrate this. mysql_stmt_free_result *should* simply discard any unfetched rows. If you can provide a working test case that demonstrates that this is NOT working as expected, then please let me know. I'll be happy to try and verify it.

I don't even see where you've provided the results of your own tests that clearly show evidence that something is not working as expected.

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.