[Mono-bugs] [Bug 500987] SqlCommand with DateTime parameter throws exception "Error converting data type varchar to datetime" (impacts NHibernate and Castle ActiveRecord)

[bugzilla_noreply at novell.com]

http://bugzilla.novell.com/show_bug.cgi?id=500987

User loic.nageleisen at gmail.com added comment
http://bugzilla.novell.com/show_bug.cgi?id=500987#c13





--- Comment #13 from Loic Nageleisen <loic.nageleisen at gmail.com>  2009-05-15 08:12:15 MDT ---
Try this snippet:

            connection = new SqlConnection(connectionString);
            connection.Open();
            string[] queries = { "SET LANGUAGE Français", 
                            "insert into TestModel (Name, Date) values
('test1',' janvier 2 2009 00:00:00:000')",
                            "insert into TestModel (Name, Date) values
('test2',' janv 2 2009 00:00:00:000')",
                            "insert into TestModel (Name, Date) values
('test3',' jan 2 2009 00:00:00:000')"
                            };
            foreach (string query in queries)
            {
                Console.WriteLine(query);
                cmd = new SqlCommand(query, connection);
                cmd.ExecuteNonQuery();
            }

This should work up to the third one, which should fail with:

            Échec de la conversion d'une valeur datetime à partir d'une chaîne
de caractères

What's more, this snippet behavior is the same regardless of whether
Thread.CurrentThread.CurrentCulture is set to fr-FR or en-US. Even better, the
following queries work just as well:

            string[] queries = { "SET LANGUAGE us_english", 
                            "insert into TestModel (Name, Date) values
('test1',' january 2 2009 00:00:00:000')",
                            "insert into TestModel (Name, Date) values
('test2',' jan 2 2009 00:00:00:000')",
                            "insert into TestModel (Name, Date) values
('test2',' feb 2 2009 00:00:00:000')"
                            };

Additionally, the snippet behave the same under Mono and .Net.

Also, I ran some trivial formatting and concluded that .Net french month
abbreviations are the same as Mono ones, including the trailing dot.

Therefore some of my deductions ('bets') above were wrong. Now, from my point
of view, there seem to be two distinct bugs here, thus some misunderstandings
on my part. The two issues are:
1) Mono/.Net datetime french abbreviations do not match MSSQL ones, and Mono
sends abbreviations.
2) base.Local should match the language used on the connection to the database,
so as to generate proper month names.

I humbly propose the following fixes:

1) In Tds70.cs, change '{0:MMM dd yyyy hh:mm:ss.fff tt}' to '{0:MMMM dd yyyy
hh:mm:ss.fff tt}' in order to make it immune to abbreviation differences
between runtime and SQL by providing the full name of the month.
2a) have base.Locale set to the correct culture matching what the database
expects, which I have no insight of how and where it is set currently (I
admittedly didn't really look yet), and what its current role is, although
judging from its use here I suppose its role is precisely this.
2b) once a SqlConnection is established, immediately send a 'SET LANGUAGE
@lang' query, with @lang set to the proper value matching base.Locale (e.g
British English for en-GB). SET LANGUAGE only affects the current connection so
that seems trouble free, though being a workaround for when 2a) fails.

Once the two items are fixed, and to the extend of my knowledge, Mono and .Net
will behave precisely the same. 

Unfortunately I can't checkout svn right now, and have no build environment
sufficient to build Mono so I am unable to test that and provide a patch.

Thanks to you for being reactive.

-- 
Configure bugmail: http://bugzilla.novell.com/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the QA contact for the bug.