Actually, changing SqlProvider.GetLiteral(DateTime) made so much sense to me that I just changed it to always use the "o" format.
Hopefully fixed for you in r1109, with no regressions to SQLite or SQL Server. (No surprise, really, since SQLite doesn't really validate anything, iirc, and SQL Server overrides that method anyway...) If this causes regressions for any other DB, the DB should override the SqlProvider.GetLiteral(DateTime) method. - Jon On Fri, 2009-06-05 at 02:23 +0000, Jonathan Pryor wrote: > On Thu, 2009-06-04 at 17:20 -0700, [email protected] wrote: > > I've hit a snag using DBLinq with MySQL and timestamp fields. The SQL > > being generated for a timestamp select is not in a valid format for > > MySQL. > > > > My code generating the query is: > > > > DataContext context = new DataContext(new MySqlConnection > > ("Database=database;Data Source=localhost;User > > Id=root;Password=password"), new DbLinq.MySql.MySqlVendor()); > > context.Log = Console.Out; > > Table<MyItem> table = context.GetTable<MyItem>(); > > var list = from item in table where item.Inserted < DateTime.Now > > select item; > > Console.WriteLine((list.FirstOrDefault() == null) ? "failed" : > > "suceeded"); > > > > The inserted property on the MyItem class is defined as: > > > > [Column(Storage = "_inserted", Name = "inserted", DbType = > > "timestamp", CanBeNull = false)] > > public DateTime Inserted {get; set;} > > > > The SQL generated is: > > > > select inserted from myitems where inserted < '5/06/2009 10:14:46 > > AM' limit 1 > > > > The timestmap format in the above SQL is invalid for MySQL and should > > be: > > > > select inserted from myitems where inserted < '2009-06-05 10:14:46' > > limit 1 > > > > I'm about to dig into the DBLinq source to try and fix this for my > > specific case but was wondering if there is any advice on the correct > > remedy instead of just throwing in a possible hack? > > The problem can probably be fixed in one of two spots: > > The first spot is in SqlProvider.GetLiteral(DateTime), in > src/DbLinq/Vendor/Implementation/SqlProvider.cs: > > public virtual SqlStatement GetLiteral(DateTime literal) > { > return Convert.ToString(literal.ToString()); > } > > This uses DateTime.ToString(), which uses the default locale conventions > (e.g. "Month/Day/Year Hour:Minute:Second"), which is what you're seeing > (and what you don't want). > > Arguably NO database should be using this format, so it may be more > sensible to make the default SqlProvider.GetLiteral(DateTime) > implementation do something more sensible. (Then again, perhaps it is > doing the most sensible thing, though I highly doubt that. It would be > extremely helpful to know what the different databases want for datetime > string formats...) > > The second possible place to fix it is in > src/DbLinq.MySql/MySqlSqlProvider.cs, by overriding > SqlProvider.GetLiteral(DateTime) to do something saner. > > Based on the fact that SQL Server overrides > SqlProvider.GetLiteral(DateTime) to use DateTime.ToString("o"), and > SQLite explicitly lists YYYY-MM-DDTHH:MM:SS.SSS (the format generated by > DateTime.ToString("o")) as a supported format [0], we should probably > make SqlProvider.GetLiteral(DateTime) return literal.ToString("o") > instead of literal.ToString() (though I'd like to know the effect on the > MySQL/etc. unit tests before doing this). > > - Jon > > [0] http://www.sqlite.org/lang_datefunc.html > > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "DbLinq" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/dblinq?hl=en -~----------~----~----~----~------~----~------~--~---
