WHERE clause containing an equals operator with len(value) > 255 characters cause MS SQL Server commands to fail
Andy Hochhaus
I am experiencing problems with long character strings (> 255 chars)
when using pyodbc to connect to MS SQL Server 2008 from Debian Squeeze
Linux.
The problems are only experienced when combined with an equals
operator in a WHERE clause (however using a LIKE operator works fine).
I experience these issues with TDS_VERSION=7.0 and TDS_VERSION=8.0
both of which should allow for values > length 255. I have verified
(by hand) that long strings can be inserted and updated (assuming they
are not in the WHERE clause) so I know that the TDS_VERSION field of
my connection string is being respected.
For example:
SELECT * FROM TestTable WHERE vc8000 = 'a' * 256 // This fails
SELECT * FROM TestTable WHERE vc8000 = 'a' * 255 // This succeeds
SELECT * FROM TestTable WHERE vc8000 LIKE 'a' * 256 // This succeeds
Upon failure, a "('HY000', 'The driver did not supply an error!')"
Exception is raised. Therefore, it is possible that the real issue is
in the underlying driver. Can anyone provide guidance on the best
course of action to isolate the source of the issue?
Please see the sample program for a detailed presentation of the
issue. Any guidance on the proper next steps for debugging is
appreciated.
Thanks,
Andy
Program demonstrating error
-----------------------------------------
#!/usr/bin/env python2.6
import pyodbc
# CREATE TABLE TestTable(
# vc8000 VARCHAR(8000) NULL,
# )
cnxn = pyodbc.connect('DRIVER={SQL
Server};SERVER=<HOSTNAME>;DATABASE=<DBNAME>;UID=<USERNAME>;PWD=<PASSWORD>;TDS_Version=7.0;')
cursor = cnxn.cursor()
short_value = 'a' * 255
long_value = 'a' * 256
try: # success
cursor.execute("SELECT * FROM TestTable WHERE vc8000 = ?", short_value)
print "short select: SUCCESS"
except Exception as e:
print "short select: FAILURE[%s]" % e
try: # failure
cursor.execute("SELECT * FROM TestTable WHERE vc8000 = ?", long_value)
print "long select: SUCCESS"
except Exception as e:
print "long select: FAILURE[%s]" % e
try: # success
cursor.execute("SELECT * FROM TestTable WHERE vc8000 LIKE ?", short_value)
print "short select like: SUCCESS"
except Exception as e:
print "short select: FAILURE[%s]" % e
try: # success
cursor.execute("SELECT * FROM TestTable WHERE vc8000 LIKE ?", long_value)
print "long select like: SUCCESS"
except Exception as e:
print "long select: FAILURE[%s]" % e
try: # success
cursor.execute("UPDATE TestTable SET vc8000 = ? WHERE vc8000 = ?",
long_value,
short_value)
print "short update: SUCCESS"
except Exception as e:
print "short update: FAILURE[%s]" % e
try: # failure
cursor.execute("UPDATE TestTable SET vc8000 = ? WHERE vc8000 = ?",
long_value,
long_value)
print "long update: SUCCESS"
except Exception as e:
print "long update: FAILURE[%s]" % e
try: # success
cursor.execute("INSERT INTO TestTable(vc8000) VALUES (?)",
long_value)
print "long insert: SUCCESS"
except Exception as e:
print "long insert: FAILURE[%s]" % e
cursor.close()
Output from above program
---------------------------------------
$ ./long_str_test.py
short select: SUCCESS
long select: FAILURE[('HY000', 'The driver did not supply an error!')]
short select like: SUCCESS
long select like: SUCCESS
short update: SUCCESS
long update: FAILURE[('HY000', 'The driver did not supply an error!')]
long insert: SUCCESS
System info
------------------
Debian Squeeze/Testing
$ python2.6
Python 2.6.6rc1+ (r266rc1:83691, Aug 5 2010, 17:07:04)
[GCC 4.4.5 20100728 (prerelease)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> pyodbc.version
'2.1.7-beta0'
$ odbcinst -j
unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/[USERNAME]/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
$ cat /etc/odbcinst.ini
[SQL Server]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
$ uname -a
Linux iq2r8d500 2.6.32-5-amd64 #1 SMP Sat Jul 24 01:47:24 UTC 2010
x86_64 GNU/Linux
Andy Hochhaus
> Upon failure, a "('HY000', 'The driver did not supply an error!')"
> Exception is raised. Therefore, it is possible that the real issue is
> in the underlying driver.
In case it is helpful in debugging, the commands that fail in pyodbc
(see my previous email) run successfully from the isql command line
utility. Does this imply the error may lie in pyodbc?
Thanks,
Andy
SQL> SELECT * FROM TestTable WHERE vc8000 =
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
...
SQLRowCount returns 0
SQL> UPDATE TestTable SET vc8000 =
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
WHERE vc8000 = 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
...
SQLRowCount returns 0
Andy Hochhaus
I built a version of pyodbc with tracing enabled using the following commands:
$ rm -rf build
$ python setup.py --trace build
$ sudo python setup.py install
Rerunning my test script with tracing enabled generates the following log:
cnxn.new cnxn=0x1695c70 hdbc=23604704
cursor.new cnxn=0x1695c70 hdbc=23604704 cursor=0x1624390 hstmt=24264496
SELECT * FROM TestTable WHERE vc8000 = ?
BIND: param=1 fCType=1 (SQL_C_CHAR) fSqlType=12 (SQL_VARCHAR)
cbColDef=255 DecimalDigits=0 cbValueMax=256 *pcb=255
SQLRowCount: 0
SQLNumResultCols: 3
Col 1: type=12 colsize=8000
Col 2: type=-1 colsize=2147483647
Col 3: type=-1 colsize=2147483647
short select: SUCCESS
SELECT * FROM TestTable WHERE vc8000 = ?
BIND: param=1 fCType=1 (SQL_C_CHAR) fSqlType=-1 (unknown) cbColDef=256
DecimalDigits=0 cbValueMax=8 *pcb=-356
In RaiseError!
long select: FAILURE[('HY000', 'The driver did not supply an error!')]
SELECT * FROM TestTable WHERE vc8000 LIKE ?
BIND: param=1 fCType=1 (SQL_C_CHAR) fSqlType=12 (SQL_VARCHAR)
cbColDef=255 DecimalDigits=0 cbValueMax=256 *pcb=255
SQLRowCount: 0
SQLNumResultCols: 3
Col 1: type=12 colsize=8000
Col 2: type=-1 colsize=2147483647
Col 3: type=-1 colsize=2147483647
short select like: SUCCESS
SELECT * FROM TestTable WHERE vc8000 LIKE ?
BIND: param=1 fCType=1 (SQL_C_CHAR) fSqlType=-1 (unknown) cbColDef=256
DecimalDigits=0 cbValueMax=8 *pcb=-356
SQLRowCount: 0
SQLNumResultCols: 3
Col 1: type=12 colsize=8000
Col 2: type=-1 colsize=2147483647
Col 3: type=-1 colsize=2147483647
long select like: SUCCESS
UPDATE TestTable SET vc8000 = ? WHERE vc8000 = ?
BIND: param=1 fCType=1 (SQL_C_CHAR) fSqlType=-1 (unknown) cbColDef=256
DecimalDigits=0 cbValueMax=8 *pcb=-356
BIND: param=2 fCType=1 (SQL_C_CHAR) fSqlType=12 (SQL_VARCHAR)
cbColDef=255 DecimalDigits=0 cbValueMax=256 *pcb=255
SQLRowCount: 0
SQLNumResultCols: 0
short update: SUCCESS
UPDATE TestTable SET vc8000 = ? WHERE vc8000 = ?
BIND: param=1 fCType=1 (SQL_C_CHAR) fSqlType=-1 (unknown) cbColDef=256
DecimalDigits=0 cbValueMax=8 *pcb=-356
BIND: param=2 fCType=1 (SQL_C_CHAR) fSqlType=-1 (unknown) cbColDef=256
DecimalDigits=0 cbValueMax=8 *pcb=-356
In RaiseError!
long update: FAILURE[('HY000', 'The driver did not supply an error!')]
INSERT INTO TestTable(vc8000) VALUES (?)
BIND: param=1 fCType=1 (SQL_C_CHAR) fSqlType=-1 (unknown) cbColDef=256
DecimalDigits=0 cbValueMax=8 *pcb=-356
SQLRowCount: 1
SQLNumResultCols: 0
long insert: SUCCESS
cnxn.clear cnxn=0x1695c70 hdbc=23604704
It appears that whenever a column has length > 255 chars the fSqlType
switches from 12 (SQL_VARCHAR) and instead becomes -1 (unknown).
I see in the unixODBC source code that SQL_LONGVARCHAR is defined to
be -1. Is this expected by pyodbc?
I hand modified the code in src/params.cpp lines 466 to 483 to ignore
the MAX_VARCHAR_BUFFER length check and instead always bind parameters
as SQL_VARCHAR regardless of their length. Doing so makes all queries
in my example run successfully. I highly doubt this is a safe change.
Is it possible that the manner in which pyodbc binds SQL_LONGVARCHAR
parameters is not compatible with what is expected by unixODBC? Any
recommendations on appropriate next steps?
Thanks,
Andy
Andy Hochhaus
> Is it possible that the manner in which pyodbc binds SQL_LONGVARCHAR
> parameters is not compatible with what is expected by unixODBC?
Trying to isolate the source of the issue, I tried running my test
script on Windows with tracing enabled. I believe that this eliminates
unixODBC and FreeTDS as possible sources of the issue by using the
Microsoft ODBC and drivers implementation instead.
The same two queries fail on windows and linux (all of the others
succeed). Unless I'm overlooking something (which is very possible
given my very limited understanding of the pyodbc codebase) this
suggests that the problem is caused by pyodbc.
The parameters seem to bind in the same fashion as they do on linux
(that is fSqlType = 12 for SQL_VARCHAR and -1 for SQL_LONGVARCHAR).
However, a better more meaningful error message is returned:
[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]The data types
varchar and text are incompatible in the equal to operator. (402)
(SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL
Server]Statement(s) could not be prepared. (8180)
This would explain why the same queries modified to use LIKE instead
of equals are able to succeed. Any ideas as to which value is being
treated as type text?
Thanks,
Andy
Full debug log:
---------------------
cnxn.new cnxn=0231D278 hdbc=6558104
cursor.new cnxn=0231D278 hdbc=6558104 cursor=003CCD78 hstmt=41674096
SELECT * FROM TestTable WHERE vc8000 = ?
BIND: param=1 fCType=1 (SQL_C_CHAR) fSqlType=12 (SQL_VARCHAR) cbColDef=255 Decim
alDigits=0 cbValueMax=256 *pcb=255
SQLRowCount: 0
SQLNumResultCols: 1
Col 1: type=12 colsize=8000
short select: SUCCESS
SELECT * FROM TestTable WHERE vc8000 = ?
BIND: param=1 fCType=1 (SQL_C_CHAR) fSqlType=-1 (unknown) cbColDef=256 DecimalDi
gits=0 cbValueMax=4 *pcb=-356
In RaiseError!
long select: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL
Server]The data types varchar and text are incompatible in the equal to operator
. (402) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server
]Statement(s) could not be prepared. (8180)')]
SELECT * FROM TestTable WHERE vc8000 LIKE ?
BIND: param=1 fCType=1 (SQL_C_CHAR) fSqlType=12 (SQL_VARCHAR) cbColDef=255 Decim
alDigits=0 cbValueMax=256 *pcb=255
SQLRowCount: 0
SQLNumResultCols: 1
Col 1: type=12 colsize=8000
short select like: SUCCESS
SELECT * FROM TestTable WHERE vc8000 LIKE ?
BIND: param=1 fCType=1 (SQL_C_CHAR) fSqlType=-1 (unknown) cbColDef=256 DecimalDi
gits=0 cbValueMax=4 *pcb=-356
SQLRowCount: 0
SQLNumResultCols: 1
Col 1: type=12 colsize=8000
long select like: SUCCESS
UPDATE TestTable SET vc8000 = ? WHERE vc8000 = ?
BIND: param=1 fCType=1 (SQL_C_CHAR) fSqlType=-1 (unknown) cbColDef=256 DecimalDi
gits=0 cbValueMax=4 *pcb=-356
BIND: param=2 fCType=1 (SQL_C_CHAR) fSqlType=12 (SQL_VARCHAR) cbColDef=255 Decim
alDigits=0 cbValueMax=256 *pcb=255
short update: SUCCESS
UPDATE TestTable SET vc8000 = ? WHERE vc8000 = ?
BIND: param=1 fCType=1 (SQL_C_CHAR) fSqlType=-1 (unknown) cbColDef=256 DecimalDi
gits=0 cbValueMax=4 *pcb=-356
BIND: param=2 fCType=1 (SQL_C_CHAR) fSqlType=-1 (unknown) cbColDef=256 DecimalDi
gits=0 cbValueMax=4 *pcb=-356
In RaiseError!
long update: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL
Server]The data types varchar and text are incompatible in the equal to operator
. (402) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server
]Statement(s) could not be prepared. (8180)')]
INSERT INTO TestTable(vc8000) VALUES (?)
BIND: param=1 fCType=1 (SQL_C_CHAR) fSqlType=-1 (unknown) cbColDef=256 DecimalDi
gits=0 cbValueMax=4 *pcb=-356
SQLRowCount: 1
SQLNumResultCols: 0
long insert: SUCCESS
cnxn.clear cnxn=0231D278 hdbc=6558104
Andy Hochhaus
My previous message (below) may not have posted to the list yet.
Running on Windows instead of Linux, the failing queries from my
previous example produce the following error:
"""
[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]The data types
varchar and text are incompatible in the equal to operator. (402)
(SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL
Server]Statement(s) could not be prepared. (8180)
"""
From online reading and experimentation I believe that SQL Server has
a hard limit of 8000 characters of data being used with "comparison
operators" (i.e. equals, greater than, less than, etc). This limit
does not apply to the LIKE operator. Can anyone confirm this is true?
I have attempted to confirm this with the following table:
CREATE TABLE TestTable(
vc8000 VARCHAR(8000) NULL,
vcmax VARCHAR(MAX) NULL,
txt TEXT NULL
)
Then from isql (on linux) and Microsoft SQL Server Management Studio
(on windows) I ran the following queries:
> SELECT * FROM TestTable WHERE vc8000 = 'a' * 8000 // success
> SELECT * FROM TestTable WHERE vc8000 = 'a' * 8001 // failure: The data types varchar and text are incompatible in the equal to operator.
> SELECT * FROM TestTable WHERE vcmax = 'a' * 8000 // success
> SELECT * FROM TestTable WHERE vcmax = 'a' * 8001 // failure: The data types varchar(max) and text are incompatible in the equal to operator.
> SELECT * FROM TestTable WHERE txt = 'a' * 8000 // failure: The data types text and varchar are incompatible in the equal to operator.
> SELECT * FROM TestTable WHERE txt = 'a' * 8001 // failure: The data types text and text are incompatible in the equal to operator.
Note that all of the above queries fail when run through pyodbc (on
linux and windows). Without knowing much about the internals, it
appears that SQL Server is treating all values of length <= 8000 as
VARCHAR and all values of length > 8000 as type Text.
However, this behavior deviates from how pyodbc binds parameters. From
src/params.cpp lines 466-483 (of version 2.1.7):
"""
if (len <= MAX_VARCHAR_BUFFER) // Note: MAX_VARCHAR_BUFFER = 255
{
fSqlType = SQL_VARCHAR;
fCType = SQL_C_CHAR;
pbValue = pch;
cbColDef = max(len, 1);
cbValueMax = len + 1;
*pcbValue = (SQLLEN)len;
}
else
{
fSqlType = SQL_LONGVARCHAR;
fCType = SQL_C_CHAR;
pbValue = param;
cbColDef = max(len, 1);
cbValueMax = sizeof(PyObject*);
*pcbValue = SQL_LEN_DATA_AT_EXEC((SQLLEN)len);
}
"""
Pyodbc binds all values of length <= 255 to type SQL_VARCHAR and all
values of length > 255 to type SQL_LONGVARCHAR. I _suspect_ that SQL
Server treats SQL_LONGVARCHAR as type Text which cannot be used with a
comparison operator. If so, this would explain the behavior that I am
seeing. Can anyone confirm? [Note that I have only tested this with
SQL Server 2008 so it may be different with other versions.]
The negative effects of this "incorrect" binding by pyodbc are hidden
in most cases (i.e. when a long string is being used with a
non-comparison operator or when a short string is being used with a
comparison operator).
In order to check my theory, I modified pyodbc to replace the
MAX_VARCHAR_BUFFER constant on line 466 with the value 8000. Then I
wrote the new test script:
"""
import pyodbc
# CREATE TABLE TestTable(
# vc8000 VARCHAR(8000) NULL,
# vcmax VARCHAR(max) NULL,
# txt TEXT Null
# )
cnxn = pyodbc.connect('DRIVER={SQL
Server};SERVER=[host];DATABASE=[db];UID=[user];PWD=[pw];TDS_Version=7.0;')
cursor = cnxn.cursor()
templates = [ 'SELECT * FROM TestTable WHERE %s = ?',
'INSERT INTO TestTable(%s) VALUES (?)',
'UPDATE TestTable SET %s = ?', ]
cols = [ 'vc8000', 'vcmax', 'txt', ]
values = [ 'a' * 255, 'a' * 256, 'a' * 8000, 'a' * 8001, ]
for sql in templates:
for col in cols:
for value in values:
name = sql.split()[0]
try:
cursor.execute(sql % col, value)
print "%s(%s)[%s]: SUCCESS" % (name, col, len(value))
except Exception as e:
print "%s(%s)[%s]: FAILURE[%s]" % (name, col, len(value), e)
cursor.close()
"""
Output running with my "hacked" pyodbc:
------------------------------------------------------------
SELECT(vc8000)[255]: SUCCESS
SELECT(vc8000)[256]: SUCCESS
SELECT(vc8000)[8000]: SUCCESS
SELECT(vc8000)[8001]: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]The data types varchar and text are
incompatible in the equal to operator. (402) (SQLExecDirectW); [42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (8180)')]
SELECT(vcmax)[255]: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]The data types varchar(max) and text are
incompatible in the equal to operator. (402) (SQLPrepare); [42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (8180)')]
SELECT(vcmax)[256]: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]The data types varchar(max) and text are
incompatible in the equal to operator. (402) (SQLPrepare); [42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (8180)')]
SELECT(vcmax)[8000]: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]The data types varchar(max) and text are
incompatible in the equal to operator. (402) (SQLPrepare); [42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (8180)')]
SELECT(vcmax)[8001]: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]The data types varchar(max) and text are
incompatible in the equal to operator. (402) (SQLPrepare); [42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (8180)')]
SELECT(txt)[255]: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]The data types text and text are
incompatible in the equal to operator. (402) (SQLPrepare); [42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (8180)')]
SELECT(txt)[256]: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]The data types text and text are
incompatible in the equal to operator. (402) (SQLPrepare); [42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (8180)')]
SELECT(txt)[8000]: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]The data types text and text are
incompatible in the equal to operator. (402) (SQLPrepare); [42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (8180)')]
SELECT(txt)[8001]: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]The data types text and text are
incompatible in the equal to operator. (402) (SQLPrepare); [42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (8180)')]
INSERT(vc8000)[255]: SUCCESS
INSERT(vc8000)[256]: SUCCESS
INSERT(vc8000)[8000]: SUCCESS
INSERT(vc8000)[8001]: FAILURE[('HY000', 'The driver did not supply an error!')]
INSERT(vcmax)[255]: SUCCESS
INSERT(vcmax)[256]: SUCCESS
INSERT(vcmax)[8000]: SUCCESS
INSERT(vcmax)[8001]: SUCCESS
INSERT(txt)[255]: SUCCESS
INSERT(txt)[256]: SUCCESS
INSERT(txt)[8000]: SUCCESS
INSERT(txt)[8001]: SUCCESS
UPDATE(vc8000)[255]: SUCCESS
UPDATE(vc8000)[256]: SUCCESS
UPDATE(vc8000)[8000]: SUCCESS
UPDATE(vc8000)[8001]: FAILURE[('HY000', 'The driver did not supply an error!')]
UPDATE(vcmax)[255]: SUCCESS
UPDATE(vcmax)[256]: SUCCESS
UPDATE(vcmax)[8000]: SUCCESS
UPDATE(vcmax)[8001]: SUCCESS
UPDATE(txt)[255]: SUCCESS
UPDATE(txt)[256]: SUCCESS
UPDATE(txt)[8000]: SUCCESS
UPDATE(txt)[8001]: SUCCESS
Output running with "clean" 2.1.7:
------------------------------------------------
SELECT(vc8000)[255]: SUCCESS
SELECT(vc8000)[256]: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]The data types varchar and text are
incompatible in the equal to operator. (402) (SQLExecDirectW); [42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (8180)')]
SELECT(vc8000)[8000]: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]The data types varchar and text are
incompatible in the equal to operator. (402) (SQLPrepare); [42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (8180)')]
SELECT(vc8000)[8001]: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]The data types varchar and text are
incompatible in the equal to operator. (402) (SQLPrepare); [42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (8180)')]
SELECT(vcmax)[255]: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]The data types varchar(max) and text are
incompatible in the equal to operator. (402) (SQLPrepare); [42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (8180)')]
SELECT(vcmax)[256]: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]The data types varchar(max) and text are
incompatible in the equal to operator. (402) (SQLPrepare); [42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (8180)')]
SELECT(vcmax)[8000]: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]The data types varchar(max) and text are
incompatible in the equal to operator. (402) (SQLPrepare); [42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (8180)')]
SELECT(vcmax)[8001]: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]The data types varchar(max) and text are
incompatible in the equal to operator. (402) (SQLPrepare); [42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (8180)')]
SELECT(txt)[255]: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]The data types text and text are
incompatible in the equal to operator. (402) (SQLPrepare); [42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (8180)')]
SELECT(txt)[256]: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]The data types text and text are
incompatible in the equal to operator. (402) (SQLPrepare); [42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (8180)')]
SELECT(txt)[8000]: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]The data types text and text are
incompatible in the equal to operator. (402) (SQLPrepare); [42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (8180)')]
SELECT(txt)[8001]: FAILURE[('42000', '[42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]The data types text and text are
incompatible in the equal to operator. (402) (SQLPrepare); [42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (8180)')]
INSERT(vc8000)[255]: SUCCESS
INSERT(vc8000)[256]: SUCCESS
INSERT(vc8000)[8000]: SUCCESS
INSERT(vc8000)[8001]: FAILURE[('HY000', 'The driver did not supply an error!')]
INSERT(vcmax)[255]: SUCCESS
INSERT(vcmax)[256]: SUCCESS
INSERT(vcmax)[8000]: SUCCESS
INSERT(vcmax)[8001]: SUCCESS
INSERT(txt)[255]: SUCCESS
INSERT(txt)[256]: SUCCESS
INSERT(txt)[8000]: SUCCESS
INSERT(txt)[8001]: SUCCESS
UPDATE(vc8000)[255]: SUCCESS
UPDATE(vc8000)[256]: SUCCESS
UPDATE(vc8000)[8000]: SUCCESS
UPDATE(vc8000)[8001]: FAILURE[('HY000', 'The driver did not supply an error!')]
UPDATE(vcmax)[255]: SUCCESS
UPDATE(vcmax)[256]: SUCCESS
UPDATE(vcmax)[8000]: SUCCESS
UPDATE(vcmax)[8001]: SUCCESS
UPDATE(txt)[255]: SUCCESS
UPDATE(txt)[256]: SUCCESS
UPDATE(txt)[8000]: SUCCESS
UPDATE(txt)[8001]: SUCCESS
Would pyodbc be willing to (for recent versions of SQL Server) change
MAX_VARCHAR_BUFFER to be 8000? If so, I am willing to prepare a diff
if it is helpful.
Apologizes for the excessively long email thread.
Thanks,
Andy
mkleehammer
The difficulty we have at this point is picking the right value. Most
drivers choke if it is over 255, but I didn't expect SQL Server to
have problems if it was under 8000.
I don't know of anything returned by SQLGetInfo which would help here,
so I'll have to post something to a Microsoft SQL Server group. (I
used to have an MS connection, but not anymore.)
As a workaround, it could be made variable so it can be set manually,
then also provide some mappings from driver names to suggested values.
Sound reasonable? See any other ways of determining the right value
per driver?
mkleehammer
SQLGetTypeInfo(SQL_VARCHAR) to determine the max size, which is 8000
on SQL Server... I'll do that in 2.1.8 which I'll *try* to get to
ASAP.
(In addition to being swamped at work, I'm trying to sell my house.
Sigh.)
mkleehammer
2.1.8, but feel free to grab the source and try it.
I also added a unit test to the SQL Server tests and tested against
Access 2007.
My testing for work is more thorough (we use it for some 24x7 systems)
so it'll take a while on Monday.
Thanks for identifying this critical bug.
Andy Hochhaus
> Wow, good job on the tests. Thanks for that.
Gladly. Thanks for pyodbc. :)
> The difficulty we have at this point is picking the right value. Most
> drivers choke if it is over 255, but I didn't expect SQL Server to
> have problems if it was under 8000.
Agreed.
> I think I have a better answer: When creating the connection, use
> SQLGetTypeInfo(SQL_VARCHAR) to determine the max size, which is 8000
> on SQL Server... I'll do that in 2.1.8 which I'll *try* to get to
> ASAP.
This solution sounds perfect. Thanks.
> (In addition to being swamped at work, I'm trying to sell my house.
> Sigh.)
Good luck with the sale. If I can do anything to help you please let me know.
Thanks,
Andy
Andy Hochhaus
> I just checked in a fix. On Monday, I'll probably package it up as
> 2.1.8, but feel free to grab the source and try it.
I grabbed the source and can confirm that it fixes this bug.
Thanks for the fast patch!
mkleehammer
Thank you very much - I'm a big fan of Walter Williams!