Property Boolean pbUseDDSQLFilters
| Read Access: | Get pbUseDDSQLFilters to BooleanVariable |
| Write Access: | Set pbUseDDSQLFilters to BooleanVariable/Value |
Set pbUseDDSQLFilters to True if you wish DataDictionary (DD) SQL filters to be used. When True, the filter applied is determined by psSQLFilter and pbApplyGlobalSQLFilters.
The default value is False.
You should only set this to True if the driver is an SQL driver that supports SQL filters. For example, the Data Access MS SQL driver supports this while the embedded database driver does not. If you attempt to use this with the embedded driver, you will get an error when you attempt to find a record.
Data Dictionary SQL Filters make it possible to set SQL filters at the Data Dictionary Object (DDO) level. When enabled, all DD record finds and reads may apply a custom DD SQL filter before the find/read and then restore the table's SQL filter state back to its original state. At the simplest level, you will enable SQL filtering by setting pbUseDDSQLFilters to True and define an SQL filter by setting psSQLFilter. You may also use pbApplyGlobalSQLFilters to determine if psSQLFilter should be used in place of whatever table-level active SQL filter exists, or if the two filters should be combined.
DD SQL filters are applied to most, but not all, DD find and read methods. They are applied to Find, Request_Find, Request_Read, and Locate_Next).
They are not applied to record identity finds Find_By_Recnum, Read_By_Recnum, FindByRowId, ReadByRowId and they are not applied to parent table relates associated with a find. Rereads which are applied as part of DD saves and deletes do not apply SQL filtering.
DD Filters will be used when autofill constrained finds require a child find (e.g., you find an order record and it finds the first related child).
DD SQL filters are not inherited. In other words, an SQL filter defined in a parent is never applied to the SQL filter of a child. Inherited parent SQL filters don't really make sense, because the SQL server can only apply the filter to the table it is querying.
Normally, you would apply filters by setting psSQLFilter in OnConstrain, although in more static cases you could just set the filter in the object declaration. These two examples would work the same:
Object SalesP_DD1 is a Salesp_DataDictionary
Set pbUseDDSQLFilters to True
Set psSQLFilter to ("SalesP.Name LIKE '%J%'")
End_Object
Object SalesP_DD is a Salesp_DataDictionary
Set pbUseDDSQLFilters to True
Procedure OnConstrain
Set psSQLFilter to ("SalesP.Name LIKE '%J%'")
End_Procedure
End_Object
The second method is a little more flexible, because it can be easily altered to let you dynamically change your DD constraints:
Property Boolean pbApply False
Object SalesP_DD is a Salesp_DataDictionary
Set pbUseDDSQLFilters to True
Procedure OnConstrain
Boolean bApply
Get pbApply to bApply
If (bApply) Begin
Set psSQLFilter to ("SalesP.Name LIKE '%J%'")
End
Else Begin
Set psSQLFilter to ""
End
End_Procedure
End_Object
SQL filters can be used alongside DataFlex constraints. When used with DataFlex constraints, the SQL filter will perform the filtered find on the server and, possibly, return a record. That record will then be tested by the DataFlex constraints.
Set pbUseDDSQLFilters to True
Procedure OnConstrain
Constrain Customer.State eq "CA"
Set psSQLFilter to ("Customer.Name LIKE '%J%'")
End_Procedure
One of the main purposes of using SQL filters is to speed up record finding by offloading filters to the server. Doing this can dramatically increase performance. However, that may not always be the case. In DataFlex, SQL queries are carefully constructed so the SQL find will use an SQL index and be able to jump in and jump out of that index. Adding filters adds additional clauses to the WHERE statement. When this happens, the SQL engine might decide that it has a faster way of finding a record than using the index you provided. Most of the time, the SQL engine will be correct, that's the way SQL works and you will want this. In some cases, your record finding may slow down. As you start providing SQL WHERE clause information via a filter, it becomes your responsibility to make sure that find you are asking for is efficient.