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