I believe this bug is also related to the other problem I just reported.
OS = Windows XP SP2
DB = Microsoft Access XP
PROBLEM:
When you use + (or &) to concatenation columns together and the columns are
of type text and the combined length exceed 255 this causes pyodbc to fail
and python to crash.
Basically
select c2 + ' ' + c3 from test_concat where c1 = 1
will cause the problem if c2 and c3 are text columns and their combined
length is 255.
I also encountered this problem years ago with mxODBC and I believe the
problem may actually be an underlying bug in odbc.
When I contacted Marc-André Lemburg,the author of mxODBC he patched it to
fix the problem and I believe the workaround was to allocate a larger
buffer.
If the columns that are concatenated are memo columns I also believe the
problem occurs.
# pyodbc.Error: ('HY090', '[HY090] [Microsoft][ODBC Driver Manager]
Invalid string
# or buffer length (0)')
To recreate the problem create an Access db named test and create a DSN
named test.
Run the createtable.py script to create the table and then run the broke.py
to demonstrate the problem.
The broke.py script has 4 select statements but only one is executed.
Change line number 34 to specify which one you want to test.
#
# createtable.py script
#
import pyodbc
dbs = pyodbc.connect('dsn=test')
c = dbs.cursor()
try:
sql = 'drop table test_concat'
c.execute(sql)
dbs.commit()
except:
# ignore drop table failure
pass
sql = 'create table test_concat (c1 int not null, c2 text not null, c3 text
not null)'
c.execute(sql)
dbs.commit()
sql = 'insert into test_concat values(1, ?, ?)'
c2_value = '1' * 251
c2_value = '%sJOE1' % (c2_value)
c3_value = '1' * 251
c3_value = '%sJOE2' % (c3_value)
c.execute(sql, (c2_value, c3_value))
dbs.commit()
c.close()
dbs.close()
#
# broke.py script
#
import pyodbc
dbs = pyodbc.connect('dsn=test')
c = dbs.cursor()
sql1 = "select c2 from test_concat where c1 = 1"
sql2 = "select c2, c3 from test_concat where c1 = 1"
sql3 = "select c2 + ' ' + c3 from test_concat where c1 = 1"
sql4 = "select c2 + ' ' + c3 as a from test_concat where c1 = 1"
#
# 1: Works fine
#
# 2: Works fine
#
# 3: Errors and python crashes
#
# Traceback (most recent call last):
# File "H:\1-pyodbc-bug\concat-bug\broke.py", line 36, in ?
# row = c.fetchone()
# pyodbc.Error: ('HY090', '[HY090] [Microsoft][ODBC Driver Manager]
Invalid string
# or buffer length (0)')
#
# 4: Errors and python crashes
#
# Traceback (most recent call last):
# File "H:\1-pyodbc-bug\concat-bug\broke.py", line 36, in ?
# row = c.fetchone()
# pyodbc.Error: ('HY090', '[HY090] [Microsoft][ODBC Driver Manager]
Invalid string
# or buffer length (0)')
#
c.execute(sql4)
row = c.fetchone()
print row[0]
if len(row) 1: print row[1]
c.close()
dbs.close()
