fast_executemany: Passing decimal(m,n) parameter value as string can throw "String data, right truncation" error

[gordthompson]

Passing a negative parameter value as string for a decimal(m,n) column can throw a "String data, right truncation" error. Using setinputsizes does not resolve the issue.

print(platform.version())
# #126-Ubuntu SMP Wed Oct 21 09:40:11 UTC 2020

print("Python version: " + sys.version.replace("\n", ""))
# Python version: 3.6.5 (default, Apr  1 2018, 05:46:30) [GCC 7.3.0]

print(f"pyodbc version: {pyodbc.version}")
# pyodbc version: 4.0.30

connection_string = get_connection_string()
cnxn = pyodbc.connect(connection_string, autocommit=True)

print(
    f"ODBC driver: {cnxn.getinfo(pyodbc.SQL_DRIVER_NAME)} "
    f"[{cnxn.getinfo(pyodbc.SQL_DRIVER_VER)}]"
)
# ODBC driver: libmsodbcsql-17.6.so.1.1 [17.06.0001]

crsr = cnxn.cursor()

print(crsr.execute("SELECT @@VERSION").fetchval())
"""console output:
Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64) 
	Jun 15 2019 00:26:19 
	Copyright (C) 2017 Microsoft Corporation
	Express Edition (64-bit) on Windows 8.1 Pro 6.3 <X64> (Build 9600: )
"""

table_name = "gh_pyodbc_845"
crsr.execute(f"DROP TABLE IF EXISTS {table_name}")
crsr.execute(f"CREATE TABLE {table_name} (id int primary key, num decimal(4,2))")

crsr.fast_executemany = True
sql = f"INSERT INTO {table_name} (id, num) VALUES (?, ?)"
crsr.setinputsizes(
    [
        (pyodbc.SQL_INTEGER,),
        (pyodbc.SQL_DECIMAL, 4, 2),
    ]
)

data = [(1, "0.07")]
crsr.executemany(sql, data)
# no error

data = [(2, "-0.07")]
crsr.executemany(sql, data)
# pyodbc.ProgrammingError: ('String data, right truncation: length 10 buffer 8', 'HY000')

[gordthompson]

... also, a large-ish positive string value can cause the error, too:

data = [(3, Decimal("99.07"))]
crsr.executemany(sql, data)
# no error

data = [(4, "99.07")]
crsr.executemany(sql, data)
# pyodbc.ProgrammingError: ('String data, right truncation: length 10 buffer 8', 'HY000')

[v-makouz]

I'm able to reproduce it, and will look into it, it does seem to be pyODBC, as I don't see any errors in ODBC trace

[v-makouz]

The issue seems to be that there is a mismatch in usage of the input size, basically pyODBC detects "column size" based either on the table, or on setinputsizes, which in this case is going to be 4 either way. But because it's passed in as a string it tries to treat it as a string, in which case the number of characters should be the column size, but it isn't so you get the mismatch.

So if you use setinputsizes to give the width in maximum number of characters it should work

[gordthompson]

Ah, okay, so if we know that we'll be passing DECIMAL(m, n) column values as strings we should use setinputsizes to specify the parameter as string with width m+2 to allow for the decimal separator and (possibly) the negative sign.

crsr.setinputsizes(
    [
        (pyodbc.SQL_INTEGER,),
        (pyodbc.SQL_VARCHAR, 6),  # for DECIMAL(4, x)
    ]
)

[DSLituiev]

@gordthompson is Decimal a function from some package?
I tried from pyodbc import Decimal, but that doesn't seem valid

[DSLituiev]

figured it's from decimal import Decimal

[gordthompson]

@DSLituiev - If you're referring to data = [(3, Decimal("99.07"))] then yes, that's correct.