Re: [pysqlite] retrieving 64-bit numbers from SQLite

Yes. That's exactly how I know that the numbers appear to be correct in the database.  Sorry for my poor wording.  If I do a select of the table from the database, here's what I get:

sqlite> select name, num from stats order by num_bytes desc limit 20;
.sgy|55094604048
.mxf|33788815542
.avi|26721386496
.bri|13425255280
.xxxx|11965614568
.rla|9267144304
.gz|9021253227
.segy|7053792792
.flate|7051593201
.exr|5465375600
.bmp|4183331610
.ppt|3951572208
.jpg-1093418973|3951572208
.pps|2529046159
.jpg-1102238551|2529046159
.mb|2482040310
.iso|2114017280
.ma|1842420876
.c|1723017165
.pptx|1473794435

So, that's why I say that the numbers appear to be correct in the database.  The database is created by a backend C program.  The python script is used to create charts for the UI.



----- Original Message ----
From: Roger Binns <[email protected]>
To: About pysqlite and APSW. <list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org>
Sent: Wednesday, September 10, 2008 6:01:35 PM
Subject: Re: [pysqlite] retrieving 64-bit numbers from SQLite

Bruce Greenblatt wrote:
> My system is a 64-bit system, 

So are mine.  It makes not one jot of difference to the behaviour of
SQLite :-)

> and the numbers appear to be correct in the database table.

How can you tell?  You could also try a query selecting the max or min
to see what type that gives you.

> It appears that somehow when I retrieve the data it is saved in a
> 32-bit signed integer,

SQLite has two apis for retrieving the value - one is as 32 bit int and
one is as 64 bit int (both signed).  pysqlite uses the latter api and
then returns a PyInt or PyLong as appropriate:

> PY_LONG_LONG intval = sqlite3_column_int64(self->statement->st, i);
> if (intval < INT32_MIN || intval > INT32_MAX) {
>     converted = PyLong_FromLongLong(intval);
> } else {
>     converted = PyInt_FromLong((long)intval);
> }

Something similar is used when sending a number from Python code into
SQLite. The value is requested from SQLite as 64 bits and full precision
is maintained in returning the value to your code as well as supplying
them to SQLite.  test/types.py checks this functionality.

> and I don't see any way to coerce the data into a 64-bit
> unsigned representation.

SQLite stores the numbers as 64 bit signed, not unsigned.

> How would you define the table to use a 64-bit unsigned data?  Would you use the Numeric type?

You want numeric for integers.  In my own code I just wanted back out
exactly what I put in, and so declare the columns to be of type 'blob'
which disables any form of conversion.

You can do conversion as part of SQL statements to see what SQLite is
doing under the hood.  This will make SQLite give you a text
representation of val.

  select cast(val as text)

This will help narrow down where your values are getting truncated.  To
diagnose further we'd need the code that inserts values as well.  You
can also do a dump from the SQLite shell to see what is actually stored
in the database.

Roger
_______________________________________________
list-pysqlite mailing list
list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org
http://itsystementwicklung.de/cgi-bin/mailman/listinfo/list-pysqlite