TSqlQuery Filter does'nt work with a ftBCD field
lazarus / 16-Databases

----------------------------------------------------------------------
Jurassic Pork · 2015-11-25 01:09

hello,
i have a sqlite3 database with this structure :
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i -- Table:
usersCREATE TABLE users (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME
VARCHAR (20), EVALUATION DECIMAL (10, 2));
When i use a Filter on my TSqlQuery  :
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.bt_filterClick(Sender: TObject);beginSQLQuery1.Filtered :=
false;SQLQuery1.FilterOptions:= [];SQLQuery1.Filter:= 'EVALUATION <=
2300';SQLQuery1.Filtered := true;end;
i have this error :
QuoteField "EVALUATION" has an invalid field type (BCD) to index on'
EVALUATION is a ftBCD type.
the exception is in file fpc\2.6.4\source\packages\fcl-
db\src\base\bufdataset_parser.pp because ftBCD isn't
in the case of the TBufDatasetParser.HandleUnknownVariable(VarName:
string) procedure.

Is it possible to add ftBCD in the case of this procedure or is it too
dangerous ? 

One solution for this problem is to use the OnFilterRecord event of
TSqlquery component like this :
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.SQLQuery1FilterRecord(DataSet: TDataSet; var Accept:
Boolean);begin  Accept := SQLQuery1.FieldByName('EVALUATION').AsFloat
<= 2100;end;
the button to filter :
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.bt_filterClick(Sender: TObject);beginSQLQuery1.Filtered :=
false;SQLQuery1.FilterOptions:= [];SQLQuery1.Filtered :=
true;end; Project in attachment

Friendly, J.P

----------------------------------------------------------------------
LacaK · 2015-11-25 07:33

Quote from: Jurassic Pork on November 25, 2015, 02:09:38 ami have this
error :
QuoteField "EVALUATION" has an invalid field type (BCD) to index on'
EVALUATION is a ftBCD type.
the exception is in file fpc\2.6.4\source\packages\fcl-
db\src\base\bufdataset_parser.pp because ftBCD isn't
in the case of the TBufDatasetParser.HandleUnknownVariable(VarName:
string) procedure.

Is it possible to add ftBCD in the case of this procedure or is it too
dangerous ? 

Hi,
it is not so simple IMHO.
As  you see in bufdataset_parser.pp there are T...IntFieldVar objects.
So at least we must create new TBCDFieldVar.
But as far as bufdataset_parser depends on dbf_prscore and dbf_prsdef
where is defined TExpressionType = (etInteger, etString, etBoolean,
etLargeInt, etFloat, etDateTime, ...) ... it would ge good also add
here etBCD, plus in other places handle it.
As a hack we can introduce TBCDFieldVar, which will internaly convert
BCD values to Float values and will report own expression type as
etFloat ... of course it can lead to rounding errors in same cases ...

----------------------------------------------------------------------
LacaK · 2015-11-30 09:35

I have created simple patch (attached). It converts currency (bcd
field) to double (float field).
Which can leads to loss of precision, but should work in most cases.
Can you test it ?

----------------------------------------------------------------------
Jurassic Pork · 2015-11-30 16:52

hello,

LacaK, thanks , your patch works for me