Bug #113767 MySqlCommand Prepare method throws SQL syntax error on batch query
Submitted: 25 Jan 23:16 Modified: 6 Feb 12:32
Reporter: Nick Guidoux Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:8.0.33 OS:Windows (10)
Assigned to: CPU Architecture:Any

[25 Jan 23:16] Nick Guidoux
Description:
When the Prepare() method is called on a MySqlCommand object that contains a batch query a SQL syntax error is thrown.

How to repeat:
Sample pseudo code

List<MySqlParameter> parameters = new List<MySqlParameter>();
parameters.Add(DataServer.CreateParameter("@FirstName", MySqlDbType.VarChar, "Test"));
parameters.Add(DataServer.CreateParameter("@LastName", MySqlDbType.VarChar, "User"));

MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "insert into Person(FirstName, LastName) values (@FirstName, @LastName);select last_insert_id()";
cmd.CommandTimeout = CommandTimeout;
cmd.CommandType = CommandType.Text;
cmd.Connection = Connection;

foreach (MySqlParameter p in commandParameters)
    if (p != null)
        cmd.Parameters.Add(p);
		
cmd.Prepare();
[26 Jan 15:54] Bradley Grainger
Duplicate of bug #14115.
[26 Jan 16:22] Nick Guidoux
Interesting, I missed that bug when searching for duplicates prior to filing.

Also, compound statements were definitely working in version 8.0.17 of Connector / NET.
[27 Jan 1:17] Bradley Grainger
> Also, compound statements were definitely working in version 8.0.17 of Connector / NET.

That might have been because the "IgnorePrepare" connection string option defaulted to true (until Connector/NET 8.0.23: https://dev.mysql.com/doc/relnotes/connector-net/en/news-8-0-23.html). When that setting was true (or unspecified), calling MySqlCommand.Prepare didn't actually do anything (so it might have just seemed like it was working).
[29 Jan 16:22] Nick Guidoux
That would do it. Thanks for the information, I appreciate it.
[6 Feb 12:32] MySQL Verification Team
Hello Nick Guidoux,

Thank you for the bug report.
As Bradley Grainger said, this is duplicate of Bug #14115, please see Bug #14115.

Bradley Grainger,

Thank you for the information.

Regards,
Ashwini Patil