[fpc-devel] SQLite and ftFmtBCD
LacaK
lacak at zoznam.sk
Tue May 10 12:39:25 CEST 2011
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));
end;
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;
about SQLFormatSettings see: http://bugs.freepascal.org/view.php?id=17188
----------------------
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
> " 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
-Laco.
More information about the fpc-devel
mailing list