[Mono-list] Bug (?) in SqliteDataReader

[Nikki Locke]

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/