[fpc-devel] SQLite and ftFmtBCD

[Joost van der Sluis]

On Tue, 2011-05-10 at 12:39 +0200, LacaK wrote:
> Hi Joost (and others also ;-),
> 
> I comment your question about 
> http://bugs.freepascal.org/view.php?id=18809 here.
> (because I do not know what comment here and what in bug tracker)
> 
> I see problem in fact, that AsString returns number formated using 
> locale specific DecimalSeparator.
> So if FmtBCD param will hold value "123456789,123456789" then this value 
> will be written into SQLite3 database.
> 
> Which is IMO not very good, becuase SQL standard and also SQLite 
> expects, that numeric values will always have point as decimal separator.

True.

> So my suggestion is in case of ftFMTBCD param use something like:
> 
> ftFmtBCD: begin
>             str1:= BCDToStr(P.AsFMTBCD, SQLFormatSettings); //this depend on bug 18807
>             checkerror(sqlite3_bind_text(fstatement,I,pcharstr(str1), length(str1), at freebindstring));
>           end;

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.

> or better:
> ftFmtBCD: begin
>             if P.AsFMTBCD.Precision > 15 then //we are out of REAL range, so we must bind as BLOB
>             begin
>               str1:=BCDTOStr(P.AsFMTBCD, SQLFormatSettings);
>               checkerror(sqlite3_bind_blob(fstatement,I,pcharstr(str1), length(str1), @freebindstring));
>             end
>             else
>             begin
>                do1=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. 

> ----------------------
> Other questions:
> > " Isn't it better to use '4' instead of 255/0 as default value for 
> the scale?"
> SQL standard says, that if scale is ommited then scale is 0 ... imho it 
> is better to follow standard

But this particular piece of code is used when an invalid value is
supplied, like 'DECIMAL(10,A)'. The case that the value is omitted is
handled elsewhere.

> > " 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?

Joost