Problems with SQL Server timestamps on Windows

[TWAC]

I am using this snippet for testing on Windows with the "SQL Server" driver.

now = datetime.datetime.now().replace(microsecond=0)
curs.execute("select ?", [now])

The default value for timestamp digits of 6 complains about Invalid scale value. In a MSSQL example MAXIMUM_SCALE is 3, and using this value stops the complaining about Invalid scale value. Instead it starts complaining about Datetime field overflow.

From what i can tell the turbodbc element size is the same as the ODBC Column Size. The size of this should according to the link not be the size of the struct, but the length of an arbitrary string representation of the datetime. In the MSSQL example COLUMN_SIZE is 23. Using this value for element size gets rid of "Datetime field overflow", and the example works.

However, when using microseconds, the Datetime field overflow comes back.

now = datetime.datetime.now().replace(microsecond=1)
curs.execute("select ?", [now])

However when microsecond is a multiple of 1000, it works.

now = datetime.datetime.now().replace(microsecond=1000)
curs.execute("select ?", [now])

[MathMagique]

Thanks for reporting!

[TWAC]

The fun continues with Invalid time format.

now = datetime.datetime.now().replace(microsecond=0)
curs.executemany("select ?", [(now,), (now,)])

Traceback (most recent call last):
  File "C:\Python36\lib\site-packages\turbodbc\exceptions.py", line 31, in wrapper
    return f(*args, **kwds)
  File "C:\Python36\lib\site-packages\turbodbc\cursor.py", line 91, in executemany
    buffer.flush()
turbodbc_intern.Error: ODBC error
state: 22007
native error code: 0
message: [Microsoft][ODBC SQL Server Driver]Invalid time format

[MathMagique]

This particular driver is starting to annoy me ;-)

[TWAC]

Is the turbodbc element size used for both memory allocations and ODBC Column Size? Perhaps they need to be separated, that is feed this value from a separate path.

[MathMagique]

Yes, currently it is the same, and this seems to violate the specification you referenced (funny thing that the column size is ignored for most data types). Splitting element size and column size seems necessary.

[TWAC]

The driver "ODBC Driver 13 for SQL Server" does not have these problems, it also performs better in my testing. "SQL Server" is included in windows installations, so it is nicer for deployment, but other than that probably no reason to prefer it.

[MathMagique]

That's good to hear. Still I'll keep this issue open and see if one can do some generic database driver capability detection with respect to the fraction second precision.

[will133]

I'm pretty interested in getting this bug resolved as I seem to get the same error on Linux talking to a MSSQL server. I'm trying to convert something from using pyodbc to turbodbc and this issue essentially is preventing me from doing so. So, it's quite a bummer since turbodbc seems better in almost all the other cases.

Is there any reason why the digit settings for the timestamp digits description is fixed btw? (I'm an ODBC newbie so I only looked at the relevant code briefly.) In MSSQL, there's datetime and datetime2 also where they have different levels of second precision. I'm not sure how turbodbc would handle that case.