ODBC Dates Wrong on Mac talking to MsSQL DB

  1. 3 months ago

    Brock N

    Dec 11 Pre-Release Testers, Xojo Pro
    Edited 3 months ago

    I'm using the ODBC driver on my mac to access a MsSQL Database but all the date properties are wrong:

    For example, I'm suppose to get: 2016-08-03 09:46:41.360
    But instead I'm getting: 7411-11-14 05:02:56
    (which has a total seconds of -293898913024)

    Is there something I can do to fix these dates and get their actual value?

  2. Dave S

    Dec 11 San Diego, California USA

    I would say one of two things may be happening

    • One the variable you are reading these "total seconds" into is too small (ie. Integer Overflow is happening)
    • what ever is populating the data (assuming it is not Xojo) has a different time offset than Xojo does
  3. Anthony D

    Dec 11 Pre-Release Testers, Xojo Pro Seattle WA

    Brock - is this a 64bit app running on OS X? I know when using the Xojo ODBC driver to connect to a FileMaker Pro database, there’s some issues with flipped bytes in the raw recordset data returned from the Xojo ODBC plugin:

    ODBC recordset date in 32 bit E2 07 09 00 14 00 10 00 09 00 2A 00 00 00 00 00
    
    ODBC recordset date in 64 bit 07 E2 00 09 00 14 00 10 00 09 00 2A 00 00 00 00

    (this is from looking at the values in the debugger)

    Maybe something similar is occurring?

  4. Brock N

    Dec 11 Pre-Release Testers, Xojo Pro

    @Anthony D Brock - is this a 64bit app running on OS X? I know when using the Xojo ODBC driver to connect to a FileMaker Pro database, there’s some issues with flipped bytes in the raw recordset data returned from the Xojo ODBC plugin:

    I am running in 64bit

    @Dave S One the variable you are reading these "total seconds" into is too small (ie. Integer Overflow is happening)

    I don't really have control over this as I'm using Xojo's built-in RecordSet class. IdxField returns a dateTime variant and the date is wrong.

    The only way I've been able to work around it is to convert all the date columns in my queries to use "Format" operator so it comes back as a string. But doing so is a pain cause I can't do things like "Select *"

  5. Dave S

    Dec 11 San Diego, California USA

    @Brock N I don't really have control over this as I'm using Xojo's built-in RecordSet class. IdxField returns a dateTime variant and the date is wrong.

    You may have more control than you believe... . If the msSQL datatype is inconsistent with what Xojo thinks is dateTime then you will get a wrong answer. So what msSQL datatype is the field in question?

    FYI (my opinion) Date and Time values should ALWAYS be stored in SQLDateTime format (as strings) to maintain the most compatiblity between platforms, database engines and future proofing. YYYYMMDD HH:MM:SS TZ

    This way there is no question about if the database has one interpetation and Xojo (or whatever) has another.

  6. Anthony D

    Dec 11 Pre-Release Testers, Xojo Pro Seattle WA

    Brock - I got those values from the built-in recordset class; it returns DatabaseField objects which have a "Value" property which returns a variant.

    I think the issue is the date data has the wrong byte order (it’s little-endian vs. big-endian or vice versa); if this is the case, then you could probably extend DatabaseField to have a "FixedDate" method which puts the native value in a memory block, fixes it’s byte-order, and then converts the corrected data back into a date object and returns it.

    Then you’d call

    someDate = rs.field("my_date_field").FixedDate
    
    vs. 
    
    someDate = rs.field("my_date_field").DateValue
    

    I haven’t had the time to look into this issue more deeply, and I’m not and expert at working with binary data so this is all "best guess".

    I know Dave is pretty proficient with binary data operations, so he may be able to verify or torpedo my conjecture about this being a byte order issue with the returned data from Xojo’s ODBC plugin.

    HTH!

  7. Dave S

    Dec 11 San Diego, California USA
    Edited 3 months ago

    Anthony ... what you pointed out looks like a mismatch in the ENDIAN property of how the data is stored (ie. the order of bytes). If you look at your example, they both contain the same bytes but in a different order. Another reason why I support the use of SQLDATE format

    And it seems that msSQL itself is not consistent with ENDIAN especially with DATE fields
    https://dba.stackexchange.com/questions/47115/understanding-ms-sql-server-date-types

  8. Anthony D

    Dec 11 Pre-Release Testers, Xojo Pro Seattle WA

    What it looks like to me too Dave. But the issue is, the data shown is what Xojo’s ODBC plugin is returning.

    The data varies between 64bit and 32 bit builds (on OS X - this doesn’t happen on Windows builds).

    This makes me feel like it’s a Xojo plugin bug - (target shouldn’t change byte order) - but I haven’t had time to really dig into it.

or Sign Up to reply!