Skip to content

SQL Filters

The DataFlex record finding model relies on indexed finding to quickly locate any record. It does this by knowing how to jump into an index, find records, and jump out of the index. This process is automatic. When a DataFlex find is generated for an SQL database, it produces an SQL statement that converts the DataFlex find request into an SQL find request. Among other things, it generates an SQL statement with a “WHERE” clause that allows the server to use an indexed jump in and jump out. This kind of indexed finding is fast, efficient, and scalable.

There are times when records must be filtered using additional criteria that cannot simply be controlled with a jump in and jump out strategy. This occurs when the filter cannot be located just using an index. When this happens, a row is found on the server and sent to the DataFlex application, where the application must evaluate the row data to determine if the record is valid. If the record is valid, it is used; if the record is not valid, it instructs the SQL server to find the next record.

In Data Dictionaries, this is referred to as a non-optimized constrained find. With lower-level Find commands, the same idea applies; each record is sent from the server to the client for approval.

Using SQL filters allows you to move this filtering process from the client to the SQL server. An SQL filter is a string that is added to the SQL statement’s WHERE clause. The server SQL engine does all the work and only returns valid records to the DataFlex application. Using SQL filters has two advantages:

  • Performance Improvement: It can improve performance, sometimes dramatically. SQL servers generally do a faster job of finding and filtering records. Additionally, because possibly invalid records are not sent to the client, network traffic is reduced.

  • Complex Filtering: You can use it to filter rows in ways that are difficult, if not impossible, to do directly in your application.

If you are using the DataFlex SQL Driver, you can use SQL Filters. There are two types of filters:

  • DD SQL Filters: SQL filtering can be applied at the data dictionary level – specifically at the data dictionary object level. Different DDO structures within different views can have completely different and independent filters applied. This lets you encapsulate your DDO structures and views in a similar fashion as standard DDO constraints.

  • Global SQL Filters: SQL Filters can be applied at the table level. If a table is open and a filter is applied, any table find in your application will apply this filter. While there are times this may be desired, it is usually harder to work with, as you must deal with the side effects of controlling a global attribute. Unless the filter is truly global and should always be applied, you will have to constantly change the table’s single SQL Filter based on the context of the find (i.e., based on the view that is using it). Normally, you should only use global SQL Filters if you cannot use DD SQL Filters.

DD SQL Filters and Global SQL Filters can be used separately or together. As a rule, it is easier to always start with DD SQL Filters. The usage is the same: you create an SQL Filter string that is added to the SQL statement’s WHERE clause (which will already contain jump in and jump filters). The server will find the records and only return the rows that are valid.

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. If the jump in and jump out logic provides most of the filtering you need, adding an additional SQL Filter will not improve performance. If your find is not sending back rows that require additional validation on the client, the server is already performing the optimization. In addition, SQL filters add 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 ensure that the find you are asking for is efficient and scalable.

DD SQL Filters

If you are using the DataFlex SQL Drivers, DD interfaces may be used to set SQL filters at the DDO level. Because these filters are applied at the DD object level, you can set table filters on a view-by-view basis. This makes DD SQL Filters flexible and powerful – you use them the same way you would use DDO constraints.

When enabled, all DD record finds and reads may apply a custom DD SQL filter. At the simplest level, you will enable SQL filtering by setting [pbUseDDSQLFilters](../VdfClassRef/WebAndWindows/DataDictionary-Property-pbUseDDSQLFilters.md) to True and define an SQL filter by setting [psSQLFilter](../VdfClassRef/WebAndWindows/DataDictionary-Property-psSQLFilter.md). You may also use [pbApplyGlobalSQLFilters](../VdfClassRef/WebAndWindows/DataDictionary-Property-pbApplyGlobalSQLFilters.md) to determine if psSQLFilter should be used in place of whatever global SQL Filter exists or if the two filters should be combined.

Normally, you would apply filters by setting psSQLFilter in [OnConstrain](../VdfClassRef/WebAndWindows/DataSet-Event-OnConstrain.md).

Object SalesP_DD is a Salesp_DataDictionary
    Set pbUseDDSQLFilters to True

    Procedure OnConstrain
        Set psSQLFilter to "SalesP.Name LIKE '%J%'"
    End_Procedure
End_Object

SQL filters can be used alongside standard DD constraints (i.e., they can be used along with the Constrain commands in your OnConstrain event). When used with DD 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 DD constraints.

Set pbUseDDSQLFilters to True

Procedure OnConstrain
    Constrain Customer.State eq "CA"
    Set psSQLFilter to "Customer.Name LIKE '%J%'" // This will be called first
End_Procedure

The SQL Filter string, psSQLFilter, is essentially a string that contains a valid SQL “WHERE” clause. You must ensure that your string is valid, secure, and reasonably efficient. The DataDictionary class also has several SQL String helper methods that make this easier to do. For example, the [SQLStrLike](../VdfClassRef/WebAndWindows/DataDictionary-Function-SQLStrLike.md) function returns a “LIKE” string that can be used by the WHERE clause. It could be used as follows:

Set pbUseDDSQLFilters to True

Procedure OnConstrain
    String sFilter
    Integer iFile
    Get Main_File to iFile
    Get SQLStrLike iFile "J" to sFilter
    Set psSQLFilter to sFilter
End_Procedure

Not only does this allow you to not worry about the SQL syntax, but the string is also escaped, making it more secure. Other SQL String helper functions include [SQLStrAppend](../VdfClassRef/WebAndWindows/DataDictionary-Function-SQLStrAppend.md), [SQLStrFileFieldName](../VdfClassRef/WebAndWindows/DataDictionary-Function-SQLStrFileFieldName.md), and [SQLEscapedStr](../VdfClassRef/WebAndWindows/DataDictionary-Function-SQLEscapedStr.md).

Global SQL Filters

If you wish to use SQL filters outside of Data Dictionary finds, you can use global SQL Filters. This is a lower-level filter used when you want to apply a filter for Find commands.

Global SQL Filters are applied using attributes ([Set_Attribute](../LanguageReference/Set_Attribute_Command.md), [Get_Attribute](../LanguageReference/Get_attribute_Command.md)). SQL filtering is enabled and disabled using the [DF_FILE_SQL_FILTER_ACTIVE](../LanguageReference/DF_FILE_SQL_FILTER_ACTIVE.md) attribute. The filter (the “WHERE” clause) is assigned using the [DF_FILE_SQL_FILTER](../LanguageReference/DF_FILE_SQL_FILTER.md) attribute. The [DF_FILE_SQL_FILTER_EQ](../LanguageReference/DF_FILE_SQL_FILTER_EQ.md) attribute determines if the filter is also applied to Find EQ operations.

For example, assume you want to filter the records processed by a function - we want to show all order detail records with a quantity ordered greater than or equal to 4. This could be done using standard DataFlex code as follows:

Procedure ShowDetails
    Open OrderDtl
    Clear OrderDtl
    Repeat
        Find Gt OrderDtl By Index.1
        If (Found And OrderDtl.Qty_Ordered >= 4) ;
            Showln OrderDtl.Order_number ", " ;
            OrderDtl.Detail_number ", " ;
            OrderDtl.Qty_ordered
    Until (Not(Found))
    Close OrderDtl
End_Procedure

The loop above will work but may be a bit slow. The loop will process all records in the Order Detail table. Using filters, you can limit the number of records that need to be processed. If we rewrite the code above to use SQL filters, we would get something like:

Procedure ShowDetailsFilter
    Handle hTable
    String sFilter
    Open OrderDtl
    Move OrderDtl.File_number To hTable
    Set_Attribute DF_FILE_SQL_FILTER Of hTable ;
        To "OrderDtl.Qty_ordered >= 4"
    Set_Attribute DF_FILE_SQL_FILTER_ACTIVE Of hTable To True
    Clear OrderDtl
    Repeat
        Find Gt OrderDtl By Index.1
        If (Found) ;
            Showln OrderDtl.Order_number ", " ;
            OrderDtl.Detail_number ", " ;
            OrderDtl.Qty_ordered
    Until (Not(Found))
    Set_Attribute DF_FILE_SQL_FILTER_ACTIVE Of hTable To False
End_Procedure

Note that this is just a sample. This could also be written using data dictionary objects and DD SQL filtering.

The global SQL filter settings are set at the table level. If the table is open, the SQL global filter is active, and the find command is used, the filter is applied. When finds are processed using DD Find methods, you have the choice of applying or ignoring the global SQL Filter.

Whenever possible, we recommend the use of DD SQL Filters over global SQL Filters. Global SQL filters should be used carefully. If the filter is truly global to an application (i.e., all table finds must use this filter), a global SQL Filter may be a good choice. When applied locally, as in the above example, one must switch on filtering, process records, and switch off filtering again. With interface view-based applications, this can be difficult. When using global SQL filters in data dictionary constraints, be aware that navigating between application views may require the SQL filter to be reset.

Special Note

Note that by default, global SQL filters are not applied to all find operations. The exceptions are Find EQ and FindByRowId. This exception exists to avoid breaking relationships. The [DF_FILE_SQL_FILTER_EQ](../LanguageReference/DF_FILE_SQL_FILTER_EQ.md) attribute controls this behavior. So if you want your user to never see data that doesn’t match your filter (for example, when building multi-tenant applications), then you need to ensure that DF_FILE_SQL_FILTER_EQ is set to true for each table.