[fpc-devel] SQLite and ftFmtBCD
Joost van der Sluis
joost at cnoc.nl
Tue May 10 13:18:45 CEST 2011
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.
> 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));
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
> or better:
> ftFmtBCD: begin
> if P.AsFMTBCD.Precision > 15 then //we are out of REAL range, so we must bind as BLOB
> str1:=BCDTOStr(P.AsFMTBCD, SQLFormatSettings);
> checkerror(sqlite3_bind_blob(fstatement,I,pcharstr(str1), length(str1), @freebindstring));
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
> > " 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?
More information about the fpc-devel