BUFFER-COMPARE statement

Performs a bulk comparison of two records (source and target) by comparing source and target fields of the same name for equality and storing the result in a field. You can specify a list of fields to exclude, or a list of fields to include. You can also specify WHEN...THEN phrases. For all such phrases you specify, the AVM evaluates the WHEN portion, and if it evaluates to TRUE, the AVM executes the THEN portion.

Syntax

`BUFFER-COMPARE source [ { EXCEPT \| USING } field ... ] TO target [ CASE-SENSITIVE \| BINARY ] [ SAVE [ RESULT IN ] result-field ] [ [ EXPLICIT ] COMPARES : WHEN field compare-operator expression THEN statement-or-block ... END [ COMPARES ] ] [ NO-LOBS ] [ NO-ERROR ]`

BUFFER-COMPARE source
  [ { EXCEPT | USING } field ... ] TO target 
  [ CASE-SENSITIVE | BINARY ]
  [ SAVE [ RESULT IN ] result-field ]
  [ [ EXPLICIT ] COMPARES :
        WHEN field compare-operator expression
          THEN statement-or-block ...
      END [ COMPARES ] ] 
  [ NO-LOBS ] [ NO-ERROR ]

source

The source database table, buffer, temp-table, or work table.

EXCEPT field

A list of source fields to exclude from the bulk compare.

USING field

A list of source fields to include in the bulk compare. The USING option is a positive version of the EXCEPT option.

TO target

The target database table, buffer, temp-table, or work table.

CASE-SENSITIVE

Directs the AVM to perform a case-sensitive comparison.

BINARY

Directs the AVM to perform a binary comparison.

SAVE RESULT IN result-field

A variable or field to contain the result of the comparison. The variable or field must be CHARACTER or LOGICAL.

If result-field is CHARACTER, the result is a comma-separated list of fields that failed the comparison, sorted in ascending order.

If result-field is LOGICAL, the result is YES if all fields are equal, or NO if any fields are unequal. In either case, BUFFER-COMPARE stops comparing when it encounters the first inequality.

EXPLICIT COMPARES

Opens a block of WHEN options. If you open the block, you must close it with END COMPARES.

WHEN field

Any data field in the source.

BUFFER-COMPARE removes this field from a USING list or adds this field to an EXCEPT list. This removes the field from the bulk compare and from result-field.

compare-operator

Represents one of the following: LT, LE, GT, GE, EQ, NE, MATCHES, BEGINS, or CONTAINS.

Note: For BLOB or CLOB fields, you can only use the EQ (=) or NE (<>) operators.

expression

Any valid ABL expression.

THEN statement-or-block

Any ABL statement or block. The statement or block executes when the WHEN clause evaluates to TRUE.

END COMPARES

Closes the block of WHEN phrases.

NO-LOBS

Directs the AVM to ignore large object data when comparing records that contain BLOB or CLOB fields.

CAUTION: When using this option, you get no warning if a LOB field has changed. This can lead to inappropriate results. Therefore, before using this option, you must understand the nature of your data and be sure that logic using this option will not result in inconsistent or out-of-date data in the database.

NO-ERROR

The NO-ERROR option is used to prevent the statement from raising ERROR and displaying error messages.

Example

The following example copies a Customer database record into a temp-table to represent an editable working copy, simulates two field changes, and then uses BUFFER-COMPARE to detect which fields differ from the original. The EXCEPT option excludes the primary-key field CustNum from the bulk comparison. Because cChanged is defined as CHARACTER, the result is a comma-separated list of the fields that failed the comparison, sorted in ascending alphabetical order.

DEFINE TEMP-TABLE ttCustomer LIKE Customer.  /* sports2020.Customer */
DEFINE VARIABLE cChanged AS CHARACTER NO-UNDO.
/* Read a Customer record and copy it into the temp-table. */
FIND FIRST Customer NO-LOCK.
CREATE ttCustomer.
BUFFER-COPY Customer TO ttCustomer.
/* Simulate user edits to two fields. */
ASSIGN ttCustomer.CreditLimit = ttCustomer.CreditLimit + 1000
       ttCustomer.Phone       = "555-0100".
/* Compare the edited temp-table record to the original database record.
   CustNum is excluded because it is the primary key and should not change.
   The CHARACTER result lists any fields whose values differ, in ascending
   alphabetical order. */
BUFFER-COMPARE ttCustomer EXCEPT CustNum TO Customer
  SAVE RESULT IN cChanged.
IF cChanged = "" THEN
  MESSAGE "No changes detected."
    VIEW-AS ALERT-BOX INFORMATION.
ELSE
  MESSAGE "Changed fields: " cChanged
    VIEW-AS ALERT-BOX INFORMATION.

The MESSAGE statement displays a dialog similar to:

Changed fields: CreditLimit,Phone

Notes

At compile time, BUFFER-COMPARE:
- Fails to compile if any source-target field pair is not type compatible. An example of such a pair is a field that is LOGICAL in the source, but DECIMAL in the target.
- Excludes from the bulk comparison all EXCEPT field fields and all WHEN field fields.
- Automatically excludes from the bulk comparison fields that appear in the source but not in the target.
- Tries to bind unqualified field names that appear in the EXCEPT and USING options to the source buffer.
At run time, BUFFER-COMPARE:
- Compares all fields not in the EXCEPT phrase and all fields not in the WHEN phrase for equality
- Stores the result in the field that the SAVE phrase specifies, if any
- Evaluates each WHEN option, executing it if its condition evaluates to TRUE
Note: This behavior is different from the behavior of the ABL CASE statement, which executes only the first WHEN option whose condition evaluates to TRUE.
When comparing records that contain BLOB fields, the AVM performs a binary comparison on the BLOB data associated with the source and target records, and reports the results of the comparison.
You cannot use the BUFFER-COMPARE statement to compare records that contain CLOB fields, unless one or both of the corresponding fields contain the Unknown value (?). However, you can convert CLOB fields to LONGCHAR values and use the EQ, GE, GT, LE, LT, or NE comparison operator to compare the LONGCHAR values.
The statement supports three types of comparison for CLOB fields, namely, case-sensitive, binary and case-neutral.
Use the NO-LOBS option with the BUFFER-COMPARE statement to ignore large object data when comparing records that contain BLOB or CLOB fields. You can also use the EXCEPT option to exclude BLOB and CLOB fields from the compare.
When using BUFFER-COMPARE in a WRITE trigger procedure, LOB columns from the OLD BUFFER cannot be compared. Use the NO-LOBS option or the EXCEPT clause to exclude BLOB and CLOB fields. See the TRIGGER PROCEDURE statement for details.

ABL Reference