[egenix-users] Re: mx.ODBC 2.0.7 bug?

[M.-A. Lemburg]

Thanks for forwarding the message, Steve.

Steve Holden wrote:
> Joe wrote:
> 
>> Python 2.4
>> Windows XP SP2
>> MS Access 2000
>> mx.ODBC 2.0.7
>>
>> Problem data truncation occuring (here's the actual error message):
>>
>> mxODBC.Warning: ('01004', 5, '[Microsoft][ODBC Microsoft Access 
>> Driver] String data, right truncated on column number 3 (Expr1002)', 
>> 3326)
>>
>> I believe that  have found a bug in mx.ODBC not properly assigning the 
>> correct data type to a column.
>>
>> Here is a sample script that demonstrates the problem and why I think 
>> it is being handled incorrectly:
>>
>> # NOTE memo1 and memo2 are memo fields in the test_table
>>
>> import mx.ODBC.Windows
>>
>> dbs = mx.ODBC.Windows.connect('database', '', '')
>>
>> sql = "select memo1, memo2, memo1 & ' ' & memo2 from test_table where 
>> record_id = 1"
>>
>> c   = dbs.cursor()
>>
>> c.execute(sql)
>>
>> print
>> print 'mxODBC SQL DataTypes:'
>> print
>>
>> for i in mx.ODBC.Windows.sqltype:
>>     print i, mx.ODBC.Windows.sqltype[i]
>>
>> print
>> print 'Column DataTypes:'
>> print
>>
>> for i in range(len(c.description)):
>>     print c.description[i][1]
>>
>> c.close()
>> dbs.close()
>>
>> When you run this script it produces the following output:
>>
>> mxODBC SQL DataTypes:
>>
>> 1 CHAR
>> 2 NUMERIC
>> 3 DECIMAL
>> 4 INTEGER
>> 5 SMALLINT
>> 6 FLOAT
>> 7 REAL
>> 8 DOUBLE
>> 9 DATE
>> 10 TIME
>> 11 TIMESTAMP
>> 12 VARCHAR
>> 91 TYPE_DATE
>> 92 TYPE_TIME
>> 93 TYPE_TIMESTAMP
>> -1 LONGVARCHAR
>> -10 WCHAR_LONGVARCHAR
>> -9 WCHAR_VARCHAR
>> -8 WCHAR
>> -7 BIT
>> -6 TINYINT
>> -5 BIGINT
>> -4 LONGVARBINARY
>> -3 VARBINARY
>> -2 BINARY
>>
>> Column DataTypes:
>>
>> -1
>> -1
>> 12
>>
>> From the output you can see that memo1 and memo2 are both determined 
>> to be of type longvarchar (-1) but when the columns are concatenated 
>> together the resulting column is given a type of varchar (12).  
>> Obviously this is why the data truncation is occurring.
>>
>> Is this a known problem?

No, but then the MS Access ODBC drivers are always full of
surprises :-) (things have gotten a lot better recently, though).

>> I can work around the problem using a converter function:
>>
>> def converter(position, sqltype, sqllen):
>>     print 'in :', position, sqltype, sqllen
>>     if position == 2:
>>         sqltype = -1
>>         sqllen  = 1073741823
>>     print 'out:', position, sqltype, sqllen
>>     return sqltype, sqllen
>>
>> and then using:
>>
>> c.setconverter(converter)
>>
>> but shouldn't mx.ODBC have properly assigned the correct sqltype and 
>> sqllen for the concatenated memo columns in the first place?

mxODBC gets the select column information from the ODBC driver
and then fetches the data rows based on that information.

In the above case, the Access ODBC driver tells mxODBC that
the third column is of type VARCHAR and passes it some
length information that obviously is wrong.

The only way to fix this is using a converter like you did.

BTW, do you have more info on the length of the memo field
contents and the value that Access passes back as sqllen
for the third column ?

It is possible that this is some off-by-one bug in the driver.
We could work around that by creating a larger buffer to
hold the data.

> This is a very nice piece of deduction, and I am copying this message to 
> you and to the egenix-users list, since that's generally a reliable way 
> to get Marc-Andre's attention.

Indeed :-) (don't read c.l.p that often these days)

> I'm not convinced that it demonstrates an mxODBC bug, since I don't 
> believe that the ampersand is actioned by the drivers, but I'm not the 
> best one to be authoritative about this.
> 
> others-who-read-this-reply-will-ly y'rs  - steve

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Mar 03 2005)
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::