This is a Mime message, which your current mail reader may not understand. Parts of the message will appear as text. To process the rest, use a Mime compatible reader or Base64 conversion utility.
--Next part of message (VA.000027d0.016955c5:trumphurst.com) Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Joshua Tauberer wrote: > 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? No. The Windows version is Microsoft, not Mono. CentOS comes with sqlite 3 installed, and that is what I have been using. > 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. Where does it try to do that? I can't find the code (and I would really like to, so I can perhaps make it work more rationally, without recompiling the library). > 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. That's an interesting assertion. I have searched the entire directory tree starting at mcs/class, and not found any calls to DateTime.FromFileTime, except in files System/System.Diagnostics/Process.cs, corlib/System.IO/File.cs, corlib/System.IO/FileSystemInfo.cs and corlib/Test/System/DateTimeTest.cs. WHat makes you think it is calling DateTime.FromFileTime? > 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. The columns are created as DateTimes. The data is inserted as an unambiguous string, of the form "YYYY-MM-DD hh:mm:ss". Under Windows, using .NET, it "just works". The data comes back in the row as a DateTime object. Under Mono on Linux, it doesn't work. The data comes back as an ambiguous string, which DateTime.Parse parses incorrectly (I should point out that I am in a region where the standard date format is DD/MM/YY). > > 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). As far as I understand it from my limited study of the source, GetSchemaTable is used by DataTable to determine the expected type of the data columns (amongst other things). > > 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. I enclose a test program as an attachment (please let me know if it doesn't get through the list). Running with the Mono.Data.SqliteClient.dll which comes with Mono, I get the following output... Sqlite Type is a date Type is System.String Value is 11/05/2006 11:45:00 DateTime is 05/11/2006 11:45:00 With my modified library, I get... Sqlite Type is a date Type is System.DateTime Value is 11/05/2006 11:45:00 DateTime is 11/05/2006 11:45:00 -- Nikki Locke, Trumphurst Ltd. PC & Unix consultancy & programming http://www.trumphurst.com/ --Next part of message (VA.000027d0.016955c5:trumphurst.com) Content-Type: application/octet-stream; name="Test.cs" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="Test.cs" dXNpbmcgU3lzdGVtOwp1c2luZyBTeXN0ZW0uRGF0YTsKdXNpbmcgU3lzdGVtLlRleHQ7CnVzaW5n IFN5c3RlbS5Db2xsZWN0aW9uczsKdXNpbmcgTW9uby5EYXRhLlNxbGl0ZUNsaWVudDsKCmNsYXNz IFRlc3QgewoJc3RhdGljIHZvaWQgTWFpbigpIHsKCQlUZXN0IHQgPSBuZXcgVGVzdCgpOwoJCXRy eSB7CgkJCXQuRXhlY3V0ZSgiZHJvcCB0YWJsZSB0ZXN0ZXIiKTsKCQl9IGNhdGNoIHsKCQl9CgkJ dC5FeGVjdXRlKCJjcmVhdGUgdGFibGUgdGVzdGVyICh0ZXN0ZGF0ZSBkYXRlKSIpOwoJCXQuRXhl Y3V0ZSgiaW5zZXJ0IGludG8gdGVzdGVyICh0ZXN0ZGF0ZSkgdmFsdWVzICgnMjAwNi0wNS0xMSAx MTo0NTowMCcpIik7CgkJdC5DaGVja1NxbGl0ZVR5cGVzKCJzZWxlY3QgKiBmcm9tIHRlc3RlciIp OwoJCQoJCURhdGFSb3dDb2xsZWN0aW9uIHJvd3MgPSB0LlF1ZXJ5KCJzZWxlY3QgKiBmcm9tIHRl c3RlciIpOwoJCW9iamVjdCBmaWVsZCA9IHJvd3NbMF0uSXRlbUFycmF5WzBdOwoJCURhdGVUaW1l IGQ7CgkJCgkJdHJ5IHsKCQkJZCA9IChEYXRlVGltZSlmaWVsZDsKCQl9IGNhdGNoIHsKCQkJZCA9 IERhdGVUaW1lLlBhcnNlKGZpZWxkLlRvU3RyaW5nKCkpOwoJCX0KCQlDb25zb2xlLldyaXRlTGlu ZSgiVHlwZSBpcyB7MH0iLCBmaWVsZC5HZXRUeXBlKCkuVG9TdHJpbmcoKSk7CgkJQ29uc29sZS5X cml0ZUxpbmUoIlZhbHVlIGlzIHswfSIsIGZpZWxkLlRvU3RyaW5nKCkpOwoJCUNvbnNvbGUuV3Jp dGVMaW5lKCJEYXRlVGltZSBpcyB7MH0iLCBkKTsKCQl0LkNsb3NlKCk7Cgl9CgoJcHJpdmF0ZSBT cWxpdGVDb25uZWN0aW9uIF9jb25uZWN0aW9uOwoKCXB1YmxpYyBUZXN0KCkgewoJCU9wZW4oImpv dXJuYWwuZGIiKTsKCX0KCglwdWJsaWMgdm9pZCBPcGVuKHN0cmluZyBmaWxlbmFtZSkgewoJCV9j b25uZWN0aW9uID0gbmV3IFNxbGl0ZUNvbm5lY3Rpb24oc3RyaW5nLkZvcm1hdCgiVVJJPWZpbGU6 ezB9LHZlcnNpb249MyIsIGZpbGVuYW1lKSk7CgkJX2Nvbm5lY3Rpb24uT3BlbigpOwoJfQoKCXB1 YmxpYyB2b2lkIENsb3NlKCkgewoJCV9jb25uZWN0aW9uLkNsb3NlKCk7Cgl9CgoJcHVibGljIHZv aWQgRXhlY3V0ZShzdHJpbmcgc3FsKSB7CgkJU3FsaXRlQ29tbWFuZCBjbWQgPSBfY29ubmVjdGlv bi5DcmVhdGVDb21tYW5kKCk7CgkJY21kLkNvbW1hbmRUZXh0ID0gc3FsOwoJCWNtZC5FeGVjdXRl Tm9uUXVlcnkoKTsKCQljbWQuRGlzcG9zZSgpOwoJfQoKCXB1YmxpYyB2b2lkIENoZWNrU3FsaXRl VHlwZXMoc3RyaW5nIHNxbCkgewoJCVNxbGl0ZUNvbW1hbmQgY21kID0gX2Nvbm5lY3Rpb24uQ3Jl YXRlQ29tbWFuZCgpOwoJCWNtZC5Db21tYW5kVGV4dCA9IHNxbDsKCQlTcWxpdGVEYXRhUmVhZGVy IHJlYWRlciA9IGNtZC5FeGVjdXRlUmVhZGVyKCk7CgoJCUNvbnNvbGUuV3JpdGVMaW5lKCJTcWxp dGUgVHlwZSBpcyBhIHswfSIsIHJlYWRlci5HZXREYXRhVHlwZU5hbWUoMCkpOwoJfQoKCXB1Ymxp YyBEYXRhUm93Q29sbGVjdGlvbiBRdWVyeShzdHJpbmcgc3FsKSB7CgkJU3FsaXRlQ29tbWFuZCBh YyA9IG5ldyBTcWxpdGVDb21tYW5kKHNxbCwgX2Nvbm5lY3Rpb24pOwoJCVNxbGl0ZURhdGFBZGFw dGVyIGRhID0gbmV3IFNxbGl0ZURhdGFBZGFwdGVyKGFjKTsKCQlEYXRhVGFibGUgZHQgPSBuZXcg RGF0YVRhYmxlKCk7CgkJZGEuRmlsbChkdCk7CgkJZGEuRGlzcG9zZSgpOwoJCWFjLkRpc3Bvc2Uo KTsKCQlyZXR1cm4gZHQuUm93czsKCX0KCn0K --Next part of message (VA.000027d0.016955c5:trumphurst.com) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline _______________________________________________ Mono-list maillist - [email protected] http://lists.ximian.com/mailman/listinfo/mono-list --Next part of message (VA.000027d0.016955c5:trumphurst.com)-- This is the end of the Mime message.
