Changes include:
Property pbUseDDSQLFilters
Property pbApplyGlobalSQLFilters
Property psSQLFilter
Function SupportsSQLFilters
Function SQLStrLike
Function SQLStrAppend
Function SQLStrFileFieldName
Procedure Data_Set_PreFind
Procedure Data_Set_PostFind
Procedure StoreDefaultSQLFilters
Procedure RestoreDefaultSQLFilters
Procedure ApplySQLFilter
New low level and high level DD messages have been added that makes it possible to set SQL filters at the DDO level. When enabled, all DD record finds and reads may apply a custom DD SQL filter before the find/read and then restore to 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 is 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. 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 DF constraints. When used with DF 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 DF 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
One of the main purposes of using SQL filters it 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.
Set pbUseDDSQLFilters to True
Set pbUseDDSQLFilters to true if you wish SQL DD 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 MSSSQL driver does support 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.
Set pbApplyGlobalSQLFilters to True
Set pbApplyGlobalSQLFilters to true if you wish your DD filter (psSQLFilter) to be appended (anded) to the value in the global table filter. When pbUseDDSQLFilters is True and pbApplyGlobalSQLFilters is False, only the value in psSQLFilter is applied to the find. When pbUseDDSQLFilters is True and pbApplyGlobalSQLFilters is True the DD filter is added to the global filter assuming that there is a global filter and that this filter is active (see df_file_sql_Filter and df_file_sql_filter_active.
The default value is False.
Set psSQLFilter to " Customer.State = 'CA' "
The psSQLFilter defines the SQL filter string to be applied and works in much the same way the df_file_sql_filter attribute works. This is only applied if pbUseDDSQLFilters is True.
If pbUseDDSQLFilters is True, pbApplyGlobalSQLFilters is false, and psSQLFilter is empty then explicitly no SQL filter is applied. If a table level filter exists and is active, it will be not used in the find.
You can use some of the SQL string helper functions such as SQLStrFileFieldName, SQLStrAppend and to help assemble your SQL filter string.
The default value is "".
Get SupportsSQLFilters to bSupported
This can be called to determine if this table is capable of supporting SQL filtering and the SQL DD filter interface. This is determined by the driver. If the driver does not support SQL filtering, you should leave pbUseDDSQLFilters set to false and not use SQL filtering.
Note that pbUseDDSQLFilters is not tied to SupportsSQLFilters and that pbUseDDSQLFilters is not automatically set true when SupportsSQLFilters is true. If you start using SQL filters your record finding strategies may change significantly and you may not want to use the same strategies with a non-SQL database such as the embedded database. This change should be a deliberate developer decision.
Get SQLStrLike field Customer.Name "ab" to sFilter
Get SQLStrLike iColumn "ab" to sFilter
This is a helper function that will create a string that creates an SQL like filter for a field and a target value. For example, the above sample would return the value "Customer.Name LIKE '%ab%'".
Get SQLStrAppend sFilter1 sFilter2 to sNewFilter
This is a helper function that will take two SQL filter strings and combine them (i.e. combine them using proper SQL AND syntax). If either filter string is empty, the function will handle this properly and just return the non-blank value. This means you don't need to test if a string is empty before calling this.
Get SQLStrFileFieldName file_field Customer.Name to sSQLName
Get SQLStrFileFieldName iMyFile iMyField to sSQLName
This is a helper function that will create a string that that represents the SQL table and column in a format that can be used by an SQL where clause. It bases the name of the SQL table name, which may be different than the DataFlex logical name.
Get SQLStrFileFieldName file_field Customer.State to sSQLName
Move (sSQLName + "='CA' ") to sFilter
Data_Set_PreFind and Data_Set_PostFind are sent to a DDO before and after finds and reads. We use this to provide a before and after hook where DD SQL filters can be applied and removed. You can also use these methods but you should do so carefully. If you do not forward send these, the entire DD SQL filter process may break. If you use these functions you are advised to carefully read and understand the code for these methods in the DataDictionary class.
Data_Set_PreFind and Data_Set_PostFind are called as part DD find and read methods. They are called within 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 the find. They are called right before and after the internal runtime record find.
Here is a general description of what these methods do and how they are used to implement DD SQL filters. If you want to know exactly what they do, you should read the code.
Data_Set_PreFind
If pbUseDDSQLFilters
// store current table SQL filter information
Send StoreDefaultSQLFilters
// create new table SQL filter information based on
// pbApplyGlobalSQLFilters and psSQLFilter
Send ApplySQLFilter
End
Data_Set_PostFind
If pbUseDDSQLFilters
// restore table SQL filter information
Send RestoreDefaultSQLFilters
End
You should rarely need to use any of these methods described here. DD SQL filtering was designed to be controlled by pbUseDDSQLFilters, pbApplyGlobalSQLFilters and psSQLFilter.
See Data_Set_PreFind
This stores any information about the table's current SQL filters. For the DAW SQL drivers this gets the attribute values of DF_FILE_SQL_FILTER, DF_FILE_SQL_FILTER_ACTIVE and DF_FILE_SQL_FILTER_EQ and stores them in the private properties psOldFilter, pbOldFilterActive and pbOldFilterEQ. This is called by Data_Set_PreFind.
It is expected that these attributes will be restored by RestoreDefaultSQLFilters, which is called by Data_Set_PostFind.
This is never called if pbUseDDSQLFilters is False.
See Data_Set_PreFind for more information about this.
This restores the global table SQL filters back to their original pre-find state. See StoreDefaultSQLFilters for more.
This is never called if pbUseDDSQLFilters is False.
This is called before a DD find/read and is used to set DD SQL Filters. Refer to Data_Set_PreFind, which calls this for more information about this process.
ApplySQLFilter sets the table SQL filter to the value in psSQLFilter. If pbApplyGlobalSQLFilters is false, this filter in psSQLFilter will replace the current global table SQL filter; if true psSQLFilter is added to any the active table SQL filter.
If psSQLFilter if empty, that empty filter is applied. If pbApplyGlobalSQLFilters is false, this means that there is no SQL filter used for this find. If pbApplyGlobalSQLFilters is true, it means that nothing changes.
After the find, the filter information is restored by RestoreDefaultSQLFilters.
This is never called if pbUseDDSQLFilters is False.
DDOs can provide information about their DF constraints. These are the constraints that are built up in the OnConstrain event using the Constrain command. You can query information about all of the constraints for a DDO and you can query information about how the constraint engine uses these for these for a find by a particular index. Much of this information is technical and would only be used in advanced programming situations. It can also be used when debugging constraints. The dd_debug package has been enhanced to show you this information.
Function DDOConstraints Returns tConstraintDef[]
Function DDOConstraintFindMeta Integer eMode Integer iOrder Returns tDDOConstraintFindMeta
Function DDOConstraintNeedsRelate Integer iOrder Returns Boolean
Constraints are built when Rebuild_Constraints is sent to a DDO. The DDO calls OnConstrain which executes all of the Constrain commands. Each constraint is added to an array of constraints. If the DDO inherits constraints from its parent DDOs (determined by pbInheritConstraints) it will send OnConstrain to the parents and continue to add these parent constraints to the original DDO's array of constraints. Upon completion this array of constraints, which consists of this DDs constraints and possibly parent DD constraints is what is used to filter all finds for this DDO.
tConstraintDef[] Constraints
Get DDOConstraints of hoDD to Constraints
You can now query this array of constraints by sending the Get DDOConstraints to the DDO after the constraints have been rebuilt. This returns an array of tConstraintDef types. A tConstraintDef type contains all of the information that can be provided for a single constraint. It is defined as follows:
// Struct used to DDOConstraints to return current constraints in an array
Struct tConstraintDef
Integer eType // constraint Find type
Integer eMode // constrain file mode
Integer iFile
Integer iField
String sValue
Integer iOtherFile
Integer iOtherField
End_Struct
eType determines the type of constraint and can be one of the following values:
Constrain Customer.Name GT "J"
The DDO filters the value of iFile and iField based on the comparison mode (eMode) and the value in sValue. This is defined with something like the "Constrain File.field GT SomeValue". See eMode for information about the comparison modes. The value in sValue is evaluated when the Constrain command is executed (which occurs when Rebuild_Constraints calls OnConstrain). Therefore this value can be a constant, a variable or an expression. Its value is determined when the constraint is built and not when the record is found.
Set Constrain_File to Customer.File_Number
The DDO relates to a parent DDO. This is usually defined with the "Set Constrain_File" interface. If can also be defined using the "Constrain File relates to ParentFile" constrain command. When this type is defined, the child file and relating from field is stored in iFile and iField and the related to parent file and field is stored in iOtherFile and iOtherField. Note that if no relationship exist found between the two files, this constraint will not even get added to the array of constraints.
Constrain Customer.ID EQ Region.ID
This is similar to CONST_VALUE except the value being compared will be another file.field value. This is usually defined with something like "Constrain MyFile.MyField GT SomeOtherFile.SomeField". The members iFile and iField define the value on the left and iOtherFile and iOtherField define the value on the right. Unlike CONST_VALUE the value on the right is not known until the record is found.
When the record is found, the runtime will determine if the record for the right-side first requires a relate. This will determine if the comparison is made before or after a relate.
Constrain Customer as (LowerCase(Left(Customer.name,1)="a"))
This is used with Constrain As constraints. Typically this is defined with something like "Constrain File as (Expression)". The runtime can provide no additional useful information about this expression so none of the other struct members convey any useful information.
This is a legacy runtime value that should never appear. It is the same as CONST_CHILD.
eType is the find comparison mode and is used with CONST_VALUE and CONST_FIELD. Values are:
CONST_LT ( 0) - Constrain Customer.sName LT sValue
CONST_LE (1) - Constrain Customer.sName LE sValue
CONST_EQ (2) - Constrain Customer.sName EQ sValue
CONST_GE (3) - Constrain Customer.sName GE sValue
CONST_GT (4) - Constrain Customer.sName GT sValue
CONST_NE (5) - Constrain Customer.sName NE sValue
CONST_Matches ( 6) - Constrain Customer.sName matches "?A*"
CONST_Contains (7) - Constrain Customer.sName contains sValue
CONST_MatchesJumpIn (9) // Constrain Customer.sName matches "A*"
The different between Matches and MatchesJumpIn is determined by the right side value and if it is structured in such a way that a jump can be performed. For example "Dat*" can be used for jump in while "?Dat*" cannot.
There is no actual between constraint operator. When you use Between two constraints are added the array, one for GE and one for LE.
Each DDO has its own array of constraints. When constraints are inherited the DDO that has inherited the constraints will contain an array of constraints that includes the inherited constraints.
Use of DDOConstraints is an advanced and rarely needed technique. If you want to see how this can be used study the code and the results in dd_debug.dg
tDDOConstraintFindMeta FindMeta
Get DDOConstraintFindMeta of hoDD (GE) iIndex to FindMeta
Before a constrained find occurs for a specified index and find mode, the runtime determines what kind of optimizations can be applied for these constraints for this index. It does this by looking at every constraint in the array and determining if the resulting constrained find can be used for Jump-in (by seeding some of the segments), and which constraints can are Jump-out (if the constraint fail, you are done), which constraints can be evaluated before the relate and which constraints are evaluated after the relate. DDOConstraintFindMeta returns this information for a particular index as a tDDOConstraintFindMeta struct type, which is defined as follows:
Struct tDDOConstraintFindMeta
Integer iJumpInSegments
Boolean bJumpOut
Boolean bPreRelate
Boolean bPostRelate
End_Struct
iJumpSegments - this contains the number of segments that are filled in when find is initialized. In general the more index segments that can be filled in, the more efficient the initial jump-in find.
bJumpOut - This determines if there are any constraints in the array that upon a failed evaluation means there are no more valid records to be found. The find is complete. Jump-out constraints are evaluated first and if they fail, the is over.
bPreRelate - this is true if any of the constraints in the array can be evaluated after the record find but before the relate. If a pre-relate constraint fails, you will not need to perform an unneeded relate for that one find. PreRelate evaluations occur after the JumpIn evaluation. If they fail, you must still keep searching for a valid record.
bPostRelate - this is true if any of the constraints in the array must be evaluated after the relate. Post relate require that a relate must occur before you can be sure that a record is valid. The overhead of relates may be significant if you need to find and test a large number of records before a valid record is found.
When a find occurs the constraints the JumpOut constraints are tested first. If they fail, the find is over. Next prerelate constraints are tested. If they fail, no relate occurs but you will keep finding records. Next a relate will occur and the constraints that must be tested post-relate will be tested. If this fails, the finding will continue.
One thing to note about post-relate constraints is that inherited constraints almost always require post-relate tests. If you do not need inherited constraints shut them off (Set pbInheritConstraints to false). Constrain-as constraints always require post-relate tests. The runtime has no idea what is in the expression and therefore requires a constraint before the test.
This is very technical information and is probably going to be most useful when debugging and profiling. The dd_debug.dg dialog now provides all of this information to you. If you want to see how this can be used study the code and the results in dd_debug.dg. Remember that the efficiency of a constraint is determined by the index used. You can use dd_debug.dg to see which indexes support jump-in and jump-out and which indexes require post-relate evaluations.
Get DDOConstraintNeedsRelate of hoServer iIndex to bNeedsRelate
When you are reading a batch of records there will be times when you don't really need to perform relates. In such a case you can use set the No_Relate_State property to True to disable constraints. Before you do that you want to make sure that the constraints you are using do not require relates to determine if a record is valid. If a relate is required, a relate must occur. While you could do this with by getting ConstraintFindMeta and looking at the bPostRelate member, you can test this directly by getting DDConstraintsNeedsRelate. You pass it an index and it will tell you if the current constraints require a relate.
For example, if you wanted to find the total of all orders based on current constraints you could do the following:
Move oOrderHea_DD to hoServer
Move 1 to iIndex
Get No_Relate_State of hoServer to bOldRelate
Get DDOConstraintNeedsRelate of hoServer iIndex to bNeedsRelate
Set No_Relate_State of hoServer to (not(bNeedsRelate))
Send Request_Read of hoServer FIRST_RECORD Orderhea.File_Number iIndex
Move (Found) to bOk
While (bOK)
Move (Orderhea.Total+nTotal) to nTotal
Send Request_Read of hoServer GT Orderhea.File_Number iIndex
Move (Found) to bOk
Loop
Set No_Relate_State of hoServer to bOldRelate
This type of technique is most useful when building classes that need to read records for an set of constraints.
What's New in DataFlex 2014 - 18.0