[Mono-list] Bug (?) in SqliteDataReader

[Joshua Tauberer]

Nikki Locke wrote:
>> I'll apply something like your original suggested fix.  But, I wonder 
>> what would happen if a double value ends up in a DATE column, for 
>> instance (as Sqlite allows)?
> 
> I've no idea. Perhaps we should try it? How is it achieved?

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()).

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.)

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.

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.

So my inclination after all of this is to recommend not using the
DataTable class with Sqlite.  But what do you say?

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).

-- 
- Joshua Tauberer

http://taubz.for.net

"Unfortunately, we're having this discussion. It's too bad,
because guess who listens to the discussion: the enemy."