[fpc-devel] SQLite and ftFmtBCD

[LacaK]

> 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
>>             begin
>>               s   tr1=BCDTOStrP.AsFMTBCD,SQLFormatSettings);
>>               checkerror(sqlite3_bind_blob(fstatement,I,pcharstr(str1), length(str1), @freebindstring));
>>             end
>>             else
>>             begin
>>                d o1=P.AsFloat
>>               checkerror(sqlite3_bind_double(fstatement,I,do1));
>>             end;
>>           end;
>>     
>
> 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:

ftFmtBCD: begin
            str1:=BCDTOStr(P.AsFMTBCD,SQLFormatSettings);
            //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));
          end;



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

Laco.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freepascal.org/pipermail/fpc-devel/attachments/20110510/b048b92e/attachment.html>