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),@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.

True.

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.

This is wrong. There are some values which can't be written as floats, but have precision<15.

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.

Nah...

Can you create a patch which does this?

Joost

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

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

Yes, tomorow I will do it

Laco.

Joost, look at ftBCD, ftCurrency they are also written as floats So if there is it acceptable, then I would say, that also in this case is it acceptable, isn't it ?
(I do not say, that it is perfect)

Can you create a patch which does this?

Patch is attached.

DECIMAL -> ftBCD (if precision and scale is not supplied, then scale=0) DECIMAL(p) = DECIMAL(p,0) = DECIMAL(p,<nonnumeric>) -> if p<=18 then ftLargeInt else ftFmtBCD
DECIMAL(p,s) -> if s>4 or p-s>14 then ftFmtBCD (number does not fit into currency)

-Laco.