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." _______________________________________________ Mono-list maillist - [email protected] http://lists.ximian.com/mailman/listinfo/mono-list
