f2s_table_constraint¶

Applies to: Oracle, MS SQL, MySql/MariaDB, PostgreSQL Drivers

Syntax

set_attribute f2s_table_constraint of {File} to {variable}
get_attribute f2s_table_constraint of {File} to {variable}

Description

This attribute allows you to put additional constraints on your finds, moving filtering to the SQL backend. Using this command can result in significantly improved performace, since the server network traffic is reduced. Note that the SQL statement passed for this command is added to the predefined Database SQL statement, so treat it as such. Because of this, it will almost always begin with “AND” followed by the desired constraint.

Setting this attribute will add the additional constraint. Getting this attribute returns the current SQL constraint for a given table. If there is no current constraint, then a blank string is returned. This attribute works in conjunction with f2s_table_constraint_state.

Example

use for_all.pkg //needed to use th For_All/End_For_All block

String sCon
//set the constraint to only show customers that do NOT have a status of Y
set_attribute f2s_table_constraint of Customer to (SFormat("AND [%1].[%2] <> '%3'", "Customer", "Status", "Y"))
get_attribute f2s_table_constratint of Customer to sCon

Showln "Show only inactive customers"
Showln sCon
For_All Customer by Index.1 Do
  Showln (Customer.Name+" "+Customer.Status)
End_For_All

Showln "********"
Showln "Show All Customers"
//clear the constraint and show all customers
set_attribute f2s_table_constraint of Customer to ""
For_All Customer by Index.1 Do
   Showln (Customer.Name+" "+Customer.Status)
End_For_All

In this example, a constraint is added to the predefined Customer SQL statement to only show those customers that are not active (Customer.Status<>’Y’). As a result, when looping through the Customer records (For_All/End_For_All) only the inactive customers are listed. Subsequently, the constraint is removed from the predefined Customer SQL statement. Now when looping through the customer records, all the customers are listed.

Related Attributes

• f2s_table_constraint_state

Replaces the commands SET_SQL_CONSTRAINT AND GET_SQL_CONSTRAINT