Recent

Author Topic: IBX Filtering  (Read 1559 times)

emilt

  • New Member
  • *
  • Posts: 26
IBX Filtering
« on: June 29, 2020, 03:32:23 pm »
IBX for Lazarus  allows the use of the TDataset.Filter property for server side filtering - the value of the property gets added to the SelectSQL's WHERE clause.

This, however, interferes with client-side filtering. The server filter is applied regardless of of any OnFilterRecord handler that may be set. The handler then gets the records already filtered, and there is no way to pass a separate Filter information to do additional filtering client-side.

The TDataset.Filter help says
Quote
In general, the filter property accepts a SQL-like syntax usually encountered in the WHERE clause of an SQL SELECT statement.

However nothing prevents me (in TSQLQuery) to set the Filter value to any text that I can then use in the OnFilterRecord event to do my custom filtering (like apply different complex filtering rules based on this value).

In my view, the fact that I've defined an OnFilterRecord event means that I want to do client-filtering and the value of the Filter property is for use by the event handle and not by the database. This is easily solved with a one-line patch, but I don't know if enough people will agree with me so that it gets into the official source.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: IBX Filtering
« Reply #1 on: June 29, 2020, 04:27:19 pm »
IBX for Lazarus  allows the use of the TDataset.Filter property for server side filtering - the value of the property gets added to the SelectSQL's WHERE clause.
The TSQLQuery.Filter does NOT add anything to the WHERE clause.

You have TSQLQuery.ServerFilter to add the filter to the SQL and use server-filtering
https://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.serverfilter.html

And you have TSQLQuery.Filter which does in-memory filtering on client side.
https://www.freepascal.org/docs-html/fcl/db/tdataset.filter.html

I hope IBX for Lazarus follows those same rules.

Edit: Ieks... Yes... TIBQuery (and not TDataset) applies the .Filter directly to the SQL statement.
So it doesn't follow the TDataset documentation (so you shouldn't look at that for TIBQuery.Filter)

In my view, the fact that I've defined an OnFilterRecord event means that I want to do client-filtering and the value of the Filter property is for use by the event handle and not by the database. This is easily solved with a one-line patch, but I don't know if enough people will agree with me so that it gets into the official source.
And what if you want to use both?
« Last Edit: June 29, 2020, 04:32:53 pm by rvk »

tonyw

  • Sr. Member
  • ****
  • Posts: 319
    • MWA Software
Re: IBX Filtering
« Reply #2 on: June 30, 2020, 10:21:53 am »
I can see the logic behind having separate properties for a "Where clause" filter for server side filtering and a property to hold directions for client side filtering (to be interpreted by the OnFilterRecord handler). My only concern is that changing this now might break someone else's code.

If I don't hear any strong objections then I will consider this as a change for the next release.

emilt

  • New Member
  • *
  • Posts: 26
Re: IBX Filtering
« Reply #3 on: June 30, 2020, 12:23:07 pm »
+1 for the separate properties (the same as in TSQLQuery).

Keeping the current behavior when there is no OnFilterRecord  handler should minimize the compatibility problems.

And one thing more - applying a client side filter should not cause refreshing the dataset from the server.

EgonHugeist

  • Jr. Member
  • **
  • Posts: 78
Re: IBX Filtering
« Reply #4 on: July 06, 2020, 07:24:38 pm »
Hi, just a note from ZeosLib devs. Client side filters/sorts are supported more than 15years... It's a complex job, so i understand why IBX doesn't have it. Don't hasitate to test..
If you do, download a snapshot of https://sourceforge.net/p/zeoslib/code-0/HEAD/tree/branches/testing-7.3/
« Last Edit: July 06, 2020, 07:28:36 pm by EgonHugeist »

tonyw

  • Sr. Member
  • ****
  • Posts: 319
    • MWA Software
Re: IBX Filtering
« Reply #5 on: July 07, 2020, 10:20:30 am »
The "Filter" property of TDataSet was I believe introduced in Delphi 2 as a TDataset property (at least I can find it documented in my Delphi 2 reference guide - but not Delphi 1). It made sense when you were dealing with Paradox (or similar) tables on a local disk and a simple syntax was defined for it looking like a single level SQL conditional statement. I assume that the filter was interpreted rather than compiled.

When you are working with a true SQL Server then it is difficult to see why you would want to define a client side filter. The SQL Server is always going to be able to perform more complex and much faster filtering of datasets; you also avoid the communications overhead of having to send records from the server to the client, only for them to be discarded by the client side filter on arrival.

Not every possible filter can be defined in SQL - and that is why the OnFilterRecord event handler exists. In such cases, client side filtering is justified, and the code is written in Pascal and compiled. This is very different from interpreting a simple condition clause in a filter statement.

IBX is solely concerned with delivering an optimal driver for a Firebird (SQL) Database. It has an internal SQL parser that allows for modification of the SQL select statement every time a dataset is opened. In the BeforeOpen event handler of any IBX dataset, the the user can readily add conditions to the WHERE or HAVING clauses, or revised the ORDER BY clause to change the sort order - as well as more radical manipulations. This feature allows for efficient and advanced server side filtering in response to (e.g.) a user selecting a check box on a form, or clicking on the heading line of a DB Grid to resort the table. Indeed, this can all happen transparently when using the IBX versions of the DB Combo Box, DB Grid and Tree View.

It is perfectly conceivable for the IBX SQL parser to be used to extract a simple condition from a filter property and then interpret this. However, no user has ever requested this and I can't see why it would be worth the effort to add what is always going to be a legacy feature.

emilt

  • New Member
  • *
  • Posts: 26
Re: IBX Filtering
« Reply #6 on: July 10, 2020, 10:39:06 am »
Quote
The SQL Server is always going to be able to perform more complex and much faster filtering of datasets;
however this doesn't come without cost - a roundtrip to the server is not free.

In my case I am using the client-side filter as a "secondary" filter - I send a request to the server with where clause to return something like 500 (let's say most recent) records from several hundred thousand, but then the client needs to additionally filter that dataset on different conditions, and do that multiple times. I believe client-side filtering of 500 records will be much faster that sending another request to go through all the records and retrieving the results over the network.

So I think both filters are good and have their use cases, and keeping them separate and compatible with TSQLQuery would be a good thing.

 

TinyPortal © 2005-2018