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 2023 8:06	Modified:	14 Feb 23:00
Reporter:	Neil Kenny	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	8.1, 8.3, 8.0.37	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.

Posted by developer:
 
The patch and the unit test are pushed into the source tree.

Same issue is on version 8.3

Bug #113980 marked as duplicate of this one.

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/ODBC 8.4.0 release, and here's the proposed changelog entry from the documentation team:

Could not execute parameterized stored procedures using syntax that
contained curly brackets.

Thank you for the bug report.

Bug #115004 marked as duplicate of this one.