[Mono-list] Bug (?) in SqliteDataReader

[Joshua Tauberer]

Hi, Nikki,

Nikki Locke wrote:
> I use the Sqlite database under both Windows and Linux. Under Windows, if I 
> have a date or datetime field, then row data returned from a query on that 
> field is returned as a DateTime. Under Linux, it is returned as a string. 

Are you sure you have the same version of mono (esp.
Mono.Data.SqliteClient) *and* Sqlite (the native library) on both
systems?  The way DateTimes are handled depends on a lot, unfortunately,
because Sqlite doesn't have a way of storing datetimes natively.  The
recommended way of using DateTimes with Sqlite is to encode/decode them
yourself in a natural way either to/from a long or some particular
string format that you decide.

There are two versions of Sqlite.  Sqlite2 only has strings internally,
which is probably what you're seeing in Linux.  The DateTimes are being
coerced into a string at some point, and it's choosing a
culture-sensitive format.  When reading back the data, there's no way to
know that it was originally a DateTime and not a string, so it returns
the string.  Using Sqlite2, you really can't use DateTimes without
encoding them yourself.

Sqlite3 has string, integer, and real internal storage types, but that
doesn't help when reading to determine that a value was originally a
datetime.  But Sqlite3 also provides the names of the types of the
columns as the table was created with.  If a column is declared as a
DATE or DATETIME, SqliteDataReader will try to turn the value back into
a DateTime.  This is probably what you're seeing in Windows.  If it
finds an integer value, it uses DateTime.FromFileTime, which is the
reverse of how it encodes DateTimes if you insert a DateTime via
parameters.  If it finds a string value, it uses DateTime.Parse -- but
note that this is a very slow operation.  So with Sqlite3, DateTimes
should be put into DATE or DATETIME columns in the database either
through parameters or by turning it into a long with ToFileTime
yourself, and then they will be read back as DateTimes.

> This appears to be a bug in SqliteDataReader.GetSchemaTable, which sets 
> schemaRow["DataType"] to typeof(string) for every field, regardless.

For Sqlite2, that's correct because everything goes in and comes back as
a string.  For Sqlite3, it's impossible to know what kind of values are
actually going to be encountered in a column (e.g. DATETIME columns can
have float values), so strings is the best guess.  It might be possible
to do some guessing for GetSchemaTable, but I don't know the purpose of
that method so I don't want to play around with it (unless someone
explains it to me).

> I have copied the entire 
> mcs/class/Mono.Data.SqliteClient/Mono.Data.SqliteClient diretory into my 
> project, applied the fix below, and recompiled, and it now works as I would 
> expect.

I'd want to understand more about how you're putting the values into the
database and how you're reading them before looking more into the patch.
 Also, in Windows, are you using the Mono runtime or MS?  I'm just not
sure where the bug really is, in Mono.Data.SqliteClient or elsewhere.

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