MySQL Bugs: #9121: CLIENT_MULTI_STATEMENTS works with mysql_real_query but not mysql_stmt

Bug #9121	CLIENT_MULTI_STATEMENTS works with mysql_real_query but not mysql_stmt_prepare
Submitted:	11 Mar 2005 6:47	Modified:	11 Mar 2005 21:49
Reporter:	Hasani	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	4.1	OS:	Windows (Win32)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Ok, There's a new feature where mysql allows the execution of multiple queries -> http://dev.mysql.com/doc/mysql/en/c-api-multiple-queries.html . This seems to only work when doing non parameterized queries, via mysql_real_query. If one wants to use this feature with parameterized queries, a call to mysql_stmt_prepare will fail, when the statement is semi-colon delimited.

e.x.:
SET SQL_SELECT_LIMIT=1;SELECT * FROM foo

generates the following error:

"1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';SELECT * FROM foo' at line 1"

How to repeat:
C# example:

MySqlHandle handle = new MySqlHandle();
handle.RealConnect("localhost", 0, "root", "xxx", "test", MySqlClientOptions.MultiStatements | MySqlClientOptions.MultiResults);
string query = "SET SQL_SELECT_LIMIT=1;SELECT * FROM foo";
MySqlStatement stmt = handle.CreateStatement();
stmt.Prepare(query);

Suggested fix:
Explicitly state if multistatements is strictly for mysql_real_query/mysql_query or make it work for the mysql_stmt_XXXX functions. I would prefer the latter =]

Prepared statements don't support multi-statements.
This is a documentation issue so I am assigning it to
myself and changing the category to Documentation.

Will this be a feature in a newer version?

There are no plans for this. The prepared statement API
is designed to work with single statements only.

The description for mysql_stmt_prepare() indicates that
the statement string must consist of a single statement:

http://dev.mysql.com/doc/mysql/en/mysql-stmt-prepare.html

I will add a note to the multi-statement section that this
capability is available for mysql_query() and mysql_real_query()
but not the prepared statement API:

http://dev.mysql.com/doc/mysql/en/c-api-multiple-queries.html