cursor returns incorrect numerical value

[smoller]

Running on

Python 2.7.8 (v2.7.8:ee879c0ffa11, Sep  3 2014, 17:02:16) 
[GCC 4.4.7 20120313 (Red Hat 4.4.7-4)] on linux2

Connecting to PosgreSQL DB v 9.4 of form

create table m {
    day DATE,
    string VARCHAR,
    volume NUMERIC(16,0)
}

Running isql using Postgres UNICODE driver returns correct values

$ isql database uid ps  < echo "select * from m LIMIT 1;"

-----------------------------------------------------------------------+-------------------+
| day |string  | volume |
| 2015-04-30| blah | 2029139           |

Using pyodbc

...
conn = pyodbc.connect(host)
query = "select * from m LIMIT 1;"
cursor= conn.cursor()
cursor.execute(query)
results = cursor.fetchone()
print results
conn.close()

outputs

(datetime.date(2015, 4, 30), 'blah', Decimal('2'))

[mkleehammer]

What version of pyodbc? And are you using unixODBC? If so, did you compile it yourself with the default flags or did you change any?

Due to driver bugs I read decimal columns as Unicode instead of a binary structure. My guess is that I'm reading it and the Unicode characters are wider than I expect. unixODBC always uses 2-byte UCS2, so I'll look at each character in 2-byte chunks. If the data is actually 4-byte, it is going to look like [ "2" NUL "0" NUL "2" NUL ... ]. So would stop at the first null terminator and end up with just "2".

[smoller]

The error occurs through versions 3.0.2 - 3.0.10. I am using unixODBC.

I think what you've described is happening. This is what I get in the logs:

[SQLGetData][139766695343872]PGAPI_GetData: enter, stmt=0x22b9ae0 icol=3
[139766695343872]     num_rows = 1
[139766695343872]     value = '2029139'
[139766695343872]**** PGAPI_GetData: icol = 2, target_type = -8, field_type = 1700, value = '2029139'
[139766695343872]copy_and_convert: field_type = 1700, fctype = -8, value = '2029139', cbValueMax=200
[139766695343872]DEFAULT: len = 28, ptr = '2'
[139766695343872]    SQL_C_WCHAR, default: len = 28, cbValueMax = 200, rgbValueBindRow = '2'

Anything I can do to fix this?

[v-chojas]

Are you still experiencing this with latest version of pyODBC? If so, please post an ODBC trace for more investigation.

[Learner-B]

Is this issue resolved? I have latest version of PYODBC - 4.0.30. I am still getting the same issue when write data from SQL Server which having different datatypes into File?

Instead of getting Date and Decimal as below
'2020-03-01 00:00:00'|10.10

writing the data into file as
datetime.datetime(2019| 9| 18| 0| 0) | Decimal('0')

[gordthompson]

Unable to reproduce with (reasonably) current versions:

Python version: 3.6.5 (default, Apr 1 2018, 05:46:30) [GCC 7.3.0]
pyodbc version: 4.0.30
ODBC driver: psqlodbcw.so [10.01.0000]

crsr.execute("INSERT INTO m (day, string, volume) VALUES ('2015-04-30', 'blah', 2029139)")
print(crsr.execute("SELECT * FROM m").fetchone())
# (datetime.date(2015, 4, 30), 'blah', Decimal('2029139'))