MSNumeric.bind_processor fails to send decimal values

The MS-SQL backend fails to insert values with decimals.

This is illustrated by this little program :

from sqlalchemy import MetaData, create_engine
from sqlalchemy.schema import Table, Column
from sqlalchemy.types import Numeric

import decimal

metadata = MetaData()

t = Table('test', metadata,
        Column('adecimal', Numeric(asdecimal=True)))

engine = create_engine("mssql://tester:tester@soleil/sf_test")

t.create(bind=engine)
try:
    engine.execute(t.insert(), {'adecimal': decimal.Decimal('4.1')})
finally:
    t.drop(bind=engine)

Running it result in the following error:

Traceback (most recent call last):
  File "test.py", line 16, in <module>
    engine.execute(t.insert(), {'adecimal': decimal.Decimal('4.1')})
  File "c:\python25\lib\site-packages\sqlalchemy-0.4.1-py2.5.egg\sqlalchemy\engine\base.py", line 1126, in execute
    return connection.execute(statement, *multiparams, **params)
  File "c:\python25\lib\site-packages\sqlalchemy-0.4.1-py2.5.egg\sqlalchemy\engine\base.py", line 789, in execute
    return Connection.executors[c](c)(self, object, multiparams, params)
  File "c:\python25\lib\site-packages\sqlalchemy-0.4.1-py2.5.egg\sqlalchemy\engine\base.py", line 840, in execute_clauseelement
    return self._execute_compiled(elem.compile(dialect=self.dialect, column_keys=keys, inline=len(params) > 1), distilled_params=params)
  File "c:\python25\lib\site-packages\sqlalchemy-0.4.1-py2.5.egg\sqlalchemy\engine\base.py", line 852, in _execute_compiled
    self.__execute_raw(context)
  File "c:\python25\lib\site-packages\sqlalchemy-0.4.1-py2.5.egg\sqlalchemy\engine\base.py", line 864, in __execute_raw
    self._cursor_execute(context.cursor, context.statement, context.parameters[0](0), context=context)
  File "c:\python25\lib\site-packages\sqlalchemy-0.4.1-py2.5.egg\sqlalchemy\engine\base.py", line 880, in _cursor_execute
    raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.DataError: (DataError) ('22001', '[22001](22001) [Microsoft](Microsoft)[SQL Server Driver](ODBC)Troncation \xe0 droite de la cha\xeene de donn\xe9es (0)') u'INSERT INTO test (adecimal) VALUES (?)' ['4.1']('4.1')

Note: the message from MSSQL means: "Data string truncated on the right"

From what I understood, the value is sent as a string by the MSNumeric.bind_processor method. Sending it as a float instead (patch below) fix the issue, but probably not in a suitable way, as I guess the float type may not be precise enough and result in information loss.

Index: lib/sqlalchemy/databases/mssql.py
===================================================================
--- lib/sqlalchemy/databases/mssql.py   (révision 3952)
+++ lib/sqlalchemy/databases/mssql.py   (copie de travail)
@@ -67,7 +67,7 @@
                 # Not sure that this exception is needed
                 return value
             else:
-                return str(value)
+                return float(value)
         return process

     def get_col_spec(self):

Comments (3)