Recent

Author Topic: TBufDataset.Filter := 'FieldName Is Null' does not work.  (Read 5870 times)

dmitryb

  • Jr. Member
  • **
  • Posts: 50
TBufDataset.Filter := 'FieldName Is Null' does not work.
« on: July 27, 2021, 02:38:19 am »
Hi All

How to set Filter property in TBufDataset to show records where 'FieldName Is Null' 'FieldName Is Not Null'?
'FieldName Is Null' or 'FieldName = Null' does not work.

lucamar

  • Hero Member
  • *****
  • Posts: 4219
Re: TBufDataset.Filter := 'FieldName Is Null' does not work.
« Reply #1 on: July 27, 2021, 03:11:54 am »
I'm no expert so excuse me if it's a daft question but ... is that valid SQL? And if it is and should work, did you remember to also set Filtered to True?

And finally, are you sure TBufDataset implements Filter? IIRC it doesn't implement any DB laguage so it seems probable it can't filter either.
Turbo Pascal 3 CP/M - Amstrad PCW 8256 (512 KB !!!) :P
Lazarus/FPC 2.0.8/3.0.4 & 2.0.12/3.2.0 - 32/64 bits on:
(K|L|X)Ubuntu 12..18, Windows XP, 7, 10 and various DOSes.

dmitryb

  • Jr. Member
  • **
  • Posts: 50
Re: TBufDataset.Filter := 'FieldName Is Null' does not work.
« Reply #2 on: July 27, 2021, 03:22:17 am »
I tested   

BufDataset1.Filter := 'ART_PAK = 10';

This works correctly.

But how to filter out empty values is not clear.

GetMem

  • Hero Member
  • *****
  • Posts: 3456
Re: TBufDataset.Filter := 'FieldName Is Null' does not work.
« Reply #3 on: July 27, 2021, 06:37:06 am »
I tested   

BufDataset1.Filter := 'ART_PAK = 10';

This works correctly.

But how to filter out empty values is not clear.

Set Filtered property to true, then use the OnFilterRecord event:
Code: Pascal  [Select][+][-]
  1. procedure TForm1.BufDataset1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
  2. begin
  3.   Accept := not Dataset.FieldByName('ART_PAK').IsNull;
  4. end;
  5.  

wp

  • Hero Member
  • *****
  • Posts: 8749
Re: TBufDataset.Filter := 'FieldName Is Null' does not work.
« Reply #4 on: July 27, 2021, 11:40:14 am »
'FieldName Is Null' or 'FieldName = Null' does not work.
According to Delphi docs (http://docwiki.embarcadero.com/Libraries/Sydney/en/Data.DB.TDataSet.Filter) this should work. Therefore, we very probably have a bug here. Please file a bug report. Note that the old bugtracker has been switched off, and issues must be filed on the new gitlab page (link at sidebar, "BugTracker"). Maybe wait for a few days until everything is finished with the move to git.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

LacaK

  • Hero Member
  • *****
  • Posts: 637
Re: TBufDataset.Filter := 'FieldName Is Null' does not work.
« Reply #5 on: July 27, 2021, 02:18:30 pm »
Yes it is there in Delphi documentation specified under
  • For IBX.IBTable.TIBTable
It is not clear, but from context I assume that construct IS NULL is for IBX components only.

Later is
  • For Data.DB.TDataSet
and examples for TADODataset shows:
 State <> 'CA' or State = NULL

Another documentation shows http://docwiki.embarcadero.com/RADStudio/Sydney/en/Setting_the_Filter_Property
 State <> CA or State = BLANK

So we have 3 various construct:
  • is null
  • =null
  • =blank

wp

  • Hero Member
  • *****
  • Posts: 8749
Re: TBufDataset.Filter := 'FieldName Is Null' does not work.
« Reply #6 on: July 27, 2021, 04:19:56 pm »
I played with Delphi's ClientDataset which corresponds to our TBufDataset - see attached demo project: Open the .dproj file with Delphi (I am using 10.3.3 Community Edition), and the empty records are filtered with the syntax "<fieldname> IS NULL" or "NOT <fieldname> IS NULL". The attachment contains also a Lazarus version of the same project (Open the .lpi file in Lazarus), and here the programs crashes when these filters are activated.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

LacaK

  • Hero Member
  • *****
  • Posts: 637
Re: TBufDataset.Filter := 'FieldName Is Null' does not work.
« Reply #7 on: August 05, 2021, 01:11:04 pm »
I investigated how Filter works in TBufDataset .
Local filtering relies on TDbf code and there is no such things as NULL handling.
Simply: involved object such as TConstant, TVariable are not NULL aware.
They expects that field has value which is assigned to their native type (integer, string, double).

 

TinyPortal © 2005-2018