Pyodbc interprets SQL_REAL to float instead of decimal

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Read a SQL_REAL value of .105 from any SQL database using pyodbc
2. Print the value to log or screen from python code. 

What is the expected output?

.105

What do you see instead?

.104999996722

Original issue reported on code.google.com by devone...@gmail.com on 1 Dec 2011 at 10:41

[GoogleCodeExporter]

According to the ODBC specification, the SQL_REAL, SQL_FLOAT, and SQL_DOUBLE 
are approximate types, not 
exact types, so floating point would be correct.

http://msdn.microsoft.com/en-us/library/windows/desktop/ms710150(v=VS.85).aspx
http://msdn.microsoft.com/en-us/library/windows/desktop/ms712567(v=VS.85).aspx

Can you change your datatype to a decimal or numeric?

I'm going to close this since I'm pretty sure it is correct.  If you find 
something else, please reopen this issue.  Thanks.

Original comment by mkleehammer on 8 Dec 2011 at 3:02

• Changed state: NoFix

[GoogleCodeExporter]

Reading your references, SQL_Real is indeed a floating-pointing type. 

However, in Oracle it seems to have much more precision (I think it's 63 bits). 
This means that pyodbc is frequently performing a lossy conversion to a 
less-precise native float type. 

I can't change my datatype. Sometimes the database one is pulling from is used 
by multiple applications, and is owned and operated by people who work in a 
different department, and live in a different country. 

The workaround, for all you folks with the same problem who landed here from 
googlesearch, is as follows:

Select cast(foo_SQL_REAL_value as String) as foo_SQL_REAL_value, ....

Then parse the string to a Decimal value in native Python code. 

Original comment by devone...@gmail.com on 8 Dec 2011 at 9:41

[GoogleCodeExporter]

You may also consider casting to a different numeric type that pyodbc will 
automatically cast to a Decimal.

Original comment by mkleehammer on 8 Dec 2011 at 11:48