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?
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?
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.
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 -- Meet the Python developers and your c.l.py favorites March 23-25 Come to PyCon DC 2005 http://www.pycon.org/ Steve Holden http://www.holdenweb.com/ -- http://mail.python.org/mailman/listinfo/python-list
