BIGINT problem on 32-bit architeture

[GoogleCodeExporter]

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.
================================================

Original issue reported on code.google.com by mattias....@gmail.com on 1 Jul 2011 at 7:57

[GoogleCodeExporter]

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.

Original comment by mkleehammer on 3 Jul 2011 at 7:22

• Changed state: NoFix

[GoogleCodeExporter]

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.

Original comment by mkleehammer on 3 Jul 2011 at 7:29

• Changed state: Hold

[GoogleCodeExporter]

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.

Original comment by mattias....@gmail.com on 3 Jul 2011 at 7:48

[GoogleCodeExporter]

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. 

Original comment by mkleehammer on 3 Jul 2011 at 8:02

[GoogleCodeExporter]

Right, my bad. :) I misunderstood what you wrote. 

Original comment by mattias....@gmail.com on 3 Jul 2011 at 8:03

[GoogleCodeExporter]

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.

Original comment by mkleehammer on 4 Jul 2011 at 3:11

• Changed state: NoFix

[GoogleCodeExporter]

Great, thanks for the help! :)

Original comment by mattias....@gmail.com on 4 Jul 2011 at 6:56