MySQL Bugs: #112285: Cannot run parameterized stored procedure using My SQL ODBC 8.1 connector

Bug #112285	Cannot run parameterized stored procedure using My SQL ODBC 8.1 connector
Submitted:	7 Sep 8:06	Modified:	8 Sep 12:02
Reporter:	Neil Kenny	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	8.1	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
An application that connects to MySql using the ODBC Unicode 8.0.33 connector successfully is able to run parameterized stored procedures. After upgrading to the 8.1 connector the execution fails with the following error:

ERROR [42000] [MySQL][ODBC 8.1(w) Driver][mysqld-8.0.34-commercial]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 '{ call GetDashboardData(?, ?) }' at line 1'

A stored procedure without parameters will execute without any issues

Also described here:
https://stackoverflow.com/questions/77052763/simple-stored-procedure-command-failed-after-...

How to repeat:
Create asimple console app in C# which opens an ODBC connection to a MySql database and attempts to run a parameterized stored procedure. 

First do this with the 8.0.33 connector installed and verify it works, then install the new Driver, run the application again and the error will occur. Make sure to change the Driver version in the connection string to match the currently installed driver (8.0 or 8.1). Example application:

  static void Main(string[] args)
    {
        var connection = new OdbcConnection("Driver={MySQL ODBC 8.1 UNICODE Driver}; Server=localhost; Database=theDatabase; Uid=theUser; Pwd=thePassword; ");
        
        connection.Open();
        OdbcCommand command = new OdbcCommand("{ call GetDashboardData(?, ?) }", connection);
        command.CommandType = CommandType.StoredProcedure;
        var param1 = command.CreateParameter();
        param1.ParameterName = "timePeriodHours";
        param1.Direction = ParameterDirection.Input;
        param1.Value = 24;
        param1.DbType = DbType.Int32;
        command.Parameters.Add(param1);

        var param2 = command.CreateParameter();
        param2.ParameterName = "IdList";
        param2.Direction = ParameterDirection.Input;
        param2.Value = "000";
        param2.DbType = DbType.String;
        command.Parameters.Add(param2);

        DataTable table = new DataTable();
        OdbcDataAdapter adapter = new OdbcDataAdapter(command);
        adapter.Fill(table); // Error occurs here for v8.1
        Console.WriteLine(table.Rows.Count);
    }

Hello Neil,

Thank you for the bug report.
Could you please share the body of procedure "GetDashboardData"(Please make it as private if you prefer)?

Regards,
Ashwini Patil

Hi Neil,

Thank you for your bug report.
The problem is repeatable with the simple stored procedure like this:

create procedure GetDashboardData(timePeriodHours INT, IdList varchar(32))
begin
 select timePeriodHours, IdList;
end

However, there is a workaround for the problem:

it is to remove the curly brackets for the call statement "{ call GetDashboardData(?, ?) }" as this:

OdbcCommand command = new OdbcCommand("call GetDashboardData(?, ?)", connection);

It is a temporary workaround though.

The query like "{call ...}" is not a standard MySQL syntax, but because it worked in the previous versions of the ODBC driver this is a regression.

Because the workaround for the problem exists it is being assigned Severity 2 instead of 1.

Verified against ODBC Driver 8.1.

Yes it is reproducible with any stored procedure with parameters

Posted by developer:
 
From the bug description I am not sure if having parameters to the stored procedure is relevant here. Probably not and if this is the case then better to mention it here (also, change bug title) to reduce number of variables in the equation.