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:
Category:Connector / ODBC Severity:S2 (Serious)
Version:8.1, 8.3, 8.0.37 OS:Windows
Assigned to: CPU Architecture:Any

[7 Sep 2023 8:06] Neil Kenny
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:

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; ");
        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;

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

        DataTable table = new DataTable();
        OdbcDataAdapter adapter = new OdbcDataAdapter(command);
        adapter.Fill(table); // Error occurs here for v8.1
[8 Sep 2023 10:09] MySQL Verification Team
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)?

Ashwini Patil
[8 Sep 2023 11:40] Bogdan Degtyariov
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))
 select timePeriodHours, IdList;

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.
[8 Sep 2023 12:02] Neil Kenny
Yes it is reproducible with any stored procedure with parameters
[11 Sep 2023 6:50] Rafal Somla
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.
[13 Dec 2023 6:42] Bogdan Degtyariov
Posted by developer:
The patch and the unit test are pushed into the source tree.
[18 Jan 5:08] Dushan Perera
Same issue is on version 8.3
[14 Feb 12:38] MySQL Verification Team
Bug #113980 marked as duplicate of this one.
[14 Feb 23:00] Philip Olson
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.
[15 May 12:28] MySQL Verification Team
Bug #115004 marked as duplicate of this one.