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>;T DS_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.

$ 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

Thanks,
Andy

SQL> SELECT * FROM TestTable WHERE vc8000 =
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
...
SQLRowCount returns 0

$ 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?

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

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:

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

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.

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.