This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hi, This isn't really a lightspeed problem, (however, Lightspeed might have away around it!) more whether anyone has come across this (and solved it) as it must be something people do all the time. C# DateTime format is ddMMyyyy, mySQL is yyyyMMdd Trying to do a LINQ query:
Records should be retrieved, but because the C# DateTime is a different format to mySQL, no records are returned. Is there a whizzy way around this problem? Thanks |
|
|
Your query above really means C#dateTime == row.databaseDateTime so you could have a time resolution problem. You might need to add a small time slop to cover stray milliseconds. Read the docs for the MySQL date fields -- it's a mess. where (C#dateTime.AddSeconds(1) >= row.databaseDateTime && C#dateTime.AddSeconds(-1) <= row.databaseDateTime); Or you may have a time zone problem. Make sure it's Utc on both sides or that the timezones match. If you're UTC and using the LightSpeed date fields (CreatedOn, UpdatedOn...) you'll want to set AutoTimestampMode.Utc on the LightSpeedContext as well. |
|
|
Hi, The problem is that I convert the string into C# dateTime and then use LINQ to retrieve data where the start and end date (held in database table) is between the C# datetime. This should retrieve at least 1 record from the database, however, none is returned. Having Lightspeed display the generated SQL (via Logger), the date is being converted back to dd/MM/yyyy format rather than keeping it as yyyy/MM/dd Anyone else had this problem.....does Lightspeed have a work around this problem? |
|
|
The generated SQL actually contains a parameter and is sent with a DateTime value. When displaying the SQL, the logger substitutes in a printable version of the DateTime value. But the DateTime value is sent to MySQL as a DateTime, NOT as a string, so the way the logger formats it for printing should not be an issue. You can see this a bit more clearly by setting LightSpeedContext.VerboseLogging = true -- this will display the parameters, their values (again stringised of course) and their data types. As chadw notes your problem is probably in the comparison:
This will match only if
|
|
|
Chaps, Apologies, my question was not well explained. Here goes..... MySQL table has 2 datetime fields - a dateofissue and dateofexpiry The user inputs a date on the view (dd/mm/yyyy). This is passed to the controller as C# DateTime - temporaryevent_EndDate in the LINQ statement My usings are:
The LINQ statement is:
This generates the following SQL:
This return no rows from the database, but should return the record contained in the table. The record within the table is:
If I take the generated SQL and change 17/05/2012 00:00:00 to 2012/05/17 00:00:00, which is the native datetime format of MySQL, then the record in the database is retrieved. Thanks for your help |
|
|
To reiterate, what is printed in the logger is NOT the generated SQL. It is the generated SQL with printable representations of the parameter values substituted. The actual generated SQL looks like this:
Notice that the actual SQL contains parameters for the user id and temporary event date. Using the verbose logging settings, we can see the types of these parameters:
Again, the formatting of the p1 DateTime value is just the way the logger prints it. It is sent to MySQL as a DateTime parameter, NOT as a string. (Can you confirm that you see the same thing if you turn on LightSpeedContext.VerboseLogging?) So whatever the problem is, I don't think it can be that we're formatting the date-time string incorrectly for MySQL, because we don't format the date-time string when sending it to the database. We just send a date-time value in a parameter. I've tried running your LINQ statement against a MySQL database containing the values you mention for your test data and I haven't been able to reproduce the problem -- in my tests, the query matched the record as expected. So I'm very puzzled as to what's going on here. Could you post the UserPersonalLicenseDetail class definition and the CREATE TABLE statement for your MySQL table, so we can try to figure out where our test case is different from yours? Thanks! More about logging I realise you're concerned about the format of the SQL we're sending to the database so I thought I would add some background on how you can diagnose this in more detail. As mentioned, the built-in loggers do NOT show the real SQL; they show the SQL with the parameter values substituted back in to make it more human-readable. However, when you need to diagnose problems, you can delve deeper than this by implementing your own logger. To do this, implement the ILogger interface. The ILogger.LogSql method receives a CommandLog object which contains the truth about the query. The default stringisation of CommandLog substitutes parameters, but by writing your own logger you can avoid this -- just print the CommandLog.CommandText property instead. Here's an example:
CommandLog also includes properties that let you examine the parameters and their values directly rather than relying on the default stringisation -- you may find these helpful in determining exactly what LightSpeed is sending to the database. (See the CommandLog class in the help file for more info.) Hope this helps! |
|
|
You can also swap your culture settings just to make sure something totally weird isn't going on. |
|
|
Hi Ivan, Thanks for the help. Here are the scripts you asked for: |
|
|
Hi Ivan, Thanks for the help. Here are the scripts you asked for: |
|
|
Ok, I think I might have found the problem: The fields in the database table were defined as DATE (I don't need the timestamp). I've changed the fields to DATETIME and the record is now retrieved as expected. I've also done a test where the database fields are DATE and then used DateTime.Date in the LINQ and that works too. Grrrr....Dates are a pain, suppose the moral of the story is to make sure the formats match at both the database field level and the c#/LINQ level Thanks |
|
|
That's great info, Douglas. We'll see if we can reproduce an issue with DATE columns and if so whether there's anything we can do about it at our end, though I can't promise anything. Glad you have a workaround though! |
|