MySQL Bugs: #81586: .NET Bug write Nullable Values

Bug #81586	.NET Bug write Nullable Values
Submitted:	25 May 2016 7:46	Modified:	22 Nov 2021 22:30
Reporter:	Roman Kern	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.9.8	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	.net, C#, MySql.Data, nullable

Description:
If I try to use nullable values, they will be interpreted as Decimals.  Everythig is Decimal in MySqlDbType and DbType is AnsiString. It seems that MySql.Data lack of a serius implementation or type casting.
I dont understand, why the nullable type is always MySqlDbType == Decimal. 

In the source Code 
https://github.com/mysql/mysql-connector-net/blob/6.9/Source/MySql.Data/parameter.cs

at 284, the MySqlDbparameter is not set at all. Still dont get, how it comes to Decimal.

How to repeat:
using (var connection = new MySqlConnection())
{
    using (var command = connection.CreateCommand())
    {
        var p = command.CreateParameter();
        p.Value = new System.Nullable<int>();
    }
}

Suggested fix:

        string SetValue<T>(T value)
        {
            this.Value = value;
            this.SetType<T>();
        }

        void SetType<T>()
        {
            var type = typeof(T);
            if (type == typeof(int) || typeof(int?))
            {
                ..
            }
            else if
            ...
        }

or just set value as AnsiString as DbType do

Guess, implicit cast in MySql from varchar (null value) to other type will work always

set @val = 'x';
set @x = 1;
select @x, @val;
set @val = null;
set @x = @val;
select @x, @val;
-- both are null

Tested on 6.8.3 and 6.9.8

Hello Roman Kern,

Thank you for your report and test case.
Verified this behavior on Visual Studio 2013 (C#.Net) and Connector/NET 6.9.8 version.

Thanks,
Chiranjeevi.

Screenshot.

Attachment: 81586.JPG (image/jpeg, text), 190.03 KiB.

Stacktrace:
   bei MySql.Data.Types.MetaData.NameToType(String typeName, Boolean unsigned, Boolean realAsFloat, MySqlConnection connection)
   bei MySql.Data.MySqlClient.StoredProcedure.GetAndFixParameter(String spName, MySqlSchemaRow param, Boolean realAsFloat, MySqlParameter returnParameter)
   bei MySql.Data.MySqlClient.StoredProcedure.CheckParameters(String spName)
   bei MySql.Data.MySqlClient.StoredProcedure.Resolve(Boolean preparing)
   bei MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   
Exception:
Unhandled type encountered

if all parameters are null

for

CREATE PROCEDURE USearch(
    IN level tinyint, 
    IN lexact bit, 
    IN cnumber int(9) unsigned, 
    IN cname nvarchar(100),
    IN deb bigint(10) unsigned,
    IN uname nvarchar(50),
    IN state tinyint, 
    IN notification int,
    IN wildcard bit,
    IN lcount int,
    IN pwset bit,
    IN mp bit,
    IN limit int(9)
    )

Possible Fix, but require generic method!

        
        public static IDataParameter AddParameter<T>(this IDbCommand command, string name, T value)
        {
            var p = command.Parameters.OfType<IDataParameter>().FirstOrDefault(a => a.ParameterName == name);
            if (p == null)
            {
                p = command.CreateParameter();
                p.ParameterName = name;
                command.Parameters.Add(p);
            }
            p.SetDbType(value);

            return p;
        }

        private static DbType SetDbType<T>(this IDataParameter parameter, T value)
        {
            DbType result = DbType.String;
            Type itemType = typeof(T);
            if (itemType.GenericTypeArguments.Length > 0)
            {
                itemType = itemType.GenericTypeArguments[0];
            }

            if (itemType.IsClass == false)
            {
                parameter.Value = Activator.CreateInstance(itemType);
                result = parameter.DbType;
            }
            else if (itemType == typeof(string))
            {
                result = DbType.String;
            }
            else
            {
                result = DbType.Object;
            }
            parameter.Value = value;
            parameter.DbType = result;

            return result;
        }

Fix do not work with boxed parameter! Object will not be resolved correctly!

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

Default values being used could cause type inconsistencies when
Connector/NET created a parameter. This fix modifies the MySqlParameter
constructor to set DbType and MySqlDbType as strings.

Thank you for the bug report.