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

[Simon Slavin]

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_completeinteger,sizeinteger,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_completeinteger,sizeinteger,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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Noah Hart]

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: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of yesnid
Sent: Thursday, February 09, 2012 9:43 AM
To: sqlite-users@sqlite.org
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_completeinteger,sizeinteger,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
> sqlite-users@sqlite.org
> 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
sqlite-users@sqlite.org
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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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_completeinteger,sizeinteger,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: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of yesnid [ns...@pelco.com]
Sent: Thursday, February 09, 2012 11:42 AM
To: sqlite-users@sqlite.org
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_completeinteger,sizeinteger,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
>
>

--
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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[yesnid]

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_completeinteger,sizeinteger,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
> 
> 

-- 
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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

On Thu, Feb 9, 2012 at 11:58 AM, yesnid  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 <
> > richard.d.watk...@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.watk...@seagate.com
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > 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
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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

Re: [sqlite] 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.watk...@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.watk...@seagate.com
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

On Tue, Jan 31, 2012 at 4:32 PM, Richard D Watkins <
richard.d.watk...@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.watk...@seagate.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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: richard.watk...@seagate.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users