Hi,
after doing some test with new implementation of TFmtBCDField for
TSQLite3Connection connector I encounter this problem:
When you declare in SQLite some column as NUMERIC or DECIMAL then this
column will have NUMERIC affinity.
CREATE TABLE t (d DECIMAL(30,7));
If you insert in such column any numeric value (quoted or unquoted):
INSERT INTO t VALUES('123456789123456789.123456');
SQLite converts such numeric value to 8-byte floating point
representation (REAL) or 8-byte integer (INTEGER), so:
(lost of precision occurs)
SELECT * FROM t;
returns 123456789123456784
See: http://www.sqlite.org/datatype3.html#affinity
"A column with NUMERIC affinity may contain values using all five
storage classes. When text data is inserted into a NUMERIC column, the
storage class of the text is converted to INTEGER or REAL (in order of
preference) if such conversion is lossless and reversible. For
conversions between TEXT and REAL storage classes, SQLite considers the
conversion to be lossless and reversible if the first 15 significant
decimal digits of the number are preserved. If the lossless conversion
of TEXT to INTEGER or REAL is not possible then the value is stored
using the TEXT storage class. No attempt is made to convert NULL or BLOB
values."
My original understanding was, that when I store big numbers as TEXT and
lossless conversion to REAL can not be done, then value will be stored
as TEXT, but in meaning in SQLite lossless is: " if the first 15
significant decimal digits of the number are preserved" (not all digits
only first 15!!!)
I have asked about this in sqlite-users mailing list (topic "storing big
numbers into NUMERIC, DECIMAL columns") and results are:
(I attach some of comments from sqlite-users mailing list)
A.) if we want preserve precision of BCD values we must store them as
BLOBs (using sqlite3_bind_blob()) ... see (1) and (2) in attachment
(with optimalization like : if BCDPrecision(...) > 15 then use
sqlite3_bind_blob(...) else sqlite3_bind_double(...) )
Here my test shows, that if I store value like BLOB I still can do
select like:
SELECT d+100 FROM t;
SELECT sum(d) FROM t;
SELECT * FROM t WHERE d>12345678912345678;
(it seems, that SQLite converts BLOB->TEXT->REAL ...of course precision
is lost)
B.) does not support numbers (big exact numbers), which are not
supported by SQLite (SQLite supports only REAL and INTEGER). ... see (3)
So DECIMAL, NUMERIC map to ftFloat if "decimal places">4 (out of range
ftBCD) and map to ftLargeInt if "decimal places"=0
What do you think, which way to go ?
-Laco.
(1)
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 methods will prevent SQLite from trying to see the value as a number. Oh
and since nobody seems to have pointed it out yet, SQLite doesn't have a
NUMERIC or a DECIMAL column type. The types can be found here:
http://www.sqlite.org/datatype3.html
Putting INTEGER and REAL together gives you NUMERIC, but there's no way to
declare a column of that type, just a value. The page actually rehearses your
problem, showing when strings containing numeric values can be converted to a
number.
--
(2)
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.
--
(3)
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 connector too. And if use declared
column as NUMERIC then he probably will expect numbers to be inserted
into that column (especially if the data type inside FreePascal is
also some sort of number). 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.
So you better insert everything as numbers, let SQLite transform
everything to REAL and lose precision. And tell your users that it's
limitation of SQLite and you cannot do anything with it, only suggest
to insert big numbers as text.
_______________________________________________
fpc-devel maillist - fpc-devel@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-devel
