mx.ODBC 2.0.7 bug?

[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[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?

[Steve Holden]

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?

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/

[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 ! ::::

[Joe]

Hi Steve,

Thanks, I find you get much better help when you provide the details needed
for your case instead of expecting others to invest alot of their time
before you did your homework.

Thanks for sending it over to Marc. Last time I found a bug in the mx stuff
(YEARS AGO) I sent it directly to Marc but was trying not to bother him
until I tried the list first.

One point I forgot to mention in the previous reply is that AFTER the
converter function is setup and used if you examine the
cursor.description[column][1] values it still has the old sqltype and
sqllen. Not sure if that would be considered a bug or not? I would have
expected that the converter hook would have also modified the description
info to match what the converter hook did.

Thanks again for your assistance.

Regards,

Joe

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/