 edited description
Decimals being saved with only four decimal places
When I use djangomssql, all DecimalFields are being saved with 4 decimal places in the database, even through the fields have far greater precision: all of them are decimal(28, 13). Everything works fine with other databases (MySQL and Postgres).
This was driving me mad, and I began debugging the save code, digging as deep as I could. I've managed to trace it down to the conversion from Decimal to adDecimal, which happens in Cursor.execute() in dbapi.py.
If I pause the Aptana debugger in line 520
_configure_parameter(p, value) # line 519 self.cmd.Parameters.Append(p) # line 520
when it's handling a Decimal parameter, and run the following command in the Console window
print value, value.__class__, p, p.__class__
I get
1643991.703308 <class 'decimal.Decimal'> 1643991,7033 win32com.client.CDispatch
which is obviously wrong. I don't know how to go further because it's crossing the C layer.
When I query the field above through SQL Server Management Studio, the result is 1643991.7033000000000. For the record, if I run a UPDATE query manually in Management Studio (UPDATE portfolio_snapshot SET shares = 1643991.703308 WHERE id = 20
), the field gets saved with the appropriate number of places.
Possibly useful information:
 Running djangomssql 1.4 with Django 1.5, Python 2.7
 Happens in both Windows 8 and Windows Server 2008 R2
 Using SQL Server 2008 R2
 Using 'sqlncli10' as the provider
Comments (11)


This appears to be either a COM or ADO limitation. A similar issue was previously encountered when adding support for datetime2 and datetimeoffset data types. The most likely fix is to pass the Decimal value to SQL as a string. I predict a string format headache dealing with Decimals like '1.2E+5'.

Thanks for the directions. I was able to make it work by changing the _configure_parameter() method, although I'm not sure I did it the best way possible.
I changed the elif starting in dbapi.py:199 from
elif isinstance(value, decimal.Decimal): p.Value = value exponent = value.as_tuple()[2] digit_count = len(value.as_tuple()[1]) if exponent == 0: p.NumericScale = 0 p.Precision = digit_count elif exponent < 0: p.NumericScale = exponent p.Precision = digit_count if p.Precision < p.NumericScale: p.Precision = p.NumericScale elif exponent > 0: p.NumericScale = 0 p.Precision = digit_count + exponent
to
p.Type = adBSTR exponent = value.as_tuple()[2] if exponent < 0: s = "%%.%df" % exponent % value else: s = "%d" % value p.Value = s p.Size = len(s)
How should I proceed from here? Is the snippet above enough for you to test and include in the lib, or do you want me to create a fork, commit and make a pull request?

Printing as a float causes the typical float rounding issues. I've started to look in to ways to generate a string usable by MS SQL.

 changed status to resolved
Decimals are passed through ADO as strings to avoid rounding at four places. Fixed
#55→ <<cset 8c1e5bf2e315>>

"I predict a string format headache dealing with Decimals like '1.2E+5'."
Sadly, you were right. The solution implemented in 8c1e5bf2e315 fails with that kind of number.
>>> from sqlserver_ado.dbapi import format_decimal_as_string, decimal >>> number = decimal.Decimal('28.2') / decimal.Decimal('0.01') >>> print number 2.82E+3 >>> print format_decimal_as_string(number) 282.
I suggest adding an ifelse checking for the exponent (somewhat like in the solution I proposed initially):
def new_format_decimal_as_string(value): """ Convert a decimal.Decimal to a fixed point string. Code borrowed from Python's moneyfmt recipe. https://docs.python.org/2/library/decimal.html#recipes """ sign, digits, exp = value.as_tuple() if exp > 0: return "%d" % value result = [] digits = list(map(str, digits)) build, next = result.append, digits.pop for i in range(exp): build(next() if digits else '0') build('.') if not digits: build('0') while digits: build(next()) if sign: build('') return ''.join(reversed(result)) >>> print new_format_decimal_as_string(number) 2820

 changed status to open

The formatting you proposed initially doesn't work.
"%%.%df" % exponent % value
People use decimal instead of float because they need the extra precision. Converting the decimal to a float on save stores the wrong value in many situations.

I realized that; that's why I didn't include that part in new_format_decimal_as_string(). The only part of my initial proposal that I used is this:
if exp > 0: return "%d" % value
When exp is <= 0, I kept your original fix's code.

It's really hard to see what you changed when you post snippets of code in a case. Any chance you can send a pull request?

 changed status to resolved
PR merged.
 Log in to comment