"Invalid precision or scale value" error from SQL Server with pypyodbc for Decimal('0.00'), Decimal('0.12')

Question

I am developing an application in python. I am inserting some financial data into database. I have approx. 1300 tables all with same schema, one table for each company.

I am using pypyodbc 1.3.3(latest).

I have Decimal as datatype. I am using precision of as Decimal(8,2). In my table, datatypes are DATE, DECIMAL, TINYINT and INT. I am inserting the following row in one of the table from my python program.

['05-JAN-2015', Decimal('30.90'), Decimal('31.40'), Decimal('30.40'), Decimal('30.85'), None, Decimal('0.00'), Decimal('0.00'), None, Decimal('0.00'), Decimal('0.00'), None, Decimal('0.00'), Decimal('0.00'), None, None, Decimal('30.88'), Decimal('0.00'), Decimal('0.00'), Decimal('0.00'), Decimal('0.00'), 32024, 321, Decimal('0.00'), Decimal('0.00'), None, Decimal('0.00'), None, Decimal('0.00')]

Datatype of 05-JAN-2015 is DATE, All None have datatype as TINYINT, and value 32024, 321 have datatype as INT. But still I am getting the SQL exception as

  File "C:\Python27\lib\site-packages\pypyodbc.py", line 1470, in execute
    self._BindParams(param_types)
  File "C:\Python27\lib\site-packages\pypyodbc.py", line 1433, in _BindParams
    check_success(self, ret)
  File "C:\Python27\lib\site-packages\pypyodbc.py", line 986, in check_success
    ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi)
  File "C:\Python27\lib\site-packages\pypyodbc.py", line 964, in ctrl_err
    raise Error(state,err_text)
Error: (u'HY104', u'[HY104] [Microsoft][SQL Server Native Client 10.0]Invalid precision or scale value')

When I try to insert the same data directly into SQL server from management studio, then It get inserted successfully. I have cross checked the datatype of the variables and all are perfect.

As I am inserting data into ~1300 tables in a loop somewhat like

for i in range(tables_number):
    insertion_data = calculate_values()
    insert_into_db(insertion_data)

,does that causes the problem? I have also tried by using the delay of 0.1 second in a loop.

Does Anybody have any idea? Is this bug in driver? Or Is there any other cause that I am missing?

UPDATE:

After debugging the code, I found in the script pypyodbc.py, function SQLBindParameter(<many parameters>) returns -1 which is SQL_ERROR for parameter Decimal('0.00') or Decimal(0.12) or Decimal(-0.12) means decimal of the form 0.*

score 2 · Accepted Answer · answered May 13 '15 at 09:25

It appears to be a bug in pypyodbc. This code, using pypyodbc 1.3.3, fails just as you describe ...

from decimal import *
import pypyodbc
connStr = "DSN=myDb_SQLEXPRESS;"
cnxn = pypyodbc.connect(connStr, autocommit=True)
crsr = cnxn.cursor()
sql = "INSERT INTO Invoices (InvoiceAmount) VALUES (?)"
invamt = Decimal('0.01')
crsr.execute(sql, [invamt])
crsr.close()
cnxn.close()

... but doing the exact same thing using pyodbc works okay:

from decimal import *
import pyodbc
connStr = "DSN=myDb_SQLEXPRESS;"
cnxn = pyodbc.connect(connStr, autocommit=True)
crsr = cnxn.cursor()
sql = "INSERT INTO Invoices (InvoiceAmount) VALUES (?)"
invamt = Decimal('0.01')
crsr.execute(sql, [invamt])
crsr.close()
cnxn.close()

Furthermore, if I run the pypyodbc version against an identical table in a MySQL database ...

from decimal import *
import pypyodbc
connStr = "DSN=usbMySQL;"
cnxn = pypyodbc.connect(connStr, autocommit=True)
crsr = cnxn.cursor()
sql = "INSERT INTO Invoices (InvoiceAmount) VALUES (?)"
invamt = Decimal('0.01')
crsr.execute(sql, [invamt])
crsr.close()
cnxn.close()

... the value that gets inserted into the database is 0.10, not 0.01.

Oh.. I spent 2 days in trying to change complete database schema to increase the size of the decimal digits and trying out various possibilities got from another forums. Thanks very much, moving to pyodbc solved it — ajay_t, May 13 '15 at 10:04

"Invalid precision or scale value" error from SQL Server with pypyodbc for Decimal('0.00'), Decimal('0.12')

1 Answers1