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 157: Negative integers are retrieved as positive integers.
7 people starred this issue and may be notified of changes. Back to list
Status:  Complete
Owner:  ----
Closed:  Nov 2011


 
Reported by kaifan0...@hotmail.com, Feb 17, 2011
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.


Feb 20, 2011
#1 yi.codep...@gmail.com
I got the same problem on sqlserver 2005.
Feb 27, 2011
Project Member #2 mkleehammer
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?

Status: Investigating
Labels: -Priority-Medium Priority-Critical
Feb 27, 2011
#3 kaifan0...@hotmail.com
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.

Apr 4, 2011
#4 ch...@fastmail.fm
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.
May 20, 2011
#5 vikram.b...@gmail.com
Here is a patch that seems to fix this. I've only tested it on 64bit Linux and 32bit Linux.

Hope this helps
p
434 bytes   View   Download
Sep 21, 2011
#6 Raf...@gmail.com
definitely helps, but is there a reason this hasn't been merged with the releases?
Nov 2, 2011
Project Member #7 mkleehammer
Fixing in the py3 branch, which is just about to be promoted to the master branch as pyodbc 3.0.1

Status: Complete

Powered by Google Project Hosting