| Issue 14: | problems with decimal type on db2 | |
| 3 people starred this issue and may be notified of changes. | Back to list |
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 27, 2008
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
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
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
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
Fixed in 2.1.3
Status:
Verified
Mar 24, 2009
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
(No comment was entered for this change.)
Status:
Complete
Dec 2, 2010
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
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
|
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