datetime2 in Fabric has issues when submitting parametrized queries with datetime

sdebruyn commented 1 year ago

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 1 year ago

Could you post an ODBC trace?

sdebruyn commented 1 year ago

[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 1 year ago

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 1 year ago

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 1 year ago

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

gordthompson commented 1 year ago

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)

mkleehammer / pyodbc

datetime2 in Fabric has issues when submitting parametrized queries with datetime #1221