On Thu, 2009-06-04 at 21:25 -0700, [email protected] wrote:
> I modified the "public virtual SqlStatement GetLiteral(object
> literal)" method in the SqlProvider class to return the DateTime
> objects in the required format. The next snag I hit was that the
> DateTime strings are incorrectly formatted on updates. Are there
> different spots to set the parameter formats for updates and selects?
Yes, and I'm surprised that updates are causing issues.
The real difference isn't between updates and selects, but between
parameterized versus literal queries, e.g.
// literal:
from e in db.Employees
where e.BirthDate < DateTime.Now
select e;
// parameterized
var time = DateTime.Now;
from e in db.Employees
where e.BirthDate < time
select e;
If you hook up a logger to TextWriter.Log you can see the difference, as
the literal queries will produce e.g.
SELECT [EmployeeID], [LastName], [FirstName], [Title], [BirthDate],
[HireDate], [Address], [City], [Region], [PostalCode],
[Country], [HomePhone], [Photo], [Notes], [TitleOfCourtesy],
[PhotoPath], [Extension], [ReportsTo]
FROM [dbo].[Employees]
WHERE ([BirthDate] < '2009-06-05T07:11:38')
-- Context: SqlServer Model: AttributedMetaModel Build: 0.19.0.0
while the parameterized version generates:
SELECT [EmployeeID], [LastName], [FirstName], [Title], [BirthDate],
[HireDate], [Address], [City], [Region], [PostalCode],
[Country], [HomePhone], [Photo], [Notes], [TitleOfCourtesy],
[PhotoPath], [Extension], [ReportsTo]
FROM [dbo].[Employees]
WHERE ([BirthDate] < @time)
-- @time: Input DateTime (Size = 0; Prec = 0; Scale = 0) [6/5/2009
7:11:38 AM]
-- Context: SqlServer Model: AttributedMetaModel Build: 0.19.0.0
While the @time value of the parameterized version looks bad, it should
be correct, as it should be using SQL parameters. See
QueryRunner.Upsert():
private void Upsert(object target, UpsertQuery insertQuery)
{
insertQuery.Target = target;
var dataContext = insertQuery.DataContext;
using (var dbCommand = insertQuery.GetCommand())
...
and ParameterizedQuery.GetCommand() (which is called via
insertQuery.GetCommand()):
public override ITransactionalCommand GetCommand()
{
ITransactionalCommand transactionalCommand = base.GetCommand(true);
foreach (var inputParameter in InputParameters)
{
var dbParameter =
transactionalCommand.Command.CreateParameter();
dbParameter.ParameterName =
DataContext.Vendor.SqlProvider.GetParameterName(inputParameter.Alias);
dbParameter.SetValue(inputParameter.GetValue(Target),
inputParameter.ValueType);
transactionalCommand.Command.Parameters.Add(dbParameter);
}
return transactionalCommand;
}
so this creates named parameters, and calls the
IDbDataParameterExtensions.SetValue() extension method, so the above
foreach should amount to:
var dbParameter = ...
dbParameter.ParameterName = ...
dbParameter.Value = inputParameter.GetValue(Target);
transactionCommand.Command.Parameters.Add(dbParameter);
The reason this matters is that the DbCommand.Command.Parameters
collection is getting the actual object value (e.g. the DateTime
object), and not some string-ized representation. Unlike with literal
queries, for parameterized queries DbLinq *never* usees .ToString() to
build the SQL.
Consequently, I'm rather surprised that your updates are having
problems, as the updates should be going through the parameterized query
mechanism, thus the MySQL client code should be responsible for handling
the DateTime values.
You might try debugging this further to see if I'm mistaken about this
somewhere (I hope not), or writing a DbLinq-less test against your DB
(using just the MySQL ADO.NET client) to see if parameterized queries
using DateTime values actually works, e.g.
var command = new MySqlCommand("SELECT * FROM Employees WHERE BirthDate
< ?Date");
command.Parameters.Add(new MySqlParameter {
ParameterName = "?Date",
Value = DateTime.Now
});
var reader = command.ExecuteReader();
while (reader.Read()) {
Console.WriteLine("{0}", reader["BirthDate"]);
}
I would expect something like the above to work. If it does, then
there's a DbLinq problem (though I'm not sure where -- you'd have to
debug). If the above doesn't work, then that looks like a MySQL bug.
- Jon
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"DbLinq" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/dblinq?hl=en
-~----------~----~----~----~------~----~------~--~---
