[fpc-devel] Implementing TFmtBCDField - ftFmtBCD and SQLite

[Joost van der Sluis]

On Thu, 2011-03-24 at 09:43 +0100, LacaK wrote:
> 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

Simply said: So Sqlite doesn't support real BCD values. So we can't
support it either for Sqlite. 

More difficult: Sqlite is not a 'normal' sql-database. It has all kind
of strange issues, it's own definitions and options. It is impossible to
support all these gimmicks in sqldb. This holds also in a lesser degree
for the other databse-engines, btw. 

Practical: (the only important answer) Show me the problem, a bug, or
some code that doesn't work. I think we don't have a problem. Sqldb is
used to read data from existing tables. Sqlite only has very few
data-types (the 'affinities') but to be gentle to our users we do
support more, only based on the field-type name. So as long as users
can't make tables with numeric fields that support more then 15 numbers,
we don't have to support bcd-fields. So map decimal to BCD (not float,
it will be invalid for values within the supported precision range). If
users want to store numbers with an higher precision, that is simply
impossible in sqlite. If they want to store their numbers in
blob-fields, they can simply define a blob field and do so...

So where's the problem? (I see the problem of Sqlite not supporting
bcd-fields, but the sqldb-problem?)

Joost.