Cannot insert/retrieve 8 byte integers without losing digits

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Cannot insert/retrieve 8 byte integers without losing digits

Richard D Watkins
I have some very large integers I need to insert and retrieve from SQLite
without losing any digits. These numbers can be up to 8 bytes (20 digits)
long.

The SQLite documentation says INTEGER datatypes can hold values up to 8
bytes long, however, when I insert any number greater than 17 digits, it
gets converted into a REAL, and truncated to 17 significant digits.

I have found only one way to insert/fetch these values without losing
digits:  define the column affinity to be TEXT, convert the value to a
string in Python, then insert it into the SQLite database. The fetched
value will be TEXT and contain all digits.


However, if I do any arithmetic on it as the query executes, the value gets
converted to REAL, truncated, then the truncated value is used in the
arithmetic.

I have a SQLite database version 3.7.8.  I'm developing in Python version
2.4.4 and using PySqlite version 2.6.3.

I'm aware of Adapters and Converters. They would have to be made such that
the returned value is converted back to a TEXT value before SQLite returns
the value to the program. This might work, but I would prefer to let SQLite
do the manipulation in the usual manner.

Is there any way to insert integers of any size, then retrieve them with a
query that might do math or other manipulations on the values, without
losing digits?

Thanks!!

Regards,
Richard Watkins
Product Development & Systems Engineering
phone: 720-684-2193
email: [hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Cannot insert/retrieve 8 byte integers without losing digits

Richard Hipp-3
On Tue, Jan 31, 2012 at 4:32 PM, Richard D Watkins <
[hidden email]> wrote:

> I have some very large integers I need to insert and retrieve from SQLite
> without losing any digits. These numbers can be up to 8 bytes (20 digits)
> long.
>

SQLite integers are 8-byte twos-complement with a range of
-9223372036854775808 to 9223372036854775807 (19 digits).  Anything outside
this range is converted to double, and is thus subject to the 17-digit
approximation.  We do test the boundary cases carefully, so we know
everything works over the full range specified above.

Do you think your python interface layer might be doing the unwanted
conversion to floating point for you?


>
> The SQLite documentation says INTEGER datatypes can hold values up to 8
> bytes long, however, when I insert any number greater than 17 digits, it
> gets converted into a REAL, and truncated to 17 significant digits.
>
> I have found only one way to insert/fetch these values without losing
> digits:  define the column affinity to be TEXT, convert the value to a
> string in Python, then insert it into the SQLite database. The fetched
> value will be TEXT and contain all digits.
>
>
> However, if I do any arithmetic on it as the query executes, the value gets
> converted to REAL, truncated, then the truncated value is used in the
> arithmetic.
>
> I have a SQLite database version 3.7.8.  I'm developing in Python version
> 2.4.4 and using PySqlite version 2.6.3.
>
> I'm aware of Adapters and Converters. They would have to be made such that
> the returned value is converted back to a TEXT value before SQLite returns
> the value to the program. This might work, but I would prefer to let SQLite
> do the manipulation in the usual manner.
>
> Is there any way to insert integers of any size, then retrieve them with a
> query that might do math or other manipulations on the values, without
> losing digits?
>
> Thanks!!
>
> Regards,
> Richard Watkins
> Product Development & Systems Engineering
> phone: 720-684-2193
> email: [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Cannot insert/retrieve 8 byte integers without losing digits

yesnid
Hello Richard,

I am having a similar issue, I am trying to store a 13 digit value in my table and it is being converted to a 10 digit value? Is there something that I need to do, to enable the 8 byte values for integer?

Thank you,


Richard Hipp-3 wrote
On Tue, Jan 31, 2012 at 4:32 PM, Richard D Watkins <
richard.d.watkins@seagate.com> wrote:

> I have some very large integers I need to insert and retrieve from SQLite
> without losing any digits. These numbers can be up to 8 bytes (20 digits)
> long.
>

SQLite integers are 8-byte twos-complement with a range of
-9223372036854775808 to 9223372036854775807 (19 digits).  Anything outside
this range is converted to double, and is thus subject to the 17-digit
approximation.  We do test the boundary cases carefully, so we know
everything works over the full range specified above.

Do you think your python interface layer might be doing the unwanted
conversion to floating point for you?


>
> The SQLite documentation says INTEGER datatypes can hold values up to 8
> bytes long, however, when I insert any number greater than 17 digits, it
> gets converted into a REAL, and truncated to 17 significant digits.
>
> I have found only one way to insert/fetch these values without losing
> digits:  define the column affinity to be TEXT, convert the value to a
> string in Python, then insert it into the SQLite database. The fetched
> value will be TEXT and contain all digits.
>
>
> However, if I do any arithmetic on it as the query executes, the value gets
> converted to REAL, truncated, then the truncated value is used in the
> arithmetic.
>
> I have a SQLite database version 3.7.8.  I'm developing in Python version
> 2.4.4 and using PySqlite version 2.6.3.
>
> I'm aware of Adapters and Converters. They would have to be made such that
> the returned value is converted back to a TEXT value before SQLite returns
> the value to the program. This might work, but I would prefer to let SQLite
> do the manipulation in the usual manner.
>
> Is there any way to insert integers of any size, then retrieve them with a
> query that might do math or other manipulations on the values, without
> losing digits?
>
> Thanks!!
>
> Regards,
> Richard Watkins
> Product Development & Systems Engineering
> phone: 720-684-2193
> email: richard.watkins@seagate.com
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
drh@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Cannot insert/retrieve 8 byte integers without losing digits

Simon Slavin-3

On 9 Feb 2012, at 4:58pm, yesnid wrote:

> I am having a similar issue, I am trying to store a 13 digit value in my
> table and it is being converted to a 10 digit value? Is there something that
> I need to do, to enable the 8 byte values for integer?

What type have you defined that column in the table as ?  Can you supply the CREATE command or the relevant part of it ?

How are you supplying the values to be stored ?  Are you using _exec() on a long string, or binding them as an integer, a float, or what ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Cannot insert/retrieve 8 byte integers without losing digits

Richard Hipp-3
In reply to this post by yesnid
On Thu, Feb 9, 2012 at 11:58 AM, yesnid <[hidden email]> wrote:

>
> Hello Richard,
>
> I am having a similar issue, I am trying to store a 13 digit value in my
> table and it is being converted to a 10 digit value? Is there something
> that
> I need to do, to enable the 8 byte values for integer?
>

You don't have to do anything.  It just works.  Behold:

SQLite version 3.7.11 2012-01-31 23:34:21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t1(x);
sqlite> insert into t1 values(9223372036854775807);
sqlite> select x, x-1 from t1;
9223372036854775807|9223372036854775806

Do follow Simon's advice, though, and make sure you are not trying to store
the integers in a TEXT column.



>
> Thank you,
>
>
>
> Richard Hipp-3 wrote:
> >
> > On Tue, Jan 31, 2012 at 4:32 PM, Richard D Watkins <
> > [hidden email]> wrote:
> >
> >> I have some very large integers I need to insert and retrieve from
> SQLite
> >> without losing any digits. These numbers can be up to 8 bytes (20
> digits)
> >> long.
> >>
> >
> > SQLite integers are 8-byte twos-complement with a range of
> > -9223372036854775808 to 9223372036854775807 (19 digits).  Anything
> outside
> > this range is converted to double, and is thus subject to the 17-digit
> > approximation.  We do test the boundary cases carefully, so we know
> > everything works over the full range specified above.
> >
> > Do you think your python interface layer might be doing the unwanted
> > conversion to floating point for you?
> >
> >
> >>
> >> The SQLite documentation says INTEGER datatypes can hold values up to 8
> >> bytes long, however, when I insert any number greater than 17 digits, it
> >> gets converted into a REAL, and truncated to 17 significant digits.
> >>
> >> I have found only one way to insert/fetch these values without losing
> >> digits:  define the column affinity to be TEXT, convert the value to a
> >> string in Python, then insert it into the SQLite database. The fetched
> >> value will be TEXT and contain all digits.
> >>
> >>
> >> However, if I do any arithmetic on it as the query executes, the value
> >> gets
> >> converted to REAL, truncated, then the truncated value is used in the
> >> arithmetic.
> >>
> >> I have a SQLite database version 3.7.8.  I'm developing in Python
> version
> >> 2.4.4 and using PySqlite version 2.6.3.
> >>
> >> I'm aware of Adapters and Converters. They would have to be made such
> >> that
> >> the returned value is converted back to a TEXT value before SQLite
> >> returns
> >> the value to the program. This might work, but I would prefer to let
> >> SQLite
> >> do the manipulation in the usual manner.
> >>
> >> Is there any way to insert integers of any size, then retrieve them with
> >> a
> >> query that might do math or other manipulations on the values, without
> >> losing digits?
> >>
> >> Thanks!!
> >>
> >> Regards,
> >> Richard Watkins
> >> Product Development & Systems Engineering
> >> phone: 720-684-2193
> >> email: [hidden email]
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> > --
> > D. Richard Hipp
> > [hidden email]
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
>
> --
> View this message in context:
> http://old.nabble.com/Cannot-insert-retrieve-8-byte-integers-without-losing-digits-tp33243026p33294747.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Cannot insert/retrieve 8 byte integers without losing digits

yesnid
In reply to this post by Simon Slavin-3
Hello Simon,

Here is my create:

CREATE TABLE IF NOT EXISTS Exports (id                  varchar(50) PRIMARY KEY NOT NULL,start_time          integer,end_time            integer,data_source_id      varchar(50),format              integer,percent_complete    integer,size                integer,comment             varchar(50),user_id             varchar(50),state               integer,friendly_name       varchar(50),download_count      integer,mark_for_delete     integer,udn                 varchar(50));

and here is my insert:

INSERT INTO Exports VALUES('d006dacf-3134-45b6-828b-0860738e4029',1311178875028,1311178935028,'dvd-1',2001,0,0,'/*NoComment*/','matt',1337,'d006dacf-3134-45b6-828b-0860738e4029',0,0,'406b8555-5ae5-496d-844c-2f839e19eb75');

what winds up in the database for start and end time is:

1213849748

which is what the number 1311178875028 becomes if you cast it to an int, I assume that I am just missing a simple thing here but can really use your help.

Thank you,


Simon Slavin-3 wrote
On 9 Feb 2012, at 4:58pm, yesnid wrote:

> I am having a similar issue, I am trying to store a 13 digit value in my
> table and it is being converted to a 10 digit value? Is there something that
> I need to do, to enable the 8 byte values for integer?

What type have you defined that column in the table as ?  Can you supply the CREATE command or the relevant part of it ?

How are you supplying the values to be stored ?  Are you using _exec() on a long string, or binding them as an integer, a float, or what ?

Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Cannot insert/retrieve 8 byte integers without losing digits

Black, Michael (IS)
Is this from inside your application?



Works fine from the shell which makes me think you're truncating your value in your program.



SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE IF NOT EXISTS Exports (id                  varchar(50) PRIMARY
   ...> KEY NOT NULL,start_time          integer,end_time
   ...> integer,data_source_id      varchar(50),format
   ...> integer,percent_complete    integer,size                integer,comment
   ...> varchar(50),user_id             varchar(50),state
   ...> integer,friendly_name       varchar(50),download_count
   ...> integer,mark_for_delete     integer,udn                 varchar(50));
sqlite>
sqlite> INSERT INTO Exports
   ...> VALUES('d006dacf-3134-45b6-828b-0860738e4029',1311178875028,1311178935028,'dvd-1',2001,0,0,'/*NoComment*/','matt',1337,'d006dacf-3134-45b6-828b-0860738e
4029',0,0,'406b8555-5ae5-496d-844c-2f839e19eb75');
sqlite>
sqlite> select * from Exports;
d006dacf-3134-45b6-828b-0860738e4029|1311178875028|1311178935028|dvd-1|2001|0|0|/*NoComment*/|matt|1337|d006dacf-3134-45b6-828b-0860738e4029|0|0|406b8555-5ae5-4
96d-844c-2f839e19eb75
sqlite>



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems

________________________________
From: [hidden email] [[hidden email]] on behalf of yesnid [[hidden email]]
Sent: Thursday, February 09, 2012 11:42 AM
To: [hidden email]
Subject: EXT :Re: [sqlite] Cannot insert/retrieve 8 byte integers without losing digits


Hello Simon,

Here is my create:

CREATE TABLE IF NOT EXISTS Exports (id                  varchar(50) PRIMARY
KEY NOT NULL,start_time          integer,end_time
integer,data_source_id      varchar(50),format
integer,percent_complete    integer,size                integer,comment
varchar(50),user_id             varchar(50),state
integer,friendly_name       varchar(50),download_count
integer,mark_for_delete     integer,udn                 varchar(50));

and here is my insert:

INSERT INTO Exports
VALUES('d006dacf-3134-45b6-828b-0860738e4029',1311178875028,1311178935028,'dvd-1',2001,0,0,'/*NoComment*/','matt',1337,'d006dacf-3134-45b6-828b-0860738e4029',0,0,'406b8555-5ae5-496d-844c-2f839e19eb75');

what winds up in the database for start and end time is:

1213849748

which is what the number 1311178875028 becomes if you cast it to an int, I
assume that I am just missing a simple thing here but can really use your
help.

Thank you,



Simon Slavin-3 wrote:

>
>
> On 9 Feb 2012, at 4:58pm, yesnid wrote:
>
>> I am having a similar issue, I am trying to store a 13 digit value in my
>> table and it is being converted to a 10 digit value? Is there something
>> that
>> I need to do, to enable the 8 byte values for integer?
>
> What type have you defined that column in the table as ?  Can you supply
> the CREATE command or the relevant part of it ?
>
> How are you supplying the values to be stored ?  Are you using _exec() on
> a long string, or binding them as an integer, a float, or what ?
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>

--
View this message in context: http://old.nabble.com/Cannot-insert-retrieve-8-byte-integers-without-losing-digits-tp33243026p33295099.html
Sent from the SQLite mailing list archive at Nabble.com.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Cannot insert/retrieve 8 byte integers without losing digits

Noah Hart
In reply to this post by yesnid
May be in your program;  From the 3.7.10 shell

sqlite> select id, start_time from exports;
d006dacf-3134-45b6-828b-0860738e4029|1311178875028
sqlite> select id, cast(start_time as integer) from exports;
d006dacf-3134-45b6-828b-0860738e4029|1311178875028
sqlite>

What version are you running?

Regards, Noah

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of yesnid
Sent: Thursday, February 09, 2012 9:43 AM
To: [hidden email]
Subject: Re: [sqlite] Cannot insert/retrieve 8 byte integers without
losing digits


Hello Simon,

Here is my create:

CREATE TABLE IF NOT EXISTS Exports (id                  varchar(50)
PRIMARY
KEY NOT NULL,start_time          integer,end_time          
integer,data_source_id      varchar(50),format            
integer,percent_complete    integer,size                integer,comment

varchar(50),user_id             varchar(50),state              
integer,friendly_name       varchar(50),download_count    
integer,mark_for_delete     integer,udn                 varchar(50));

and here is my insert:

INSERT INTO Exports
VALUES('d006dacf-3134-45b6-828b-0860738e4029',1311178875028,131117893502
8,'dvd-1',2001,0,0,'/*NoComment*/','matt',1337,'d006dacf-3134-45b6-828b-
0860738e4029',0,0,'406b8555-5ae5-496d-844c-2f839e19eb75');

what winds up in the database for start and end time is:

1213849748

which is what the number 1311178875028 becomes if you cast it to an int,
I assume that I am just missing a simple thing here but can really use
your help.

Thank you,



Simon Slavin-3 wrote:
>
>
> On 9 Feb 2012, at 4:58pm, yesnid wrote:
>
>> I am having a similar issue, I am trying to store a 13 digit value in

>> my table and it is being converted to a 10 digit value? Is there
>> something that I need to do, to enable the 8 byte values for integer?
>
> What type have you defined that column in the table as ?  Can you
> supply the CREATE command or the relevant part of it ?
>
> How are you supplying the values to be stored ?  Are you using _exec()

> on a long string, or binding them as an integer, a float, or what ?
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>

--
View this message in context:
http://old.nabble.com/Cannot-insert-retrieve-8-byte-integers-without-los
ing-digits-tp33243026p33295099.html
Sent from the SQLite mailing list archive at Nabble.com.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



CONFIDENTIALITY NOTICE:
This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Cannot insert/retrieve 8 byte integers without losing digits

Simon Slavin-3
In reply to this post by yesnid

On 9 Feb 2012, at 5:42pm, yesnid wrote:

> CREATE TABLE IF NOT EXISTS Exports (id                  varchar(50) PRIMARY
> KEY NOT NULL,start_time          integer,end_time          
> integer,data_source_id      varchar(50),format            
> integer,percent_complete    integer,size                integer,comment            
> varchar(50),user_id             varchar(50),state              
> integer,friendly_name       varchar(50),download_count    
> integer,mark_for_delete     integer,udn                 varchar(50));
>
> and here is my insert:
>
> INSERT INTO Exports
> VALUES('d006dacf-3134-45b6-828b-0860738e4029',1311178875028,1311178935028,'dvd-1',2001,0,0,'/*NoComment*/','matt',1337,'d006dacf-3134-45b6-828b-0860738e4029',0,0,'406b8555-5ae5-496d-844c-2f839e19eb75');
>
> what winds up in the database for start and end time is:
>
> 1213849748
>
> which is what the number 1311178875028 becomes if you cast it to an int

That is very good diagnostic information which helps us a lot in figuring out your problem.

Your handling of integers is fine.  Try the same commands using the SQLite3 command-line tool and you'll find that your commands work perfectly:

SQLite version 3.7.7 2011-06-25 16:35:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE IF NOT EXISTS Exports (id                  varchar(50) PRIMARY
   ...> KEY NOT NULL,start_time          integer,end_time          
   ...> integer,data_source_id      varchar(50),format            
   ...> integer,percent_complete    integer,size                integer,comment            
   ...> varchar(50),user_id             varchar(50),state              
   ...> integer,friendly_name       varchar(50),download_count    
   ...> integer,mark_for_delete     integer,udn                 varchar(50));
sqlite> INSERT INTO Exports
   ...> VALUES('d006dacf-3134-45b6-828b-0860738e4029',1311178875028,1311178935028,'dvd-1',2001,0,0,'/*NoComment*/','matt',1337,'d006dacf-3134-45b6-828b-0860738e4029',0,0,'406b8555-5ae5-496d-844c-2f839e19eb75');
sqlite> SELECT start_time FROM Exports;
1311178875028

So the fault is in your application.  My guess is that your are retrieving the result of the SELECT into smaller integer fields than you're realised: that the truncation is happening after the correct number is retrieved from the database.

Incidentally, the columns you've designated as 'varchar(50)' are being handled as TEXT because SQLite doesn't have a varchar type.  So do not rely on SQLite enforcing any 50-character limit.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users