MySQL Bugs: #51239: mysql parameters parse values with the current culture

Bug #51239	mysql parameters parse values with the current culture
Submitted:	17 Feb 2010 12:00	Modified:	19 Feb 2010 12:30
Reporter:	Peter Hagen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	6.2.2	OS:	Linux
Assigned to:	Tony Bedford	CPU Architecture:	Any

Description:
It seems that the .net connector uses the current culture to format values in parameters. My culture is set to nl-NL. If i add a float to a parameter, with value (0.5) which is in Dutch writen as '0,5', the value 0 is put into the DB. Also tested it with 5,5 which results in 5.

The fields are for example double(3,2), so there should be at least a 2 decimal precision.

To fix this problem for now, I add the value not as object, but as string: (this example comes from my own mysql provider)

if (type != null && (type == typeof(double) || type== typeof(float)))
{
  base.AddParameter(cm, Parametername, type, ((Double)Value).ToString(MySqlStatementBase.MySqlCulture) );
}

Datetimes have the same issue. After parsing them to a string (ToString("yyyyMMdd...) the issue is fixed. But I guess this should be done by the connector, not by my own code

How to repeat:
Set the culture to "nl-NL". Create a statement which insert a value with a parameter:

  insert into mod_issue (hours) values (@par0);

@par0 has to be filled with a double or float:

  float Value = 0.5;

if you print the float in this culture settings, it will result as this:

  Console.WriteLine("value={0}", Value);

  value=0,5

Inserting the value to the parameter object will be done by object, not by string:

  IDbDataParameter parameter = new MySqlParameter(Parametername, t);
  parameter.Value = Value; // ((float)0.5
  command.Parameters.Add(parameter);

After executing the query, the value in the table will be: 0

Suggested fix:
formatting of the string should always use a fixed culture (en-US?)

Peter, this is a duplicate, let me find the original report.

again? ooow sorry. I couldnt find something like this in the search bug form. For now i have a solution by parsing the values to strings, with the en-US culture. So no hurry for me

Nope, I was wrong... Bug#43574 and Bug#44455 are about EntityFramework (same problem though).

Peter, can you please attach small, self-sufficient test case so I can check?

Peter, I need to see how you used cultures... ie.
    CultureInfo curCulture = Thread.CurrentThread.CurrentCulture;
    CultureInfo curUICulture = Thread.CurrentThread.CurrentUICulture;
    CultureInfo newCulture = new CultureInfo("nl-NL");
    Thread.CurrentThread.CurrentCulture = newCulture;
    Thread.CurrentThread.CurrentUICulture = newCulture;
...

the testing was on Ubuntu 9.10 with a MySql "mysql  Ver 14.14 Distrib 5.1.37, for debian-linux-gnu (i486) using  EditLine wrapper" run on Mono 2.6.1. 

The result if run without the culture settings is "0.5". We run with the nl-NL settings, it results in "0"

adding:

Thread.CurrentThread.CurrentCulture = new CultureInfo("nl-NL");

doesn't make a difference, by the way.

Thanks Peter, will check on this.

and I tested the usage of the InvariantCulture:

Console.WriteLine("val={0}", val.ToString(CultureInfo.InvariantCulture));

which results in '0.5'. So that is ok. Can you remove the zip by the way. I left some personal info in it

Removed.

Peter, can't repeat (again) against trunk (latest sources)...

Regarding having "." for default. "." is actually a culture insensitive decimal separator in SQL. Nothing to do with US or GB. Culture sensitive are the default conversions of string <-> decimal values.

Now, I modified your code a bit since parameter.Add is deprecated and one should use parameter.AddWithValue. Also you did not include DDL statements so I have no INSERT, just SELECT:

    Console.WriteLine("Default culture={0}", Thread.CurrentThread.CurrentCulture);
    Thread.CurrentThread.CurrentCulture = new CultureInfo("nl-NL");

    string sql = "SELECT (?par0)";
    double val = 0.5;
    Console.WriteLine("Value set to ={0}", val);

    string ConnS = string.Format("User ID={2};Password={3};Initial Catalog={1};Server={0};logging=true;pooling=false;", "**", "**", "**", "**");
    using (MySqlConnection connection = new MySqlConnection(ConnS))
    {
        try
        {
            connection.Open();
            MySqlCommand cm = new MySqlCommand();
            cm.CommandText = sql;
            cm.Connection = connection;

            MySqlParameter parameter = new MySqlParameter("par0", DbType.Decimal);
            cm.Parameters.AddWithValue("par0", val);
            cm.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
Console.Error.WriteLine("MySqlProvider::TestConnection::exception: {0}", ex.Message);
        }
        finally
        {
            Console.WriteLine("DONE TEST");
            connection.Close();
        }
    }

Produces:
Default culture=hr-HR
Value set to =0,5
DONE TEST

General query log from MySQL server shows:
SELECT (0.5)
which is also correct.

Am I missing something?

I have tested the usage of the AddWithValue without my changed code (not in the test, but in the main application), and that seems to work flawless (cm.Parameters.AddWithValue("par0", val)). So that solves the issue. Allthough you did write to create a new parameter: 

MySqlParameter parameter = new MySqlParameter("par0", DbType.Decimal);

But that doesn't need to be done :p. I guess the deprecated Parameter.Add is the source of the issue. Shouldn't that be fixed somehow, or maybe throw an exception on usage? Or a workaround?

I don't think it's necessary to do more, it's documented in changelog and in intellisense. But might ask Reggie why is param.Add even there any more. Probably some legacy...

Tony, please see that this is documented and that samples are changed as Reggie suggested.

I have changed the examples/tutorials in the main docs to use AddWithValue(). (Changes should appear online within 24 hours)