My favorites | Sign in
Project Home Downloads Wiki
READ-ONLY: This project has been archived. For more information see this post.
Search
for
  Advanced search   Search tips   Subscriptions
Issue 91: Problem with 38-precision Decimals on SQL Server
1 person starred this issue and may be notified of changes. Back to list
Status:  Complete
Owner:  ----
Closed:  Sep 2010


 
Reported by pland...@gmail.com, Feb 26, 2010
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.

dectest.py
2.0 KB   View   Download
Feb 26, 2010
#1 pland...@gmail.com
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.
pyodbc_params.cpp.diff
563 bytes   View   Download
dectest.py
2.6 KB   View   Download
Aug 24, 2010
Project Member #2 mkleehammer
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.

Status: Started
Sep 4, 2010
Project Member #3 mkleehammer
Fixed in the v2unicode branch, which will become 2.1.8 as soon as I'm sure it is ready.  Thanks again.

Sep 6, 2010
Project Member #4 mkleehammer
Fixed in 2.1.8

Status: Fixed
Nov 21, 2010
Project Member #5 mkleehammer
(No comment was entered for this change.)
Status: Complete

Powered by Google Project Hosting