Joshua Tauberer wrote: > Just declare the column DATETIME and insert your favorite nonintegral > number (with or without quotes!). Sqlite ignores the types of the > columns (sometimes...?), which is among the reasons why there's so much > trouble fitting Sqlite into the db adapter paradigm. > > As I suspected, if you do this, and with your patch (that returns > typeof(DateTime) in GetSchemaTable for DATETIME columns), an > InvalidCastException is thrown in > System.Data.Common.AbstractDataContainer when reading the data back. > Since there's no native way to store DateTimes in Sqlite, it's possible > someone would want to put a floating point representation of a DateTime > into a DATETIME column (like the return of DateTime.ToOADate()).
Thats a point against my patch. > OTOH, if you insert a string that can't be parsed as a DateTime, a > FormatException is thrown instead in > Mono.Data.SqliteClient.SqliteDataReader (with or without the patch) > since we assume strings in a DateTime columns are parseable. (We could > aggressively convert doubles to DateTimes too, which would partially get > around the problem above.) That's a point against the existing implementation. > The next best thing we can do, I think, is to have GetSchemaTable return > the result of GetFieldType. GetFieldType is implemented to look at the > first row of the result set and return the type of the object it's > actually going to return (or to return typeof(string) if the result set > is empty). This way, so long as the column contains fields of a > consistent type, it'll be ok. However, even this is possible to mess > up. If I declare the column as INT and then insert first an integer and > then a string (let's say by accident), a FormatException is thrown in > AbstractDataContainer when reading it back. That's a point against this proposed fix. > Lastly, since SqliteDataReader buffers all of the results, a solution > could be to return a type that surely all of the values in a column can > be converted to. If a double is found in a DATETIME column, it could > automatically return typeof(string) and convert all of the values > appropriately. However, this would be doing a lot of behind-the-scenes > conversions that don't seem too friendly. That sounds horrible. > So my inclination after all of this is to recommend not using the > DataTable class with Sqlite. But what do you say? That's a cop-out :-) What about putting in some type checking when the data is _written_ to the database, instead? OK, it wouldn't stop exceptions being thrown if the data was written by a some other app, but at least it would make things work consistently. > And finally, if there's really a bug in DateTime.Parse reversing the > dates on non-US locales, fixing that might make all of this moot (for > your application, at least). That would help - at least I could put in some MONO-specific code which would work for my application. Could I have a copy of the test code you used to try all this out? I want to run it on the Microsoft/Finisar version, to see what happens. -- Nikki Locke, Trumphurst Ltd. PC & Unix consultancy & programming http://www.trumphurst.com/ _______________________________________________ Mono-list maillist - [email protected] http://lists.ximian.com/mailman/listinfo/mono-list
