pyodbc data corruption problem

[mensanator at aol.com]

On May 18, 6:46 pm, "Joe Salmeri" <JoeSalm... at hotmail.com> wrote:
> I have found a data corruption problem with pyodbc.
>
> OS = Windows XP SP2
> DB = Microsoft Access XP
>
> PROBLEM:
>
> When selecting columns from a table that are of type Memo the value
> returned is padded with a bunch of null characters at the end.
>
> The problems does not seem to occur until the length of the Memo column
> exceeds 2048 bytes.

Interesting. MS-Access has had a bug about Memo fields
ever since Version 2.0 (the last time it worked). I was trying
to use a Memo field on a form and append short status messages.
This mysteriously stopped working in the version following 2.0
(Access 95?) and has never worked since. Seems there is some
kind of 2048 buffer involved that was created by some punk
MicroSoft gave the lowly job of software revision to while
those more educated did more important things.

My guess is you won't find a Python solution short of
accepting what Access gives you and dealing with it.

>
> I have attached several scripts to help demonstrate the problem.
>
> To recreate the problem:
>
> 1. Create a blank Access database named test.
> 2. Create a ODBC DSN named test for that database
> 3. Run the createtable.py script to create the table
> and load it with the dummy data
> 4. Run the broke.py script to show the problem.
>
> The issue is when the data value is > 2048 bytes.
>
> The size in the createtable.py is 2046 bytes plus 3 bytes at the end that
> contain "JOE" for a total of 2049 bytes.
>
> If you change it from 2046 to 2045 (or less) then the problem does not
> occur.
>
> #
> # createtable.py script
> #
>
> import pyodbc
>
> dbs = pyodbc.connect('dsn=test')
>
> c   = dbs.cursor()
>
> try:
>     sql = 'drop table test_memo'
>     c.execute(sql)
>     dbs.commit()
> except:
>     # ignore drop table failure
>     pass
>
> sql = 'create table test_memo (c1 int not null, c2 memo not null)'
>
> c.execute(sql)
>
> dbs.commit()
>
> sql = 'insert into test_memo values(1, ?)'
>
> c2_value = '1' * 2046
>
> c2_value = '%sJOE' % (c2_value)
>
> c.execute(sql, (c2_value,))
>
> dbs.commit()
>
> c.close()
> dbs.close()
>
> #
> # broke.py script
> #
>
> import pyodbc
>
> dbs = pyodbc.connect('dsn=test')
>
> c   = dbs.cursor()
>
> sql = 'select c2, len(c2) as c2_db_len from test_memo where c1 = 1'
>
> c.execute(sql)
>
> row = c.fetchone()
>
> (
>     c2,
>     c2_db_len
> ) = row
>
> print repr(c2)
>
> print 'c2 length        :', len(c2)
> print 'c2_db_length     :', c2_db_len
>
> print 'before nul length:', len(c2[0:c2.find('\x00')])
>
> c.close()
> dbs.close()