string>255 in length with fast_executemany=True doesn't provide appropriate exception message

[jwcook23]

Environment

• Python: 3.9.1
• pyodbc: 4.0.30
• OS: Windows 64-bit
• DB: SQL Server
• driver: ODBC Driver 17 for SQL Server

Issue

I would expect a pyodbc.ProgrammingError of "...Invalid object name..." to always be raised if a table doesn't exist. Instead for string values>255 in length, a pyodbc.ProgrammingError of "...String data, right truncation..." is raised if fast_executemany=True.

The correct exception message is provided if fase_executemany=False or cursor.setinputsize is utilized.

import pyodbc

connection = pyodbc.connect(
    driver='ODBC Driver 17 for SQL Server', server='localhost', database='master',
    autocommit=False, trusted_connection='yes'
)
cursor = connection.cursor()

statement = """
INSERT INTO
##NonExistantTable (
    [ColumnA]
) VALUES (
    ?
)
"""

# expected behavior
cursor.fast_executemany = True
sz = 255
args = [['a'*sz]]
try:
    cursor.executemany(statement, args)
except pyodbc.ProgrammingError as err:
    # True
    print('Invalid object name' in str(err))

# unexpected behavior
cursor.fast_executemany = True
sz = 256
args = [['a'*256]]
try:
    cursor.executemany(statement, args)
except pyodbc.ProgrammingError as err:
    # False, err = ProgrammingError('String data, right truncation: length 512 buffer 510', 'HY000')
    print('Invalid object name' in str(err))

# work-around: fast_executemany=False
cursor.fast_executemany = False
sz = 256
args = [['a'*sz]]
try:
    cursor.executemany(statement, args)
except pyodbc.ProgrammingError as err:
    # True
    print('Invalid object name' in str(err))

# work-around: setinputsize
cursor.fast_executemany = True
sz = 256
args = [['a'*sz]]
cursor.setinputsizes([(pyodbc.SQL_VARCHAR,sz*2,0)])
try:
    cursor.executemany(statement, args)
except pyodbc.ProgrammingError as err:
    # True
    print('Invalid object name' in str(err))

[v-chojas]

This is because fast_executemany attempts to discover the parameter layout of the query before executing it. The SQLDescribeParam fails (it can fail for other reasons than an invalid query) so it defaults to a varchar(255), and then gives the truncation error when it encounters a column longer than that. This is before the query is executed.

Anyway you should use fast_executemany only when your existing code is already working and you need to improve the performance, as it has some other limitations.

[gordthompson]

Closing this as the related issue has been closed.

jwcook23/mssql_dataframe#1