tanrj / pyodbc

Automatically exported from code.google.com/p/pyodbc
MIT No Attribution
0 stars 0 forks source link

Negative integers are retrieved as positive integers. #157

Closed GoogleCodeExporter closed 8 years ago

GoogleCodeExporter commented 8 years ago
What steps will reproduce the problem?

1. In a MySQL database, make a table, say T, with an integer column, say x. 
Insert some negative integers into that column.
2. Download and install a MySQL ODBC driver. In .odbc.ini in your home 
directory, create a DSN for the MySQL database, e.g.:

[MYSQL]
Description = MySQL
Driver      = /usr/lib64/libmyodbc5.so
Server                  = localhost
Port                    = 3306
User                = root
Password                = mysqlroot
Database            = mydb

3. Write a Python program that retrieves the values in column x in table T 
using pyodbc, e.g.:

import pyodbc

cnxn = pyodbc.connect("DSN=MYSQL")
cursor = cnxn.cursor()
cursor.execute("select x from T")

while True:
    results = cursor.fetchall()
    print results
    if not cursor.nextset():
        break

cursor.close()

What is the expected output? What do you see instead?

If the column x contains the integers -74218, -74217 and -74216, the Python 
code in step 3 above should give the following output:

[(-74218, ), (-74217, ), (-74216, )]

However, I got this output:

[(4294893078, ), (4294893079, ), (4294893080, )]

What version of the product are you using? On what operating system?

pyodbc 2.1.8 (installed through pip) on Fedora 14 64-bit with MySQL 5.1.52 and 
mysql-connector-odbc.

Please provide any additional information below.

I got the correct output with pyodbc 2.1.7.

Original issue reported on code.google.com by kaifan0...@hotmail.com on 18 Feb 2011 at 1:14

GoogleCodeExporter commented 8 years ago
I got the same problem on sqlserver 2005.

Original comment by yi.codep...@gmail.com on 21 Feb 2011 at 3:09

GoogleCodeExporter commented 8 years ago
On SQL Server 2005?  Are you using a 64-bit version of Python?

For both, what data type are you using for your column?  Is it possible the 
data type is an unsigned type?

Original comment by mkleehammer on 28 Feb 2011 at 12:13

GoogleCodeExporter commented 8 years ago
The data type I am using is INT(11) in MySQL. It is not an unsigned type as I 
was able to retrieve the values correctly in MySQL Workbench, and as I said, it 
worked with pyodbc 2.1.7.

Original comment by kaifan0...@hotmail.com on 28 Feb 2011 at 12:42

GoogleCodeExporter commented 8 years ago
I'm observing this bug with MS SQL Server 2008 as well. I have to access some 
tables in which the maintainer has rows with id=-1. They show up in Python as 
4,294,967,295. The column is defined as [id] [int] NOT NULL, and it is a PK:

>>> cursor.execute("SELECT [id] FROM [journal_categories] ORDER BY 
[id]").fetchall()
[(4294967295, ), (0, ), (2, ), (3, ), (4, )]

When I connect to the database with Microsoft's management GUI to run the same 
query, I see the expected result: -1,0,2,3,4

I'm using a 64-bit build of Python 2.6.4 on Fedora Core 13, and I'm using 
PyODBC 2.1.8.

Original comment by ch...@fastmail.fm on 4 Apr 2011 at 8:42

GoogleCodeExporter commented 8 years ago
Here is a patch that seems to fix this. I've only tested it on 64bit Linux and 
32bit Linux.

Hope this helps

Original comment by vikram.b...@gmail.com on 20 May 2011 at 12:59

Attachments:

GoogleCodeExporter commented 8 years ago
definitely helps, but is there a reason this hasn't been merged with the 
releases?

Original comment by Raf...@gmail.com on 21 Sep 2011 at 2:41

GoogleCodeExporter commented 8 years ago
Fixing in the py3 branch, which is just about to be promoted to the master 
branch as pyodbc 3.0.1

Original comment by mkleehammer on 2 Nov 2011 at 10:52