Long byte string / regular string not binding as varbinary(max), varchar(max) when fast_executemany=True

[ww2406]

Please first make sure you have looked at:

• Documentation: https://github.com/mkleehammer/pyodbc/wiki
• Other issues

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be
sure to specify 32-bit Python or 64-bit:

• Python: 3.8.5 (OS X) and 3.8.7 (Windows 64 bit)
• pyodbc: 4.0.30
• OS: 10.15.7 (OS X) and 10 (Windows...unsure what build #)
• DB: SQL Server 2019
• driver: FreeTDS (OS X), ODBC Driver 17 for SQL Server (Windows)

Issue

When fast_executemany=True, long binary or string input values are not converted to the correct ODBC type it seems. Note that my examples below focus on using

Observed Behavior and MRE

Consider tables defined as follows:

CREATE TABLE binTest (
    ID int,
    bin varbinary
);

CREATE TABLE strTest (
    ID int,
    val varchar(max)
);

When running the following Python script (OS X example shown, but same occurs with equivalent Windows conn string), a

'String data, right truncation: length 8500 buffer 255'

error is returned for binTest.

import pyodbc

conn: pyodbc.Connection = pyodbc.connect("DSN=dockersql;UID=SA;PWD=?")
cur: pyodbc.Cursor = conn.cursor()
cur.fast_executemany = True

strToTest = 'a'*8500
binToTest = bytearray(strToTest,'utf8')

sql = """
INSERT INTO binTest VALUES (?,?)
"""
params = [(1, binToTest)]
cur.executemany(sql, params)
cur.commit()

sql =  """
INSERT INTO strTest VALUES (?,?)
"""
params = [(1, strToTest)]
cur.executemany(sql, params)
cur.commit()

When commenting the binTest portion and doing the strTest portion, similar behavior happens:

'String data, right truncation: length 17000 buffer 510'

A workaround I have found is to manually specify the input type for the column as varbinary,0 or varchar,0
cur.setinputsizes([None, None, (pyodbc.SQL_VARBINARY,0)])
cur.setinputsizes([None, None, (pyodbc.SQL_VARCHAR,0)])

[v-chojas]

Could you provide an ODBC trace? I believe it should be using DAE for those long types....

[gordthompson]

When I try and run your repro code I get

pyodbc.ProgrammingError: ('String data, right truncation: length 8500 buffer 1', 'HY000')

because varbinary (without a length) is interpreted as varbinary(1).

Once I fix that, your code runs fine for me.

# https://github.com/mkleehammer/pyodbc/issues/867
import sys

import pyodbc

print(sys.version)
# 3.8.6 (tags/v3.8.6:db45529, Sep 23 2020, 15:37:30) [MSC v.1927 32 bit (Intel)]
print(pyodbc.version)
# 4.0.30

connection_string = (
    "DRIVER=ODBC Driver 17 for SQL Server;"
    "SERVER=192.168.0.199;"
    "DATABASE=test;"
    "UID=scott;PWD=tiger^5HHH;"
)
conn: pyodbc.Connection = pyodbc.connect(connection_string)
cur: pyodbc.Cursor = conn.cursor()

print(cur.execute("SELECT @@VERSION").fetchval())
"""
Microsoft SQL Server 2019 (RTM-CU3) (KB4538853) - 15.0.4023.6 (X64) 
	Mar  4 2020 00:59:26 
	Copyright (C) 2019 Microsoft Corporation
	Developer Edition (64-bit) on Linux (Ubuntu 18.04.4 LTS) <X64>
"""

cur.execute("DROP TABLE IF EXISTS binTest")
cur.execute("""\
CREATE TABLE binTest (
    ID int,
    val varbinary(max)
);
""")

cur.execute("DROP TABLE IF EXISTS strTest")
cur.execute("""\
CREATE TABLE strTest (
    ID int,
    val varchar(max)
);
""")

strToTest = 'a'*8500
binToTest = bytearray(strToTest,'utf8')

cur.fast_executemany = True

sql = """
INSERT INTO binTest VALUES (?,?)
"""
params = [(1, binToTest)]
cur.executemany(sql, params)
cur.commit()

sql =  """
INSERT INTO strTest VALUES (?,?)
"""
params = [(1, strToTest)]
cur.executemany(sql, params)
cur.commit()

[ww2406]

@gordthompson Guess this is a classic case of oversimplification and assumption :)

You are of course right about varbinary(max)--had corrected it at some point but forgot to update the create statement. You're also right about this example running perfectly on Windows. I was actually trying to troubleshoot a different issue, and thought this simple case might have been the root cause, and when I had success on OS X I assumed I was right and didn't retest this simpler case on Windows before I posted. This issue as I have it written does persist on OS X, though, for both the binary and string cases--given that it works on Windows this might be a unixODBC or FreeTDS issue?

I'll post a separate issue about the more involved case I was looking at that fails (for sure this time!) on both Windows and OS X. It involves some geometry functionality so I thought a simpler use case might have been beneficial, but I guess that didn't pan out as nicely as I'd hoped.

[v-chojas]

Note that the Microsoft ODBC Driver for SQL Server is available for Mac too - if it works with that, then bug is in FreeTDS.

[ww2406]

I just tried the MS ODBC driver -- the binary insert works now, but not the string insert. @v-chojas you mentioned an ODBC trace earlier, how do I do that?

[v-chojas]

https://github.com/mkleehammer/pyodbc/wiki/Troubleshooting-%E2%80%93-Generating-an-ODBC-trace-log

[ww2406]

Attaching the ODBC trace (UID/PWD removed from connection string). Using MS ODBC driver here.

odbc_binary.txt
odbc_varchar.txt

[gordthompson]

My test code runs okay on Xubuntu 18.04, too.

odbctrace.txt

I notice that the SQLDescribeParam calls work okay for me

[ODBC][3518][1613051791.846856][SQLPrepareW.c][165]
        Entry:
            Statement = 0x13a3d00
            SQL = [
INSERT INTO strTest VALUES (?,?)
][length = 34]
[ODBC][3518][1613051791.846869][SQLPrepareW.c][346]
        Exit:[SQL_SUCCESS]
[ODBC][3518][1613051791.846873][SQLNumParams.c][144]
        Entry:
            Statement = 0x13a3d00
            Param Count = 0x7ffe746676c6
[ODBC][3518][1613051791.846878][SQLNumParams.c][231]
        Exit:[SQL_SUCCESS]
            Count = 0x7ffe746676c6 -> 2
[ODBC][3518][1613051791.846883][SQLDescribeParam.c][185]
        Entry:
            Statement = 0x13a3d00
            Parameter Number = 1
            SQL Type = 0x13ad0b2
            Param Def = 0x13ad0b8
            Scale = 0x13ad0c0
            Nullable = 0x7ffe746677a8
[ODBC][3518][1613051791.851229][SQLDescribeParam.c][338]
        Exit:[SQL_SUCCESS]                
            SQL Type = 0x7ffe74667220                
            Param Def = 0x7ffe74667310                
            Scale = 0x7ffe74667400                
            Nullable = 0x7ffe746674f0
[ODBC][3518][1613051791.851251][SQLDescribeParam.c][185]
        Entry:
            Statement = 0x13a3d00
            Parameter Number = 2
            SQL Type = 0x13ad11a
            Param Def = 0x13ad120
            Scale = 0x13ad128
            Nullable = 0x7ffe746677a8
[ODBC][3518][1613051791.851264][SQLDescribeParam.c][338]
        Exit:[SQL_SUCCESS]                
            SQL Type = 0x7ffe74667220                
            Param Def = 0x7ffe74667310                
            Scale = 0x7ffe74667400                
            Nullable = 0x7ffe746674f0

but the corresponding calls from @ww2406 's "odbc_varchar.txt" show Exit:[SQL_ERROR]

[ODBC][32303][1612998942.259466][SQLPrepareW.c][165]
        Entry:
            Statement = 0x7f97b2821a00
            SQL = [
INSERT INTO strTest VALUES (?,?)
][length = 34]
[ODBC][32303][1612998942.259521][SQLPrepareW.c][346]
        Exit:[SQL_SUCCESS]
[ODBC][32303][1612998942.259567][SQLNumParams.c][144]
        Entry:
            Statement = 0x7f97b2821a00
            Param Count = 0x7ffee6d83d4e
[ODBC][32303][1612998942.259674][SQLNumParams.c][231]
        Exit:[SQL_SUCCESS]
            Count = 0x7ffee6d83d4e -> 2
[ODBC][32303][1612998942.259725][SQLDescribeParam.c][185]
        Entry:
            Statement = 0x7f97b2821a00
            Parameter Number = 1
            SQL Type = 0x7f97b2417d52
            Param Def = 0x7f97b2417d58
            Scale = 0x7f97b2417d60
            Nullable = 0x7ffee6d83e48
[ODBC][32303][1612998942.266081][SQLDescribeParam.c][338]
        Exit:[SQL_ERROR]                
            SQL Type = 0x7ffee6d83c60                
            Param Def = 0x7ffee6d83b70                
            Scale = 0x7ffee6d83a80                
            Nullable = 0x7ffee6d83990
[ODBC][32303][1612998942.266150][SQLDescribeParam.c][185]
        Entry:
            Statement = 0x7f97b2821a00
            Parameter Number = 2
            SQL Type = 0x7f97b2417dba
            Param Def = 0x7f97b2417dc0
            Scale = 0x7f97b2417dc8
            Nullable = 0x7ffee6d83e48
[ODBC][32303][1612998942.268039][SQLDescribeParam.c][338]
        Exit:[SQL_ERROR]                
            SQL Type = 0x7ffee6d83c60                
            Param Def = 0x7ffee6d83b70                
            Scale = 0x7ffee6d83a80                
            Nullable = 0x7ffee6d83990

[gordthompson]

@ww2406 - Does the msodbcsql install on Mac also install unixODBC 2.3.7 like it does on Linux?

[gord@localhost ~]$ odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/gord/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

[ww2406]

@gordthompson looks like it does 2.3.9

odbcinst -j
unixODBC 2.3.9
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /Users/wwelch/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

[gordthompson]

@ww2406 - I upgraded my Xubuntu 18.04 box to unixODBC 2.3.9 and my test code still runs fine, so it's not an issue with that version of unixODBC per se. This is starting to look like "a Mac thing".

[ww2406]

@gordthompson sometimes “think different” goes a step too far ha. Is this something you think there’s realistically a chance of isolating what’s happening?

[gordthompson]

Is this something you think there’s realistically a chance of isolating what’s happening?

First we need to be sure we're talking about the same thing. Can you verify whether or not my test code from here fails on your Mac. I've demonstrated that it works on Windows and Linux (Ubuntu, at least).

[ww2406]

Well...seems a few days break has reset something? Both your test code and my test code are working? I'm very flummoxed now...

I'm glad I had those ODBC traces so you don't think I'm completely crazy!