pyodbc: Cursor throws an error when trying to insert in SQL Server database when fast_executemany flag is set

Still no dice on 4.0.24.

I see the comment on #450, any chance this will see a release soon?

We ran into this issue as well. executemany() doesn’t seem to handle empty string. Our environment is:

python: 2.7.5 pyodbc: 4.0.23 sqlalchemy: 1.2.5 pandas: 0.18.1 ODBC Driver 17 for SQL Server Ubuntu 16.04

@eddyizm he is not using SQL Server, but I would not be surprised if the ODBC driver does not support parameter arrays; fast_executemany was originally designed for and works best with ODBC Driver 17 for SQL Server, although it may also be usable with other drivers.

@gordthompson #467 seems to relate more to null values than empty string values. I am testing it now.

Best workaround so far seems to be to replace empty strings with null or whitespace.

I can confirm that PR #467 fixes the issue I had with SQLAlchemy, pandas, and fast_executemany documented in my earlier comment of this thread.

Can someone provide a workaround for this issue? I tried using old versions and changing driver but no success. it gives different errors.

Not sure if this will help anyone else, but I was able to solve the problem by explicitly encoding the string data as ascii. Using the code provided by @v-chojas earlier in the thread, I am able to reproduce the error with this:

conn = pyodbc.connect([your connection string here], autocommit=True)
stmt = conn.cursor()
stmt.execute("DROP TABLE IF EXISTS fastexecutemany_test")
stmt.execute("CREATE TABLE fastexecutemany_test([index] BIGINT NULL, col1 VARCHAR(MAX) NULL)")
stmt.fast_executemany = True
stmt.executemany("INSERT INTO fastexecutemany_test([index], col1) VALUES(?, ?)", [[1,'foo'],[2,'bar']])
print (stmt.execute("SELECT * from fastexecutemany_test").fetchall())

Result:

Error: ('HY010', '[HY010] [Microsoft][ODBC Driver 13 for SQL Server]Function sequence error (0) (SQLParamData)')

But explicitly encoding the string data as ascii allowed the code to execute correctly. This is the code I used:

stmt.executemany("INSERT INTO fastexecutemany_test([index], col1) VALUES(?, ?)", [[1,'foo'.encode('ascii')],[2,'bar'.encode('ascii')]])
print (stmt.execute("SELECT * from fastexecutemany_test").fetchall())

Result:

[(1, 'foo'), (2, 'bar')]

Environment: Python 3.6 pyodbc 4.0.23 connecting to an Azure-SQL database (ODBC Driver 13 for SQL Server) Windows 7, 64-bit

@v-chojas - Before posting I tried to reproduce the issue with plain pyodbc under Python 3.6.4 (Windows, 64-bit) but could not. That’s why I went with the pandas code and the ODBC logs in case they gave a hint as to what pandas (or perhaps SQLAlchemy) was doing differently.