Hi,
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
On Thu, Aug 19, 2010 at 8:42 PM, Andy Hochhaus <ahochh
...@samegoal.com> wrote:
> 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
