Error passing long string to stored proc with varchar(max) MS SQL - HY104 / Invalid precision value

[erichards97]

I've found when passing long strings (~2000+) to a stored procedure in MS SQL Server, where the parameter is set as varchar(max), that turbodbc reports a HY104, Invalid precision value error.

This doesn't happen with pyodbc, nor does it happen with turbodbc when the parameter is constructed as part of the SQL string; only when it is provided separately.

Strangely I also found that if the string was something like 'a'*3000 no such error occurred yet 'a123456...' as in the code below does result in error.

The example code shows:

• pyodbc with string passed as parameter, works fine

• turbodbc with string passed as parameter, doesn't work

• pyodbc with string as part of sql text, works fine

• turbodbc with string as part of sql text, works fine

from turbodbc import connect as turbo_connect
from pyodbc import connect as pyodbc_connect

turbo_db_conn = turbo_connect(...)

pyodbc_db_conn = pyodbc_connect(...)

pyodbc_cursor = pyodbc_db_conn.cursor()
turbo_cursor = turbo_db_conn.cursor()

my_str = 'a'
for i in range(3000):
    my_str += str(i)
params = (my_str,)

sql_with_param = """EXEC [schema].SelectByFirstName @fname=? -- varchar(max)"""
sql_full = f'EXEC [schema].SelectByFirstName @fname=\'{my_str}\' -- varchar(max)'


print('Trying pyodbc - with params') # WORKS
try:
    pyodbc_cursor.execute(sql_with_param, params)
    print(pyodbc_cursor.fetchall())
except Exception as e:
    print(e)

print('Trying turbodbc - with params') # DOESN'T WORK - HY104 / Invalid precision value
try:
    turbo_cursor.execute(sql_with_param, params)
    print(turbo_cursor.fetchall())
except Exception as e:
    print(e)

print('Trying pyodbc - without params') # WORKS
try:
    pyodbc_cursor.execute(sql_full)
    print(pyodbc_cursor.fetchall())
except Exception as e:
    print(e)

print('Trying turbodbc - without params') # WORKS
try:
    turbo_cursor.execute(sql_full)
    print(turbo_cursor.fetchall())
except Exception as e:
    print(e)

SQL used for setup:

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

INSERT INTO [schema].Persons
(PersonID, LastName, FirstName, Address, City)
VALUES
(0, -- PersonID - int
'Smith'  , -- LastName - varchar(255)
'John'  , -- FirstName - varchar(255)
'123'  , -- Address - varchar(255)
'London'  -- City - varchar(255)
    )

CREATE PROCEDURE SelectByFirstName @fname varchar(max)
AS
SELECT * FROM Persons WHERE FirstName = @fname

EXEC [schema].SelectByFirstName @fname='John' -- varchar(max)

This is using ODBC Driver 17 for SQL Server and the full error message is:

  File "...\venv\lib\site-packages\turbodbc\exceptions.py", line 41, in wrapper
    return f(*args, **kwds)
  File "...\venv\lib\site-packages\turbodbc\cursor.py", line 134, in execute
    buffer.add_set(parameters)
turbodbc_intern.Error: ODBC error
state: HY104
native error code: 0
message: [Microsoft][ODBC Driver 17 for SQL Server]Invalid precision value

[erichards97]

This looks similar to #143