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

[25 May 2016 7:46] Roman Kern
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
[25 May 2016 7:54] Roman Kern
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
[25 May 2016 7:58] Roman Kern
Tested on 6.8.3 and 6.9.8
[25 May 2016 8:36] Chiranjeevi Battula
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.
[25 May 2016 8:36] Chiranjeevi Battula
Screenshot.

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

[25 May 2016 9:03] Roman Kern
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)
    )
[6 Jun 2016 8:09] Roman Kern
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;
        }
[6 Jun 2016 8:10] Roman Kern
Fix do not work with boxed parameter! Object will not be resolved correctly!
[22 Nov 2021 22:30] Christine Cole
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.