Problem with 38-precision Decimals on SQL Server

[GoogleCodeExporter]

I've found what appears to be a bug in pyodbc when using Decimals of the
maximum precision of 38.  Though obviously it's possible I'm just missing
something. I'm using pyodbc 2.1.7 on Windows 7 x64 with an x64 version of
Python 2.6.4.  Talking to a MS SQL Server 2008 database.   

Simply put, if you try to insert a 38 precision decimal you get this error:

Error: ('HY104', '[HY104] [Microsoft][ODBC SQL Server Driver]Invalid
precision value (0) (SQLBindParameter)')

Using a 37 precision decimal (or less) avoids the problem.  I've attached a
simple unit test script which hopefully demonstrates the problem.  

Both tests should pass, instead only one is passing.  The passing test
inserts a 37 digit decimal, and the failing test inserts a 38 digit one. 
SQL Server supports a maximum precision of 38. 

Hopefully this is enough info to reproduce the problem.

Original issue reported on code.google.com by pland...@gmail.com on 26 Feb 2010 at 10:09

Attachments:

• dectest.py

[GoogleCodeExporter]

OK I did some debugging work.  It looks like the problem is in src/params.cpp 
at line
644 (in v. 2.1.7) where it sets cbColDef, the ColumnSize parameter to 
SQLBindParameter.

It's setting the ColumnSize to simply the length of the string representation 
of the
number.  The problem is that it's including the decimal point, and the negative 
sign
for negative numbers.  This results in a too-long ColumnSize. E.g. if you pass 
a 38
decimal digit negative number, ColumSize becomes 40, and SQL Server only allows 
38
precision.  Thus the error. The solution seems to be to not include the decimal 
and
negative sign (if any) in the ColumnSize.

Attached is a patch which does that and an updated version of that unit test 
that
demonstrates the fix and also tests a negative number and a number with 
scale=0.  I
imagine the actual patch would need to be cleaned up before being committed.  
For one
thing, I'm not sure how kosher it is to directly check the _sign attribute of 
Decimal
numbers, as opposed to calling is_signed().   Also I'd suggest adding 
representative
test cases to the pyodbc project unit tests. 

Hope this helps.

Original comment by pland...@gmail.com on 27 Feb 2010 at 6:08

Attachments:

• pyodbc_params.cpp.diff
• dectest.py

[GoogleCodeExporter]

Thanks for the patch.  I'll add the unit tests as you suggest (always a good 
idea) and apply the patch to 2.1.8, due very soon.

Original comment by mkleehammer on 24 Aug 2010 at 4:07

• Changed state: Started

[GoogleCodeExporter]

Fixed in the v2unicode branch, which will become 2.1.8 as soon as I'm sure it 
is ready.  Thanks again.

Original comment by mkleehammer on 5 Sep 2010 at 6:19

[GoogleCodeExporter]

Fixed in 2.1.8

Original comment by mkleehammer on 6 Sep 2010 at 5:38

• Changed state: Fixed

[GoogleCodeExporter]

Original comment by mkleehammer on 21 Nov 2010 at 4:44

• Changed state: Complete