[fpc-devel] SQLite and ftFmtBCD
lacak at zoznam.sk
Tue May 10 14:08:54 CEST 2011
> I'll look at this solution. I'm a little bit confused about
> SQLFormatSettings and why it was implemented as it is now. I'll have to
> investigate this.
IMO for locale independent formatting numeric, date, time values
according to sql standard
>> or better:
>> ftFmtBCD: begin
>> if P.AsFMTBCD.Precision > 15 then //we are out of REAL range, so we must bind as BLOB
>> s tr1=BCDTOStrP.AsFMTBCD,SQLFormatSettings);
>> checkerror(sqlite3_bind_blob(fstatement,I,pcharstr(str1), length(str1), @freebindstring));
>> d o1=P.AsFloat
> This is wrong. There are some values which can't be written as floats,
> but have precision<15.
Yes, then my mystake then we can/must use:
//bind as BLOB, because when bind as TEXT, SQLite always will convert to REAL or INTEGER,
//which can lead to lost of precision
checkerror(sqlite3_bind_blob(fstatement,I,pcharstr(str1), length(str1), @freebindstring));
> But this particular piece of code is used when an invalid value is
> supplied, like 'DECIMAL(10,A)'.
yes, in this case we can default to 4 (but A as scale is in all cases
something very strange)
> The case that the value is omitted is
> handled elsewhere.
ok, in this case we should use 0
>>> " Isn't it better to map to a ftInteger/ftLargeInt field when size1=0?"
>> Yes IMHO it is better, at least because ftLargeInt (int64) can hold more
>> significant digits (left to decimal point) than ftBCD (currency)
>> But what if user defines NUMERIC(30,0) then we have two options:
>> 1. say "sorry" SQLite does not support this, so we map to ftLargeInt
>> 2. or map to ftFmtBCD if (scale>4) or (precision>18)
>> if precision>18 then ftFmtBCD
>> else if scale=0 then ftLargeInt
>> else if scale<=4 then ftBCD
>> else ftFmtBCD
> Can you create a patch which does this?
Yes, tomorow I will do it
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the fpc-devel