Cursor.fast_executemany with SQL Server temporary table mis-handles Unicode under Python3

[gordthompson]

The Cursor.fast_executemany feature introduced in 4.0.18/19 does not seem to handle Unicode characters correctly under Python3 when working with a SQL Server temporary table. The test code

import pyodbc
import sys
print('Python version ' + sys.version)
print('pyodbc version ' + pyodbc.version)

conn_str = (
    r'DRIVER=ODBC Driver 11 for SQL Server;'
    r'SERVER=(local)\SQLEXPRESS;'
    r'DATABASE=myDb;'
    r'Trusted_Connection=yes;'
)
cnxn = pyodbc.connect(conn_str, autocommit=True)
crsr = cnxn.cursor()
crsr.fast_executemany = False

crsr.execute("CREATE TABLE #tmp (id INT IDENTITY PRIMARY KEY, txt NVARCHAR(1))")

params = [('Ώ',), ('π',), ('α',)]
crsr.executemany("INSERT INTO #tmp (txt) VALUES (?)", params)

print(crsr.execute("SELECT txt FROM #tmp ORDER BY id").fetchall())

works as expected, producing

C:\Users\gord\PycharmVirtualenvs\py3pyodbc\Scripts\python.exe C:/Users/gord/PycharmProjects/py3pyodbc_test/main.py
Python version 3.6.2 (v3.6.2:5fd33b5, Jul  8 2017, 04:14:34) [MSC v.1900 32 bit (Intel)]
pyodbc version 4.0.19
[('Ώ', ), ('π', ), ('α', )]

Process finished with exit code 0

but when I enable the new feature with crsr.fast_executemany = True I get

C:\Users\gord\PycharmVirtualenvs\py3pyodbc\Scripts\python.exe C:/Users/gord/PycharmProjects/py3pyodbc_test/main.py
Python version 3.6.2 (v3.6.2:5fd33b5, Jul  8 2017, 04:14:34) [MSC v.1900 32 bit (Intel)]
pyodbc version 4.0.19
[('?', ), ('p', ), ('a', )]

Process finished with exit code 0

SQL Profiler shows the following

exec sp_describe_undeclared_parameters N'INSERT INTO #tmp (txt) VALUES (@P1)'
exec sp_prepare @p1 output,N'@P1 varchar(255)',N'INSERT INTO #tmp (txt) VALUES (@P1)',1
exec sp_execute 1,'?'
exec sp_execute 1,'p'
exec sp_execute 1,'a'

Notice that the parameter type is mis-identified as varchar(255) when it really should be nvarchar(1).

[gordthompson]

Update: The same test with a permanent table does not reproduce the issue. Perhaps this is a limitation of sp_describe_undeclared_parameters when working with temporary tables.

[v-chojas]

Perhaps this is a limitation of sp_describe_undeclared_parameters when working with temporary tables.

Yes, it appears that the SP does not support temporary tables. Running the call manually after creating the temp table results in an "invalid object name" error.

Note that this would succeed if you use an older ODBC driver, like SQL Server (SQLSRV32.DLL), since it does not use that SP to implement SQLDescribeParam --- it uses the deprecated-but-working FMTONLY instead.

[gordthompson]

Yes, it appears that the [sp_describe_undeclared_parameters] SP does not support temporary tables.

Bummer. It seems to me that a common usage case would be to

• create a #temporary table,
• upload a big bunch of rows to that table, and then
• MERGE those rows into an existing permanent table.

Given that we already know the column types when we create the #temporary table, perhaps we can specify them explicitly using .setinputsizes (instead of implicitly relying on SQLDescribeParam) once #280 gets sorted out ...?

[v-chojas]

As a workaround, you can use a global (i.e. ## prefix) instead of local temporary table. sp_describe_undeclared_parameters seems to work in that case, at least for your example.

[v-chojas]

You may also try the preview of the next msodbcsql driver, and set ColumnEncryption=Enabled in your connection string or DSN, which should also work around the issue with the SP.

[mkleehammer]

Should we close this? Keep it open as long as you need a discussion area. I'm just going through the issues to make the next build.

[gordthompson]

@v-chojas - I see that #280 has been closed because "the functionality is included in the parameter array refactoring". Will that refactoring offer any benefit for this issue, e.g., using .setinputsizes as suggested in my earlier comment?

[v-chojas]

@gordthompson Yes, you should be able to use the setinputsizes extension to specify the SQL type of the parameters.

[gordthompson]

@v-chojas

You may also try the preview of the next msodbcsql driver, and set ColumnEncryption=Enabled in your connection string or DSN

Now that "ODBC Driver 17 for SQL Server" has been officially released, I tried your suggestion and it seems to be working fine. I have added a tip to the Wiki.