pyodbc data corruption problem

[Joe Salmeri]

Thank you for your response but this is not an Access problem.

The exact same code using mx.ODBC or using the old odbc.py that comes with 
the win32 files works fine.

It only fails with pyodbc.

<mensanator at aol.com> wrote in message 
news:1179533525.685271.43670 at n59g2000hsh.googlegroups.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()
>
>