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
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