Closed GoogleCodeExporter closed 9 years ago
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
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
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
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
Right, my bad. :) I misunderstood what you wrote.
Original comment by mattias....@gmail.com
on 3 Jul 2011 at 8:03
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
Great, thanks for the help! :)
Original comment by mattias....@gmail.com
on 4 Jul 2011 at 6:56
Original issue reported on code.google.com by
mattias....@gmail.com
on 1 Jul 2011 at 7:57