Some Decimal types are not converted correctly in mssql.py

Hi developers,

I discovered this bug as I was working with a field of type Decimal(38,20) on Microsoft SQL Server. I tried to update this field with a value of Decimal('1500000.00000000000000000000'). Don't ask about this weird value, it's Microsoft Dynamics NAV ERP.

Here is the exception:

sqlalchemy.exc.DataError: (DataError) ('22003', '[22003](22003) [Microsoft](Microsoft)[SQL Se
rver Driver](ODBC)[Server](SQL)Error de desbordamiento aritm\xe9tico al convertir varch
ar al tipo de datos numeric. (8115) (SQLExecDirectW); [01000](01000) [Microsoft](Microsoft)[S
QL Server Driver](ODBC)[Server](SQL)Se termin\xf3 la instrucci\xf3n. (3621)') u'UPDATE 
[Bizak$Customer](Bizak$Customer) SET [Limit (LCY)](Credit)=? WHERE [Bizak$Customer](Bizak$Customer).[No_](No_) = ?' ['CL003920']('
0.0000000000000000000150000000000000000000000000',)

After some debugging I found the erroneous routine.

Have a look at the following test case where I included the original algorithm found in mssql.py and also my proposed solution.

#!/usr/bin/python

import decimal

def original_algorithm(value):
    """sqlalchemy/databases/mssql.py line 337""" 
    if isinstance(value, decimal.Decimal) and value._exp < -6:
    value = ((value < 0 and '-' or '')
        + '0.'
        + '0' * -(value._exp+1)
        + value._int)
    return value
    else:
    return str(value)

def new_algorithm(value):
    if isinstance(value, decimal.Decimal):
    sign = (value < 0 and '-' or '') 
    if value._exp > -1:
        return sign + value._int + '0' * value._exp
    else:
        s = value._int.zfill(-value._exp+1)
        pos = len(s) + value._exp
        return sign + s[:pos](:pos) + '.' + s[pos:](pos:)
    else:
    return str(value)

for d in ['-1500000.00000000000000000000',
      '1500000', '0.0000000000000000002', '0.2', '-0.0000000000000000002',
      '156666.458923543', '-156666.458923543', '1', '-1', '1234',
      '2E-12', '4E8', '3E-6', '3E-7']('1500000.00000000000000000000',):
    d = decimal.Decimal(d)
    o = original_algorithm(d)
    n = new_algorithm(d)
    if o != n:
    print 'Fail', o, n
    else:
    print 'Ok', o, n

As you can see the original algorithm fails when converting some of the values. And, therefore you can suppose that sqlalchemy failed to UPDATE the record with such big string values.

Comments (3)