[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));

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));

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


More information about the fpc-devel mailing list