TBufDataset.Filter := 'FieldName Is Null' does not work.
lazarus / 63-Database

----------------------------------------------------------------------
dmitryb · 2021-07-27 00:38

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 · 2021-07-27 01:11

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.

----------------------------------------------------------------------
dmitryb · 2021-07-27 01:22

I tested  

BufDataset1.Filter := 'ART_PAK = 10';

This works correctly.

But how to filter out empty values is not clear.

----------------------------------------------------------------------
balazsszekely · 2021-07-27 04:37

Quote from: dmitryb on July 27, 2021, 03:22:17 amI 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.BufDataset1FilterRecord(DataSet: TDataSet; var Accept:
Boolean);begin  Accept := not
Dataset.FieldByName('ART_PAK').IsNull;end;

----------------------------------------------------------------------
wp · 2021-07-27 09:40

Quote from: dmitryb on July 27, 2021, 02:38:19 am'FieldName Is Null'
or 'FieldName = Null' does not work.
According to Delphi docs (http://docwiki.embarcadero.com/Libraries/Syd
ney/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.

----------------------------------------------------------------------
LacaK · 2021-07-27 12:18

Yes it is there in Delphi documentation specified under
For IBX.IBTable.TIBTableIt is not clear, but from context I assume
that construct IS NULL is for IBX components only.

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

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

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

----------------------------------------------------------------------
wp · 2021-07-27 14:19

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.

----------------------------------------------------------------------
LacaK · 2021-08-05 11:11

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