Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-24 Thread LacaK
> User also will probably execute some > queries that do some arithmetic operations on values in that column. > And they will be really surprised to see that not all the data is > numbers there. > > My test shows, that I can successfuly execute queries like (c is NUMERIC column): select c,

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-23 Thread Jean-Christophe Deschamps
At 18:46 23/03/2011, you wrote: >Current US national debt is 16 digits. Nothing less? That's where the bug lies. OK, OK, I'm out ;-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-23 Thread Igor Tandetnik
On 3/23/2011 3:52 PM, Black, Michael (IS) wrote: > Get rid of the pennies and you can get $1.8 quintillion > > Is there a use for tracking 1/1000th's of a dollar at these amounts? That's why I said you can trade range for accuracy, and vice versa. > Newer gcc's have 128-bit ints for 64-bit

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-23 Thread Black, Michael (IS)
: EXT :Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns On Wed, Mar 23, 2011 at 1:30 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > On 3/23/2011 1:46 PM, TR Shaw wrote: >> Current US national debt is 16 digits. > > A 64-bit unsigned integer can represe

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-23 Thread Nico Williams
On Wed, Mar 23, 2011 at 1:30 PM, Igor Tandetnik wrote: > On 3/23/2011 1:46 PM, TR Shaw wrote: >> Current US national debt is 16 digits. > > A 64-bit unsigned integer can represent about $18 trillion, in > millionths of a dollar. This should have both range and accuracy to >

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-23 Thread Igor Tandetnik
On 3/23/2011 1:46 PM, TR Shaw wrote: > On Mar 23, 2011, at 1:25 PM, Igor Tandetnik wrote: >> Billing/accounting is best done in integers - say, in millionths of a >> dollar. > > Current US national debt is 16 digits. A 64-bit unsigned integer can represent about $18 trillion, in millionths of a

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-23 Thread Jonathan Allin
-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: 23 March 2011 17:25 To: sqlite-users@sqlite.org Subject: Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns On 3/23/2011 12:58 PM, Eugene N wrote: > I find the discussion very interesting. Apart from billing/accounting ev

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-23 Thread TR Shaw
On Mar 23, 2011, at 1:25 PM, Igor Tandetnik wrote: > On 3/23/2011 12:58 PM, Eugene N wrote: >> I find the discussion very interesting. Apart from billing/accounting every >> good (scientific) application soones or later is bound to requare >> multiprecision library. > > Which scientific

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-23 Thread Igor Tandetnik
On 3/23/2011 12:58 PM, Eugene N wrote: > I find the discussion very interesting. Apart from billing/accounting every > good (scientific) application soones or later is bound to requare > multiprecision library. Which scientific instrument can measure which physical quantity with an accuracy

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-23 Thread Eugene N
Hi I find the discussion very interesting. Apart from billing/accounting every good (scientific) application soones or later is bound to requare multiprecision library. The best way, as was stated above, would be to use blobs, as it would allow to dispance with all in/out converting (in case of

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-23 Thread Pavel Ivanov
> Yes, but I can not affect column type ... FreePascal SQLite3 connector must > be able to work with any user database. If your goal is to work with any user database created outside of your FreePascal connector then chances are that user will use the same database outside of your FreePascal

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-23 Thread LacaK
And...no conversion is performed if you declare the field as text and insert as text. Yes, but I can not affect column type ... FreePascal SQLite3 connector must be able to work with any user database. And when user defines column like NUMERIC or DECIMAL ... so with NUMERIC column affinity,

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-23 Thread Black, Michael (IS)
3, 2011 2:20 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns > Here are two options which will let you get the contents back to the original > precision: > A) Store the values as BLOBs. > B) Store the value as TEXT

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-23 Thread LacaK
Here are two options which will let you get the contents back to the original precision: A) Store the values as BLOBs. B) Store the value as TEXT, but add a non-digit to the beginning of each number value, for example X24395734857634756.92384729847239842398423964294298473927 Both

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-22 Thread Nico Williams
You can store any big-number representation you like as TEXT or BLOB values. The main issue is that you'll lose syntactic sugar: SQLite3 won't be able to treat those as numeric values, therefore it won't be able to compare numerically nor use arithmetic with such values. You can get some of that

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-22 Thread Simon Slavin
On 22 Mar 2011, at 1:12pm, LacaK wrote: >> You still don't say what you're planning on doing with these number...just >> displaying them? > > Yes may be ... > I am working on modification of database component for accessing SQLite3 > databases for FreePascal project. > We map declared

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-22 Thread Igor Tandetnik
On 3/22/2011 12:50 PM, Jonathan Allin wrote: > Igor, > > Is there another way of looking at the problem by considering how Java and > other libraries handle big integers and big decimals? They have data types for them, and the library to support them. > Can you store the numeric value across

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-22 Thread Jonathan Allin
...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Konrad J Hambrick Sent: 22 March 2011 15:25 To: General Discussion of SQLite Database Subject: Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns LacaK wrote, On 03/22/2011 08:53 AM: >>> / Problem will be solved

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-22 Thread Konrad J Hambrick
LacaK wrote, On 03/22/2011 08:53 AM: >>> / Problem will be solved if SQLite will store such values as text ... so Laco -- Problem will be solved when you teach SQLite to store such values as text. This library might help your project: http://speleotrove.com/decimal/ -- kjh

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-22 Thread Igor Tandetnik
On 3/22/2011 9:53 AM, LacaK wrote: > Base idea is store as TEXT when : > 1. column value is supplied as TEXT (only in case sqlite3_bind_text) > 2. conversion to REAL or INTEGER leads to loose of precision (digits) > > I do not know details how to implement it ;-) > May be, 1. strip out leading and

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-22 Thread LacaK
/ Problem will be solved if SQLite will store such values as text ... so />>/ will behave like this: />>/ 1. is supplied value in TEXT (sqlite3_bind_text) />>/ 2. if yes then try convert this text value into INTEGER or REAL />>/ 3. convert back to text and compare with original value />>/ 4. if

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-22 Thread Igor Tandetnik
LacaK wrote: >> Problem will be solved if SQLite will store such values as text ... so >> will behave like this: >> 1. is supplied value in TEXT (sqlite3_bind_text) >> 2. if yes then try convert this text value into INTEGER or REAL >> 3. convert back to text and

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-22 Thread LacaK
If you already have an arbitrary precision number, just encode it to text, save it in sqlite and then decode on the way out. Yes it is possible, but such values (and databases) will not be readable by other database connectors (like for example in PHP etc.) Problem will be solved if SQLite

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-22 Thread Pavel Ivanov
> Is there way how to store numeric values, which are out of REAL range ? SQLite has no way of storing numbers other than REAL or INTEGER. If you want the exact number to be stored your only option is to store it as TEXT (and don't work with it as a number on SQL level). Pavel On Tue, Mar 22,

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-22 Thread TR Shaw
On Mar 22, 2011, at 9:12 AM, LacaK wrote: >> You still don't say what you're planning on doing with these number...just >> displaying them? > > Yes may be ... > I am working on modification of database component for accessing SQLite3 > databases for FreePascal project. > We map declared

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-22 Thread LacaK
You still don't say what you're planning on doing with these number...just displaying them? Yes may be ... I am working on modification of database component for accessing SQLite3 databases for FreePascal project. We map declared column's types to native freepascal internal field types. So

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-22 Thread Igor Tandetnik
LacaK wrote: >> So once if I define column as DECIMAL,NUMERIC then there is no chance store >> in such column numeric values out of range of 64bit >> integers or 64bit floating point values, right ? Well, no chance to store them losslessly, preserving the precision.

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-22 Thread Black, Michael (IS)
formation Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of LacaK [la...@users.sourceforge.net] Sent: Tuesday, March 22, 2011 6:25 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [s

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-22 Thread LacaK
/ So only solution is use TEXT columns (with TEXT affinity) ? There is no />>/ way how to use DECIMAL columns (with NUMERIC affinity) ? />>/ My goal is store numeric values with big precision (as declared per />>/ column DECIMAL(30,7)). / SQLite happily ignores those numbers in parentheses.

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-22 Thread Igor Tandetnik
LacaK wrote: >> So only solution is use TEXT columns (with TEXT affinity) ? There is no >> way how to use DECIMAL columns (with NUMERIC affinity) ? >> My goal is store numeric values with big precision (as declared per >> column DECIMAL(30,7)). SQLite happily ignores

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-22 Thread LacaK
Hi Michael, thank you for response. So only solution is use TEXT columns (with TEXT affinity) ? There is no way how to use DECIMAL columns (with NUMERIC affinity) ? My goal is store numeric values with big precision (as declared per column DECIMAL(30,7)). I do not want any conversion to

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-22 Thread Black, Michael (IS)
51 AM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] storing big numbers into NUMERIC, DECIMAL columns Hi, I have table like this: CREATE TABLE tab1 ( a INTEGER, c DECIMAL(30,7), ... ); When I am trying insert values like: INSERT INTO tab1 (a,c) VALUES(1, 123456789123456789.123456); INSERT IN

[sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-22 Thread LacaK
Hi, I have table like this: CREATE TABLE tab1 ( a INTEGER, c DECIMAL(30,7), ... ); When I am trying insert values like: INSERT INTO tab1 (a,c) VALUES(1, 123456789123456789.123456); INSERT INTO tab1 (a,c) VALUES(2, '123456789123456789.123456'); values for c column are always rounded or cast

[sqlite] storing big numbers into NUMERIC, DECIMAL columns

2011-03-22 Thread LacaK
Hi, I have table like this: CREATE TABLE tab1 ( a INTEGER, c DECIMAL(30,7), ... ); When I am trying insert values like: INSERT INTO tab1 (a,c) VALUES(1, 123456789123456789.123456); INSERT INTO tab1 (a,c) VALUES(2, '123456789123456789.123456'); values for c column are always rounded or cast to :