pyodbc.Error Crash

[Joe Salmeri]

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()