MySQL Bugs: #75267: MySqlParameter with Direction=Output does not work for Text commands

Bug #75267	MySqlParameter with Direction=Output does not work for Text commands
Submitted:	18 Dec 2014 18:29	Modified:	28 Nov 2022 17:43
Reporter:	Jay Nading	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.9.4, 6.9.5	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
A MySqlParameter with a Direction of ParameterDirection.Output is not treated as an output parameter when referenced within a MySqlCommand whose CommandType is CommandType.Text.

How to repeat:
Code to reproduce problem:

    string connectionString = "TBS";
    MySqlConnection connection = new MySqlConnection(connectionString);
    connection.Open();

    MySqlCommand cmd = connection.CreateCommand();
    cmd.CommandText = "set @outputParam = 1234";

    MySqlParameter outputParam = new MySqlParameter();
    outputParam.ParameterName = "@outputParam";
    outputParam.MySqlDbType = MySqlDbType.Int32;
    outputParam.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(outputParam);

    cmd.ExecuteNonQuery();

    object returnedValue = cmd.Parameters["@outputParam"].Value;

Expected result: The value 1234 is assigned to the returnedValue variable in the last line of the example code.

Actual result: ExecuteNonQuery throws an exception with the following message:

    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 'NULL = 1234' at
    line 1

Note: When the code is modified to use the corresponding classes in the System.Data.SqlClient namespace to access a MSSQL database, the code produces the expected result.

Server: MySQL Community Edition 5.6.19 running in Windows Server 2008 R2 Enterprise SP1
Connector/Net: 6.9.4 running on Windows 7 Professional SP 1 (64-bit)

Hello Jay Nading,

Thank you for the bug report.
Verified this behavior on Visual Studio 2013 (C#.Net) with  MySQL Connector/Net 6.9.5.

Thanks,
Chiranjeevi.

I suggest to raise the severity. A construct we use often is SELECT @Name=Name, @Code=Code FROM ... which works in SQL-Server and does not work in MySql: output parameters remain null (.net null as opposed to database NULL), inhibiting an 1:1 port of software from SQL-Server to MySql. Updated the server to v5.5.45 and updated the .net connector to v6.9.7; no luck. Windows 10, 32 bit. Thanks for boosting the priority of this shortcoming, Michiel de Wolde.

This is still an issue for the latest version of Connector/NET, are there no updates since this was reported over two years ago?

Is there anyway to utilize the out parameter set that was introduced in MySQL 5.5.3?
https://dev.mysql.com/doc/internals/en/out-parameter-set.html

Is this issue fixed now？ It look like it still be there

Unfortunately it is still an issue. I upgraded the server from 5.6.30 to 5.6.45 and Connector/NET from 6.9.8 to 8.0.17. Retested in Visual Studio 2017, using NuGet to install MySQL.Data 8.0.17. Same error.

Output parameters in combination with a command of type text is still not supported. Upgraded Connector/NET to 8.0.31, using Visual Studio 2022. I am starting to become curious. I am 61 now. Will this be solved before I die?

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

Output parameters in combination with a command of type CommandType.Text
were not supported. This fix changed how Connector/NET manages the
parameters with an output direction for text commands.

Thank you for the bug report.

Let me start with a simple Thank You, for picking up the issue. I tested it; it works.

I updated Connector/NET (and MySQL Workbench), using mysql-installer-community-8.0.32.0.msi.
I updated NuGet package MySql.Data to version 8.0.32.

A command of type text, an output parameter and an ExecuteNonQuery now works for the following case:
SET @valNum:=(SELECT ValueNumber FROM `sky.application` WHERE Name='Agents')
Value @valNum can then be retrieved through the output parameter.

Note that retrieving two values does not work for me, despite it does the job in the Workbench:
SELECT @valNum:=ValueNumber, @instNum:=InstanceNumber FROM `sky.application` WHERE Name='Agents'
Error: Parameter 'valNum:=ValueNumber' not found in the collection.
Same error if retrieving one value.

Fortunately the following syntax works:
SELECT ValueNumber, InstanceNumber INTO @valNum, @instNum FROM `sky.application` WHERE Name='Agents'

An addition to my previous comment. Although the output parameter worked for an int value it did not for binary data. Solved it by replacing @valNum etc in the INTO part  by valNum etc, hence by not using @ for the variable. The @ interferes with the query generated internally: valNum becomes something like @_cnet_param_valNum. Possibly @valNum becomes @_cnet_param_@valNum, resulting in an error in the query.