Lazarus

Free Pascal => Database => Topic started by: dmitryb on July 27, 2021, 02:38:19 am

Title: TBufDataset.Filter := 'FieldName Is Null' does not work.
Post by: dmitryb 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.

Title: Re: TBufDataset.Filter := 'FieldName Is Null' does not work.
Post by: lucamar 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.

Title: Re: TBufDataset.Filter := 'FieldName Is Null' does not work.
Post by: dmitryb 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.

Title: Re: TBufDataset.Filter := 'FieldName Is Null' does not work.
Post by: balazsszekely on July 27, 2021, 06:37:06 am

Quote from: dmitryb 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.

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.  

Title: Re: TBufDataset.Filter := 'FieldName Is Null' does not work.
Post by: wp on July 27, 2021, 11:40:14 am

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

Title: Re: TBufDataset.Filter := 'FieldName Is Null' does not work.
Post by: LacaK 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

Title: Re: TBufDataset.Filter := 'FieldName Is Null' does not work.
Post by: wp 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.

Title: Re: TBufDataset.Filter := 'FieldName Is Null' does not work.
Post by: LacaK 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).