J
Joe
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
print
print 'Column DataTypes:'
print
for i in range(len(c.description)):
print c.description[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?
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 'mxODBC SQL DataTypes:'
for i in mx.ODBC.Windows.sqltype:
print i, mx.ODBC.Windows.sqltype
print 'Column DataTypes:'
for i in range(len(c.description)):
print c.description[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?