Recent

Author Topic: TSqlQuery Filter does'nt work with a ftBCD field  (Read 3006 times)

Jurassic Pork

  • Hero Member
  • *****
  • Posts: 1228
TSqlQuery Filter does'nt work with a ftBCD field
« on: November 25, 2015, 02:09:38 am »
hello,
i have a sqlite3 database with this structure :
Code: MySQL  [Select][+][-]
  1. -- Table: users
  2. CREATE 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][+][-]
  1. procedure TForm1.bt_filterClick(Sender: TObject);
  2. begin
  3. SQLQuery1.Filtered := false;
  4. SQLQuery1.FilterOptions:= [];
  5. SQLQuery1.Filter:= 'EVALUATION <= 2300';
  6. SQLQuery1.Filtered := true;
  7. end;

i have this error :
Quote
Field "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 ?  :-X

One solution for this problem is to use the OnFilterRecord event of TSqlquery component like this :
Code: Pascal  [Select][+][-]
  1. procedure TForm1.SQLQuery1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
  2. begin
  3.   Accept := SQLQuery1.FieldByName('EVALUATION').AsFloat <= 2100;
  4. end;

the button to filter :
Code: Pascal  [Select][+][-]
  1. procedure TForm1.bt_filterClick(Sender: TObject);
  2. begin
  3. SQLQuery1.Filtered := false;
  4. SQLQuery1.FilterOptions:= [];
  5. SQLQuery1.Filtered := true;
  6. end;
  7.  
Project in attachment

Friendly, J.P
« Last Edit: November 25, 2015, 02:16:49 am by Jurassic Pork »
Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: TSqlQuery Filter does'nt work with a ftBCD field
« Reply #1 on: November 25, 2015, 08:33:53 am »
i have this error :
Quote
Field "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 ?  :-X

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

  • Hero Member
  • *****
  • Posts: 691
Re: TSqlQuery Filter does'nt work with a ftBCD field
« Reply #2 on: November 30, 2015, 10:35:44 am »
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

  • Hero Member
  • *****
  • Posts: 1228
Re: TSqlQuery Filter does'nt work with a ftBCD field
« Reply #3 on: November 30, 2015, 05:52:44 pm »
hello,

LacaK, thanks , your patch works for me  :)
Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

 

TinyPortal © 2005-2018