[DB-SIG] mxODBC is truncating BLOBs when reading them

[M.-A. Lemburg]

Friedlander Paul wrote:
> Thanks for getting back to me.
> 
> First of all, here is the traceback:
> 
> 
>>>>c.execute("SELECT raw_data FROM datasets LIMIT 1")
>>>>a = c.fetchall()
>>>
> Traceback (most recent call last):
>   File "<interactive input>", line 1, in ?
> Warning: ('01004', -2, 'Fetched item was truncated.', 3480)

Ok, this is a warning which gets thrown as Python exception.
Now, this may or may not be an error. Unfortunately, you
can't see the data which the ODBC driver truncated.

To disable reporting of warnings, you'd have to recompile
mxODBC on Windows, or I could send you a beta version binary
of mxODBC 2.1.0 which allows defining an error handler for this
purpose.

To be able to look deeper into the problem, you should
also run a debug version of mxODBC which produces a
very verbose log file about what is going on underneath.

I think it's best if we take this off the list. Could you
also send me a short script which creates a table using
the BYTEA data types, inserts some data and then does the
above query ? This would aid in trying to narrow down the
cause.

> As for the setup. I am using ActivePython V2.2 and the latest stable release
> of mxODBC. Postgres is running
> on a Linux box and I am running the client on Windows 2000. The ODBC driver
> (running on windows) is from insight distribution systems (version
> 7.01.00.09, PSQLODBC.DLL, 11/27/01).
> 
> I was originally using the ODBC driver that comes with ActivePython. It
> worked correctly (the data was returned correctly). However, reading BYTEA
> columns seemed to make it unstable and crashed Python. All other operations
> including writing BYTEA columns ran flawlessly. As a work around, I tried
> mxODBC. I can query other types of fields but get the traceback above when I
> try to query a BYTEA field. I haven't tried writing to a BYTEA field. 
> 
> The binary data is typically between 15k and 20k.
> 
> For the time being, I have ported the client application to linux using
> psycopg but this isn't my prefered solution (it works fine in Linux).
> 
> Per the Postgres documentation, I am escaping "\", "'", and \x00. I think
> that I have discovered that 0x0D characters are mysteriously disappearing
> too and am looking into escaping them also.

This hints into a different direction: you should always
try to use bound parameters in SQL statements you pass to
.execute(). The DB API module will then do the proper
escaping for you. With mxODBC you don't even have to worry
about different database backends since the ODBC drivers will
quote the data for you.

> I hope this gives you enough information to point me in a direction.
> 
> Thanks again.
> 
> 
> -----Original Message-----
> From: M.-A. Lemburg [mailto:mal@lemburg.com]
> Sent: Tuesday, July 16, 2002 5:28 PM
> To: Friedlander Paul
> Cc: 'db-sig@python.org'
> Subject: Re: [DB-SIG] mxODBC is truncating BLOBs when reading them
> 
> 
> Friedlander Paul wrote:
> 
>>I am using mxODBC to retrieve data from a Postgres database. I am using
> 
> the
> 
>>latest ActivePython distribution.
>>
>>When I read data from a column with BYTEA data I get a warning and am told
>>that the data was truncated. I found a comment that this used to be a
>>problem with MySQL databases but has been fixed.
> 
> 
> Just guessing here since you don't provide enough information
> (traceback, log file, versions, etc.): this could be related
> to a network buffer problem or a problem with the ODBC driver
> for Postgres. mxODBC doesn't truncate the data -- it's the
> driver that's truncating it.
> 
> 
>>I am using the Windows sub-object and am accessing the database through an
>>ODBC converter provided by insight (running in the windows control panel).
>>
>>Can anyone shed light on this?
> 
> 
> Please post the traceback and give some hint about the size
> of the data you are requesting.
> 
> Thanks,


-- 
Marc-Andre Lemburg
CEO eGenix.com Software GmbH
_______________________________________________________________________
eGenix.com -- Makers of the Python mx Extensions: mxDateTime,mxODBC,...
Python Consulting:                               http://www.egenix.com/
Python Software:                    http://www.egenix.com/files/python/