| Issue 157: | Negative integers are retrieved as positive integers. | |
| 7 people starred this issue and may be notified of changes. | Back to list |
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
Feb 27, 2011
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
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
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
Here is a patch that seems to fix this. I've only tested it on 64bit Linux and 32bit Linux. Hope this helps
Sep 21, 2011
definitely helps, but is there a reason this hasn't been merged with the releases?
Nov 2, 2011
Fixing in the py3 branch, which is just about to be promoted to the master branch as pyodbc 3.0.1
Status:
Complete
|