Problem with inserting DATE field in Sql.Builder

[yarecky1]

When I create SQL query with condition:

PetaPoco.Sql.Builder.Append("WHERE P.[date] >= @0", /see below examples/)

like

P.[date] >= @0", New Date(2017, 4, 3)
or
P.[date] >= @0", Now.AddDays(-30).Date

it transform to SQL like below (using LastCommand) and entire query returns proper result

-> @0 [DateTime] = "2017-04-03 00:00:00"

However if I write

P.[date] >= @0", Now.AddDays(-30)
or 
P.[date] >= @date", New With {.date = Now.AddDays(-30)}

I receive

-> @0 [DateTime] = "2017-04-03 12:04:56"

and this query raises exception (see below). Of course this properly hardcoded version (without argument) also works:

P.[date] >= #2017-04-03 12:04:56#

but this hardcoded version with single quotes around date and with zeros in hour/minute/second part of date gives exactly the same exception like version with arguments above

P.[date] >= '2017-04-03 00:00:00'

How can I solve it?

Exception:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in PetaPoco.dll

  ErrorCode=-2147217913
  HResult=-2147217913
  Message=Data type mismatch in criteria expression.
  Source=Microsoft Access Database Engine
  StackTrace:
       w System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       w System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       w System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       w System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       w System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       w System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
       w System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader()
       w PetaPoco.Database.<Query>d__49`1.MoveNext()
       w System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       w System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
       w PetaPoco.Database.Fetch[T](String sql, Object[] args)
       w PetaPoco.Database.Fetch[T](Sql sql)

[pleb]

Is P.[date] a string? If so, you will need to .ToString() your date before adding it as a param

[yarecky1]

Yes, field is a date, no special format.

For simplicty I created fresh new table called pocodateissue with 6 date fields and poco insert sql command.

Dim sql = PetaPoco.Sql.Builder.
Append("INSERT INTO pocodateissue").
Append(" (date1, date2, date3, date4, date5, date6)").
Append("VALUES (").
Append(" @date1,", New With {.date1 = Now}).
Append(" @date2,", New With {.date2 = New Date(2017, 1, 1)}).
Append(" @date3,", New With {.date3 = New Date(2017, 1, 1, 12, 23, 36)}).
Append(" @date4,", New With {.date4 = Now.AddDays(-1)}).
Append(" @date5,", New With {.date5 = Now.AddDays(-1).Date}).
Append(" @0", New Date(2017, 1, 1, 12, 23, 36)).
Append(")")

What I see?

INSERT INTO pocodateissue
 (date1, date2, date3, date4, date5, date6)
VALUES (
 @0,
 @1,
 @2,
 @3,
 @4,
 @5
)
	 -> @0 [DateTime] = "2017-05-04 09:11:41"
	 -> @1 [DateTime] = "2017-01-01 00:00:00"
	 -> @2 [DateTime] = "2017-01-01 12:23:36"
	 -> @3 [DateTime] = "2017-05-03 09:11:41"
	 -> @4 [DateTime] = "2017-05-03 00:00:00"
	 -> @5 [DateTime] = "2017-01-01 12:23:36"

Exception?

System.Data.OleDb.OleDbException was unhandled
  ErrorCode=-2147217913
  HResult=-2147217913
  Message=Data type mismatch in criteria expression.
  Source=Microsoft Access Database Engine
  StackTrace:
       w System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       w System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       w System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       w System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       w System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       w System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
       w PetaPoco.Database.Execute(String sql, Object[] args)
       w PetaPoco.Database.Execute(Sql sql)

However I altered table changing all date fields into string fields and execute the same query. It executed without error. What I see in database?

date1 -> 2017-05-04 09:25:17.458098000
date2 -> 2017-01-01 00:00:00
date3 -> 2017-01-01 12:23:36
date4 -> 2017-05-03 09:25:17.458098000
date5 -> 2017-05-03 00:00:00
date6 -> 2017-01-01 12:23:36

[pleb]

Hmm. Ok. Seems like maybe PetaPoco is converting the datetime to a string.

Some of the integration tests use a datetime here, so it's looking more likely to not be an issue with PetaPoco (not ruling that out though).

Check to make sure there's not a mapping for DateTime to String somewhere in the code.

You could attach the debugger and check the Mappers._mappers collection. Or try a call to Mapper.RevokeAll() before you run the insert to see if it works with datetime columns.

[yarecky1]

    <TableName("Poco")>
    Private Class Poco
        Property Id As Integer
        Property DateTimeField As Date
    End Class

    Private Sub TestOnPetaPoco()

        Dim db = DatabaseConfiguration.
            Build.
            UsingConnectionString(_DbContext.GetConnectionStringAccess).
            UsingProvider(Of Providers.MsAccessDbDatabaseProvider).
            Create

        'While Not Debugger.IsAttached
        'Threading.Thread.Sleep(1000)
        'End While

        Try
            Dim poco1 As New Poco With {.DateTimeField = Now}
            Mappers.RevokeAll()
            db.Insert(poco1)
        Catch ex As Exception
            Stop
        End Try

    End Sub

Still the same exception. If in Poco class I leave Property DateTimeField As Date but in database I change the field from Date to String query works inserting values 2017-05-09 18:57:34.649504900

Mappers._mappers gives me:

? Mappers._mappers
Count = 0

? Mappers._mappers.tostring
"System.Collections.Generic.Dictionary`2[System.Object,PetaPoco.IMapper]"

I don't know how I can see private properties. As you also see I again created minimal example completely from scratch so for sure there is no mapping date to string or whatever else.

[yarecky1]

@pleb So, what's the status?

[pleb]

Sorry @yarecky1 I don't have access installed. There's the PetaPoco.Tests.Integration.x86 project which has all the integration tests for Access. If you like, try running that it make sure nothing is broken

[hpposch]

I faced the same issue. Under C# I used DateTime.Now which throws an Exception when i tried to insert this.

So i build a Helper Class for that: public static class PetaPocoDateTime { public static DateTime Now { get { return new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Hour, DateTime.Now.Minute, DateTime.Now.Second, DateTimeKind.Utc); } } }

I can now call PetaPocoDateTime.Now and get a valid DateTime that can be inserted.

Hope this can help someone in the future.

[yarecky1]

@hpposch Interesting approach. However I suppose we shouldn't create helper classes or any other workarounds to get absolutely basic functions or behaviours. That's why I switched from PetaPoco to another engine where everything works as expected.

[hpposch]

I understand what you mean. Always keep your code clean :)

May i ask you what engine you use and what target Database?

[hpposch]

BTW: Another option would be to implement an extionson method for DateTime in PetaPoco Source so you can use it out of the Box when you include PetaPoco. And then inside the code it can be converted to the correct format.

But thats stuff for some PetaPoco devs :)

Maybe i'll do it if there is time

[yarecky1]

@hpposch I use Linq2Db and both MS Access and MS SQL at work.

[pleb]

There are integration tests for Access. Can somebody add a test to show this is broken?