Bug #81586 .NET Bug write Nullable Values

• Submitted: 25 May 2016 7:46
• Modified: 6 Jun 2016 8:10
• Reporter: Roman Kern
• Status: Verified
• Category: Connector / NET
• Severity: S2 (Serious)
• Version: 6.9.8
• OS: Microsoft Windows
• 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!