pyodbc: Cannot insert strings with a length greater than 2000 into columns with a datatype of varchar(max) or nvarchar(max) using parametrised queries

Environment

Python: 3.8.5
pyodbc: 4.0.30
OS: macOS 10.5.7
DB: MS SQL Server 2017 and 2019
driver: microsoft/mssql-release/msodbcsql17: stable 17.6.1.1

Issue

I cannot insert strings with a length greater than 2000 into columns with a datatype of varchar(max) or nvarchar(max) using parametrised queries.

In particular the POC (see below) fails with following output:

started
non-param: len(content)=2001: success
Traceback (most recent call last):
  File "test.py", line 54, in <module>
    cursor.execute(sql, (content,))
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot convert to text/ntext or collate to 'Latin1_General_100_CI_AS_SC' because these legacy LOB types do not support UTF-8 or UTF-16 encodings. Use types varchar(max), nvarchar(max) or a collation which does not have the _SC or _UTF8 flags. (4189) (SQLParamData)")

Every parametrised insert with a string having a length greater than 2000 fails with the above error message relating to a text/ntext conversion or Latin1_General_100_CI_AS_SC collation, which is strange considering that only plain ASCII is inserted.

How can I resolve this issue and insert strings of any length into the database?

POC:

import pyodbc

host = 'tcp:127.0.0.1,1433'
db = 'pyodbc_test'
user = 'sa'
pwd = 'P@ssw0rd'

print('started')

cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+host+';DATABASE='+'master'+';UID='+user+';PWD='+ pwd, autocommit=True)
cursor = cnxn.cursor()

try:
    cursor.execute(f'CREATE DATABASE {db} COLLATE Latin1_General_100_CI_AS_SC')
except pyodbc.ProgrammingError as e:
    pass # database exists

cursor.execute(f'USE {db}')

try:
    cursor.execute("""
        CREATE TABLE msg (
            id int identity(1,1) not null,
            content varchar(max) not null
        );""")
except pyodbc.ProgrammingError as exc:
    pass # table exists

content = 2001 * 'A'

cursor.execute(f"""
    INSERT INTO msg (content)
    VALUES ('{content}')""")
print(f'non-param: {len(content)=}: success')

# fails (pyodbc 4.0.30)
sql = f"""
      INSERT INTO msg (content)
      VALUES (?)"""
cursor.execute(sql, (content,))
print(f'param: {len(content)=}: success')

print('finished')

About this issue

Original URL
State: closed
Created 4 years ago
Comments: 36 (16 by maintainers)

Commits related to this issue

Add varchar(max) and nvarchar(max) SQL Server tests Trying to reproduce #835. No luck. — committed to mkleehammer/pyodbc by mkleehammer 4 years ago

Most upvoted comments

@v-chojas - The test code also completes successfully with PDO_ODBC under PHP:

<?php
header('Content-Type: text/html; charset=utf-8');
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>PDO example</title>
</head>
<body>
<?php
echo (8 * PHP_INT_SIZE) . "-bit<br/>";
$connStr = 
        'odbc:' .
        'DRIVER=ODBC Driver 17 for SQL Server;' .
        'SERVER=.\\SQLEXPRESS;' .
        'DATABASE=master;' .
        'Trusted_Connection=yes;' .
        'UseFMTONLY=yes';
$dbh = new PDO($connStr);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$db_name = "gh_sqla_5651";
$table_name = "table1";

$sth = $dbh->prepare("DROP DATABASE IF EXISTS " . $db_name);
$sth->execute();
$sth = $dbh->prepare("CREATE DATABASE " . $db_name . " COLLATE Latin1_General_100_CI_AI_SC");
$sth->execute();
$sth = $dbh->prepare("USE " . $db_name);
$sth->execute();
$sth = $dbh->prepare("CREATE TABLE " . $table_name . "(id int PRIMARY KEY, txt nvarchar(max))");
$sth->execute();
$sth = $dbh->prepare("INSERT INTO " . $table_name . " (id, txt) VALUES (?, ?)");
$id = 1;
$sth->bindParam(1, $id, PDO::PARAM_INT);
$txt = str_repeat("x", 2001);
$sth->bindParam(2, $txt, PDO::PARAM_STR);
$sth->execute();
?>
</body>
</html>

SQL Profiler shows

set fmtonly on select id,txt from table1 where 1=2 set fmtonly off

declare @p1 int
set @p1=5
exec sp_prepare @p1 output,N'@P1 int,@P2 nvarchar(max)',N'INSERT INTO table1 (id, txt) VALUES (@P1, @P2)',1
select @p1

exec sp_execute 5,1,N'xxx ... xxx'

exec sp_unprepare 5

pyodbc is definitely the outlier here.

gordthompson on Oct 20, 2020