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 186: BIGINT problem on 32-bit architeture
1 person starred this issue and may be notified of changes. Back to list
Status:  NoFix
Owner:  ----
Closed:  Jul 2011


 
Reported by mattias....@gmail.com, Jul 1, 2011
What steps will reproduce the problem?
======================================

Python 2.6.2 (r262:71605, Apr 14 2009, 22:40:02) [MSC v.1500 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> cnx = pyodbc.connect("DSN=mytestdb")
>>> cur = cnx.cursor()
>>> cur.execute("CREATE TABLE t (value BIGINT)")
<pyodbc.Cursor object at 0x0310D9F8>
>>> cur.execute("INSERT INTO t VALUES (-430000000)")
<pyodbc.Cursor object at 0x0310D9F8>
>>> for i in cur.execute("select * from t"):
...    print i
...
(18446744073279551616L, )

Not that this value should fit in a 32bit integer. Even a signed one.
Now to rule out sqlite3 problems:

>>> import sqlite3
>>> cnx = sqlite3.connect("C:/tmp/testdb.db")
>>> cur = cnx.cursor()
>>> cur.execute("CREATE TABLE t (value BIGINT)")
<sqlite3.Cursor object at 0x0311AB30>
>>> cur.execute("INSERT INTO t VALUES (-430000000)")
<sqlite3.Cursor object at 0x0311AB30>
>>> for i in cur.execute("select * from t"):
...    print i
...
(-430000000,)
>>>

So it appears to work okay when using the sqlite3 module.
I tried building pyodbc (both 32 bit and 64 bit) on solaris and tried it there and it appears that the 64-bit version will do the right thing.

using isql (from unixodbc) on solaris, I can also get the right output so it seems that sqliteodbc will also do the right thing.

What is the expected output? What do you see instead?
======================================================
I expect to get back the value I put into the DB.


What version of the product are you using? On what operating system?
=====================================================================
Python 2.6.2 (r262:71605, Apr 14 2009, 22:40:02) [MSC v.1500 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> pyodbc.version
'2.1.8'

Please provide any additional information below.
================================================


Jul 3, 2011
Project Member #1 mkleehammer
Assuming you are using the ODBC driver from http://www.ch-werner.de/sqliteodbc, I believe I have determined the cause of the problem.  The value is being inserted into the database correctly, but when being read the driver indicates that the value should be interpreted as unsigned.  Since a negative value sets the high-bit, the value will be very large when interpreted as unsigned.

We need a fix applied to the ODBC driver.

From the documentation, it does not appear that SQLite even supports unsigned values, so the ODBC driver should not really have any code for it.  I downloaded the source and found a 'nosign' variable that seems to be dealt with rather loosely.  I believe it is used for 3 values: signed, unsigned, and unknown, with unsigned and unknown being equivalent.  If I am correct, that is probably the root of the problem.

To test, I used a script to write the value to the database, then read it using the command line sqlite program and I got the negative value.  The command line program probably doesn't deal with signed vs. unsigned at all, but I didn't check its source.

In pyodbc's cursor.cpp InitColumnInfo where the sign is requested, I printed the results of:

        ret = SQLColAttribute(cursor->hstmt, iCol, SQL_DESC_UNSIGNED, 0, 0, 0, &f);

The value of `f` is always SQL_TRUE indicating the value should be treated as unsigned.  In that case, the large positive value would be correct.

Since the bug is actually in the driver, I'm going to close this with NoFix, but if you really need a workaround, I think a Connection level boolean for forcing integers to be treated as signed is reasonable.  Let me know if that would be helpful.

Status: NoFix
Jul 3, 2011
Project Member #2 mkleehammer
I just sent an email to the author of the driver.  If I get a response, I'll post it here.

Since I need to create a set of unit tests, I'm re-opening this with status Hold as a reminder.
Status: Hold
Jul 3, 2011
#3 mattias....@gmail.com
First of all, thanks for looking at this.

I'm not saying you are wrong in your conclusions, but I think SQLite3 should support signed types. From http://www.sqlite.org/datatype3.html - 
"INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value."
That doesn't mean everything will work as expected of course. 

I did some more tests and ended up installing a 32-bit ubuntu (64-bit seemed to work OK and I thought I'd try to debug a little...)
Anyway, when running my example on latest ubunutu 32-bit, I actually got the negative value back. 
So the score so far is:
returning large positive value: 32-bit Solaris 10 and 32-bit Windows Vista.
returning negative values: 64-bit Solaris 10, 64-bit Ubuntu 10.04 and 32-bit ubuntu 10.04.

On solaris I built python, unixodbc, sqlite and sqliteodbc myself, so I will have a look at flags to see if I have missed something there. I'll follow up here if I find something of interest.
Jul 3, 2011
Project Member #4 mkleehammer
I  agree that it supports signed. I don't think it supports unsigned. The driver is reporting the value as unsigned when it should be signed. 
Jul 3, 2011
#5 mattias....@gmail.com
Right, my bad. :) I misunderstood what you wrote. 
Jul 4, 2011
Project Member #6 mkleehammer
I've already tested 0.91 pre-release of the ODBC driver and it fixes the issue.  Thank you Christian Werner for looking at this so quickly.

I was under the impression that it was going to be released right away.

Closing with Nofix now that a solution is available.
Status: NoFix
Jul 4, 2011
#7 mattias....@gmail.com
Great, thanks for the help! :)

Powered by Google Project Hosting