Re: [sqlite] Cannot insert/retrieve 8 byte integers without losing digits
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
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
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
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
On Thu, Feb 9, 2012 at 11:58 AM, yesnidwrote: > > 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
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
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
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
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