mkleehammer/pyodbc

datetime2 in Fabric has issues when submitting parametrized queries with datetime

Closed this issue · 6 comments

sdebruyn commented

I created a complete repro here: https://github.com/sdebruyn/fabric-dt2issue-repro/blob/main/repro.ipynb

This code is failing when you are using it with Microsoft Fabric Data Warehouses (announced today at Build).

With SQL Server, Azure SQL and Azure Synapse we could use the datetime type in T-SQL and there the parametrized datetime (Python) objects worked just fine.

Is there some kind of type mapping that I am missing?

v-chojas commented

Could you post an ODBC trace?

sdebruyn commented 
[ODBC][21100][1684876192.131880][SQLAllocHandle.c][578]
		Entry:
			Handle Type = 3
			Input Handle = 0x7fc037061a00
[ODBC][21100][1684876192.132029][SQLAllocHandle.c][1123]
		Exit:[SQL_SUCCESS]
			Output Handle = 0x7fc037040c00
[ODBC][21100][1684876192.146368][SQLFreeStmt.c][144]
		Entry:
			Statement = 0x7fc037040c00
			Option = 0
[ODBC][21100][1684876192.146564][SQLFreeStmt.c][266]
		Exit:[SQL_SUCCESS]
[ODBC][21100][1684876192.151307][SQLPrepareW.c][165]
		Entry:
			Statement = 0x7fc037040c00
			SQL = [insert into repro_dt2_issue_schema2.repro_dt2_issue_table(id, name, dt2) values (?, ?, ?)][length = 89]
[ODBC][21100][1684876192.151432][SQLPrepareW.c][346]
		Exit:[SQL_SUCCESS]
[ODBC][21100][1684876192.151588][SQLNumParams.c][144]
		Entry:
			Statement = 0x7fc037040c00
			Param Count = 0x7ff7b6dd7e26
[ODBC][21100][1684876192.151706][SQLNumParams.c][231]
		Exit:[SQL_SUCCESS]
			Count = 0x7ff7b6dd7e26 -> 3
[ODBC][21100][1684876192.151913][SQLBindParameter.c][217]
		Entry:
			Statement = 0x7fc037040c00
			Param Number = 1
			Param Type = 1
			C Type = 1 SQL_C_CHAR
			SQL Type = 2 SQL_NUMERIC
			Col Def = 1
			Scale = 0
			Rgb Value = 0x7fc035cf60d0
			Value Max = 0
			StrLen Or Ind = 0x7fc035cfe478
[ODBC][21100][1684876192.151974][SQLBindParameter.c][434]
		Exit:[SQL_SUCCESS]
[ODBC][21100][1684876192.153424][SQLBindParameter.c][217]
		Entry:
			Statement = 0x7fc037040c00
			Param Number = 2
			Param Type = 1
			C Type = -8 SQL_C_WCHAR
			SQL Type = -9 SQL_WVARCHAR
			Col Def = 24
			Scale = 0
			Rgb Value = 0x113999190
			Value Max = 24
			StrLen Or Ind = 0x7fc035cfe4e0
[ODBC][21100][1684876192.153584][SQLBindParameter.c][434]
		Exit:[SQL_SUCCESS]
[ODBC][21100][1684876192.153654][SQLBindParameter.c][217]
		Entry:
			Statement = 0x7fc037040c00
			Param Number = 3
			Param Type = 1
			C Type = 11 SQL_C_TIMESTAMP
			SQL Type = 11 SQL_TIMESTAMP
			Col Def = 27
			Scale = 7
			Rgb Value = 0x7fc035cfe578
			Value Max = 0
			StrLen Or Ind = 0x7fc035cfe548
[ODBC][21100][1684876192.153757][SQLBindParameter.c][434]
		Exit:[SQL_SUCCESS]
[ODBC][21100][1684876192.153803][SQLExecute.c][187]
		Entry:
			Statement = 0x7fc037040c00
[ODBC][21100][1684876192.180511][SQLExecute.c][357]
		Exit:[SQL_ERROR]
		DIAG [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]A precision value must be specified. The range of valid values is an integer value between 0 and 6.

[ODBC][21100][1684876192.180794][SQLGetDiagRecW.c][535]
		Entry:
			Statement = 0x7fc037040c00
			Rec Number = 1
			SQLState = 0x7ff7b6dd7e44
			Native = 0x7ff7b6dd7e2c
			Message Text = 0x7fc0388b7400
			Buffer Length = 1023
			Text Len Ptr = 0x7ff7b6dd7e42
[ODBC][21100][1684876192.180896][SQLGetDiagRecW.c][596]
		Exit:[SQL_SUCCESS]
			SQLState = [42000]
			Native = 0x7ff7b6dd7e2c -> 24598 (32 bits)
			Message Text = [[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]A precision value must be specified. The range of valid values is an integ]
[ODBC][21100][1684876192.180996][SQLFreeStmt.c][144]
		Entry:
			Statement = 0x7fc037040c00
			Option = 3
[ODBC][21100][1684876192.181045][SQLFreeStmt.c][266]
		Exit:[SQL_SUCCESS]
[ODBC][21100][1684876192.181143][SQLFreeStmt.c][144]
		Entry:
			Statement = 0x7fc037040c00
			Option = 0
[ODBC][21100][1684876192.181237][SQLFreeStmt.c][266]
		Exit:[SQL_SUCCESS]
[ODBC][21100][1684876192.181369][SQLFreeHandle.c][387]
		Entry:
			Handle Type = 3
			Input Handle = 0x7fc037040c00
[ODBC][21100][1684876192.181478][SQLFreeHandle.c][490]
		Exit:[SQL_SUCCESS]
v-chojas commented

If you see this same bind (with the scale of 7) when testing against SQL Server/Azure SQL DB/etc. and it works, this is likely to be a server-side limitation.

		Entry:
			Statement = 0x7fc037040c00
			Param Number = 3
			Param Type = 1
			C Type = 11 SQL_C_TIMESTAMP
			SQL Type = 11 SQL_TIMESTAMP
			Col Def = 27
			Scale = 7
			Rgb Value = 0x7fc035cfe578
			Value Max = 0
			StrLen Or Ind = 0x7fc035cfe548
[ODBC][21100][1684876192.153757][SQLBindParameter.c][434]
		Exit:[SQL_SUCCESS]```
prdpsvs commented

What if the database only supports datetime2(6) instead of datetime2(7)?
do something like this?
tup = struct.unpack("<6hI", dt2_value) # e.g., (2017, 5, 30, 8, 59, 37, 0, 665039700)
return datetime(tup[0], tup[1], tup[2],
hour=tup[3], minute=tup[4], second=tup[5],
microsecond=math.floor(tup[6] / 1000.0 + 0.5))

v-chojas commented

You may be able to use setinputsizes function to specify precision/scale explicitly.

gordthompson commented

You may be able to use setinputsizes function to specify precision/scale explicitly.

Yes, that works.

crsr.setinputsizes([(pyodbc.SQL_TYPE_TIMESTAMP, 27, 6)])
pyodbc_issue_12 1ca4-1ff0	EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
        HSTMT               0x0000003033A70080
        UWORD                        1 
        SWORD                        1 <SQL_PARAM_INPUT>
        SWORD                       11 <SQL_C_TIMESTAMP>
        SWORD                       93 <SQL_TYPE_TIMESTAMP>
        SQLULEN                   27
        SWORD                        6 
        PTR                0x0000003030E93DA8
        SQLLEN                     0
        SQLLEN *            0x0000003030E93D78 (16)