mx.ODBC 2.0.7 bug?

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