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 14: problems with decimal type on db2
3 people starred this issue and may be notified of changes. Back to list
Status:  Complete
Owner:  ----
Closed:  Dec 2008


 
Reported by axel.b.k...@googlemail.com, Dec 12, 2008
I am successfully connected to a db2-database server (on a mainframe as
well as on a aix workstation).

Doing a select on a table with columns with decimals (with positions after
the decimal point) only yields values without positions after the decimal
point (the value is 10 to the power of the positions after the decimal point).

Example: The value in the table is 3.14 and the type is decimal(n, 2). Then
the result in Python is Decimal("314").




Dec 26, 2008
#1 axel.b.k...@googlemail.com
Addition: This problem occurs under linux as well as under windows. Here is a simple
example, first directly invoked from from db2, following by the same example with pyodbc:

db2 => connect to db2tst

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.5.2
 SQL authorization ID   = DB2INST
 Local database alias   = DB2TST

db2 => select * from weg

KEY         VALUE
----------- -------------
          1         47,11

  1 record(s) selected.

db2 =>


>>> import pyodbc
>>> conn = pyodbc.connect("DSN=db2tst;UID=uid;PWD=pwd")
>>> cur = conn.cursor()
>>> cur.execute("select * from weg")
<pyodbc.Cursor object at 0x7f01eb2a5990>
>>> for i in cur:
...  print i
...
(1, Decimal("4711"))
>>>

Please email me if I can halp you investigating this problem or if you want to reject
this report. Thanks!

Axel
Dec 27, 2008
#2 axel.b.k...@googlemail.com
Found the problem to be the locale settings in pyodbcmodule.cpp. Changing the values
for chDecimal, chGroupSeparator, chCurrencySymbol to the german values resolved the
issue.

But, espacially as I don't own a c-compiler for windows where I mainly use the
library, I would really love these settings to be a install or even better a runtime
option of the package. Perhaps one of the next versions will show this feature.

Thanks a lot for this great package!

Axel

B.t.w. the type changed from "defect" to "wish".
Dec 29, 2008
Project Member #3 mkleehammer
OK, I found the problem.

In pyodbcmodule, init_locale_info() is called when the module is first imported. 
It's job is to determine the correct characters for the decimal point and thousands
separator, specifically for the issue you reported.

The bug is that it is looking for 'decimal' instead of 'decimal_point'.  I'll fix
this and upload a build today.
Status: Started
Labels: -Priority-Medium Priority-High
Dec 29, 2008
Project Member #4 mkleehammer
By the way, if you are using Python 2.6 and are interested, you can use the free
Visual Studio Express compiler: http://www.microsoft.com/express/vc


Dec 30, 2008
#5 axel.b.k...@googlemail.com
I applied your fix under Linux and it worked perfectly!

When will you release a new version with this bug fixed?

Thanks a lot!
Dec 30, 2008
Project Member #6 mkleehammer
Fixed in 2.1.3

Status: Verified
Mar 24, 2009
#7 ichsehew...@gmail.com
I encounter exactly the same behaviour with...
- MS Windows Server 2003 SP2, Regional Settings "German (Austria)"
- Python 2.5.1
- pyodbc 2.1.3
- Teradata 12 RDBMS

If I change regional settings to "English (United States)" selecting DECIMALs works
flawlessly.


Nov 21, 2010
Project Member #8 mkleehammer
(No comment was entered for this change.)
Status: Complete
Dec 2, 2010
#10 montague...@gmail.com
I am using pyodbc 2.1.6 to hit a Progress database. I get the decimal("0.000") results.  I really need the result to be 0.000

Thanks

Sep 19, 2012
#11 f...@reichbier.de
Hi there! I'm getting pretty much the same error with pyodbc 3.0.6 and the DB2 ODBC driver. Trying a query in isql:

SQL> SELECT 40.5 - 1 FROM SYSIBM.SYSDUMMY1
+----------------+
| 1              |
+----------------+
| 39,5           |
+----------------+
SQLRowCount returns -1
1 rows fetched

and pyodbc returns `[(Decimal('395'), )]`.

Passing `LC_ALL=C` to the pyodbc test script shows `[(Decimal('39.5'), )]` (the correct result, I think) though.

Since I'm not that skilled with databases -- is this expected behaviour? The locale on the client machine is de_DE.utf8, but that's not necessarily the same locale as on the database server. Is there any way to avoid having to specify the `C` locale to make the example above work?

Cheers!

Friedrich

Powered by Google Project Hosting