MSSQL_SetConstraint

Obsolete

The MSSQL_SetConstraint command was introduced in version 1 of the SQL Server Driver. Since then, new techniques allow the programmer a better and more controllable interface. The command is still available for backward compatibility reasons. For new logic, we recommend the use of the DF_FILE_SQL_FILTER and DF_FILE_SQL_FILTER_ACTIVE attributes or the use of Embedded SQL.

This command was added to give the programmer more control over the SELECT statement generated by the driver. It effectively turns off the driver logic that generates the WHERE clause of a SELECT statement and replaces it with what is passed in the command. Since you are overwriting internal logic, this command has the potential to generate unexpected results when misused. Therefore, the command must be used in one defined way: you must set up the clause, find records in the result set by the index that you want the result ordered by in forward or backward direction (GT, LT), and remove the clause.

For example, if we want to use this functionality in the Order Entry sample of DataFlex to find all order lines for the MODEMS item, we could do this in the following way:

Use MSSQLDRV
String Clause
Move "ITEM_ID = 'MODEMS'" To Clause
MSSQL_SetConstraint OrderDtl.File_number Clause
Repeat
    Find Gt OrderDtl By Index.1
    [Found] Showln "Order detail for a modem is: " OrderDtl.Order_Number ", " OrderDtl.Detail_number
Until [Not Found]
MSSQL_SetConstraint OrderDtl.File_number ""

This feature can speed up reporting in particular. Sometimes looking for related information is more efficient if the WHERE clause is replaced by a ChildColumn = ParentValue construction. The SELECT statements generated by the driver will select the desired record and all that follow according to the index used. By giving it a specific WHERE clause, having specific knowledge about the database, you are able to limit the size of the result sets and thereby increase performance.

The MSSQL_SetConstraint command will reset the DF_FILE_MAX_ROWS_FETCHED attribute when issued. If the command is issued with a clause, the attribute will be set to zero (0). If it is called with no clause (an empty string), the attribute will be reset to its original value.

Please note that you need to use SQL syntax in the WHERE clause that you specify. If you want to select on dates, you must use the YYYY-MM-DD format that SQL uses. Normally, this is easiest accomplished by:

Date MyDate
Integer OrgFmt OrgSep
Sysdate MyDate
Get_Attribute DF_DATE_FORMAT     To OrgFmt
Get_Attribute DF_DATE_SEPARATOR To OrgSep
Set_attribute DF_DATE_FORMAT     To DF_DATE_MILITARY
Set_attribute DF_DATE_SEPARATOR To (ASCII("-"))
MSSQL_SetConstraint MyFile.File_number ("MYDATECOLUMN = '" + string(MyDate) + "'")
Set_attribute DF_DATE_FORMAT     To OrgFmt
Set_attribute DF_DATE_SEPARATOR To OrgSep

Also, note that string constants are placed in single quotes (').

OF SPECIAL NOTE

• If this command is used on a table, you cannot use other finds on that table until ending the MSSQL_SetConstraint. As long as the constraint is in force, it will overwrite the find logic of the driver. If, before ending MSSQL_SetConstraint, we changed the example above to find on OrderDtl by some other index or in a different direction (not GT) on the same index, the result would be unpredictable.

  Use MSSQLDRV
  String Clause
  Move "ITEM_ID = 'MODEMS'" To Clause
  MSSQL_SetConstraint OrderDtl.File_number Clause
  Repeat
      Find Gt OrderDtl By Index.1
      [Found] Showln "Order detail for a modem is: "
      OrderDtl.Order_Number ", " OrderDtl.Detail_number
      Find Lt OrderDtl By Index.1  // The result of this command is unpredictable
  Until [Not Found]
  MSSQL_SetConstraint OrderDtl.File_number ""
  

• You cannot change the buffer between finds to re-seed the find logic. Doing that will result in the first record being found again. If we adjust the previous code to the example below, the repeat loop will turn into an endless loop since you are finding the same record repeatedly.

  Use MSSQLDRV
  String Clause
  Move "ITEM_ID = 'MODEMS'" To Clause
  MSSQL_SetConstraint OrderDtl.File_number Clause
  Repeat
      Find Gt OrderDtl By Index.1
      [Found] Showln "Order detail for a modem is: "
      OrderDtl.Order_Number ", " OrderDtl.Detail_number
      Move SomeValue To OrderDtl.Order_number
  Until [Not Found]
  MSSQL_SetConstraint OrderDtl.File_number ""
  

• You can use find, save, and delete commands on other tables where no MSSQL_SetConstraint is in force. You could, for example, expand the code above and show the customer names for the customers that bought modems. To achieve this, you would need to find an OrderHeader and a Customer record.

• You can end looping through an MSSQL_SetConstraint block anytime you like. If we wanted to find only two records in the sample above, we could add a counter, increment it for every found record, and break the loop when the counter is two.

  Use MSSQLDRV
  String Clause
  Integer Counter
  Move 0 To Counter
  Move "ITEM_ID = 'MODEMS'" To Clause
  MSSQL_SetConstraint OrderDtl.File_number Clause
  Repeat
      Find Gt OrderDtl By Index.1
      [Found] Showln "Order detail for a modem is: " ;
      OrderDtl.Order_Number ", " OrderDtl.Detail_number
      Increment Counter
      If (Counter >= 2) Break
  Until [Not Found]
  MSSQL_SetConstraint OrderDtl.File_number ""
  

• You must use SQL syntax in the string that is passed.

• If you want to write portable applications that support multiple database drivers, you can use the general form of the MSSQL_SetConstraint command, CLI_SetConstraint. CLI_SetConstraint performs the same functionality. In fact, it is called from the MSSQL_SetConstraint command. The CLI_SetConstraint command works exactly as described for MSSQL_SetConstraint. It gets an extra parameter, the identification of the driver that you want to set the constraint for. If we changed the code above to be more generic, it would look like the sample below.

  Use MSSQL_DRV
  String Clause
  String DriverId
  Move MSSQLDRV_ID To DriverId
  Move "ITEM_ID = 'MODEMS'" To Clause
  CLI_SetConstraint OrderDtl.File_number Clause DriverId
  Repeat
      Find Gt OrderDtl By Index.1
      [Found] Showln "Order detail for a modem is: "
      OrderDtl.Order_Number ", " OrderDtl.Detail_number
  Until [Not Found]
  CLI_SetConstraint OrderDtl.File_number "" DriverId
  

DISCLAIMER

Data Access has done a lot of research into performance issues and design differences between the native DataFlex database and a SQL Server database. We found that the MSSQL_SetConstraint or CLI_SetConstraint command can speed up a connection to an SQL Server database considerably. While we are looking into ways to improve internal driver logic to achieve the kind of results these commands can provide, this is not a trivial task and the outcome of further research is uncertain. In the meantime, Data Access wants to offer this solution to the DataFlex community.

You should be aware that the misuse of these commands could yield unexpected results. It is the developer's responsibility to verify that the results are as expected. All risks associated with the use of these commands are the developer's; Data Access Corporation disclaims any and all liability with respect to the use of the MSSQL_SetConstraint or CLI_SetConstraint command.