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 ! :::: -- http://mail.python.org/mailman/listinfo/python-list
