Tip!    Demonstration of the Progress 4GL COLLATE Phrase

Introduction

This page will introduce you to the Progress 4GL COLLATE phrase, explain why you would use it, provide an overview of the syntax, and demonstrate collations changing at run-time. This 4GL feature is closely related to the COMPARE function, and once you understand one, the other will be understood. This page is not attempting to be a detailed reference for either feature. Please refer to the Progress documentation.
Contents of this page include:

Instructions for Downloading Source Code

The source code for the demonstration is available for download. It is a zip file with a single .p file in it. It shouldn't take long to download, especially if you use a broadband service such as O2 Broadband in the UK.

The program demonstrates collations dynamically changing using the COLLATE phrase.

The program populates a temp-table with strings beginning with each of the characters of the current code page. It then displays the strings in a browse. A drop-down box allows the user to select different collations. As the collations are changed, the browse is updated and the new sort ordering of the strings can be seen.

To Top

Uses of the COLLATE phrase and the COMPARE function

These 2 features give the 4GL the ability to dynamically change the rules for comparing strings or sorting query results. The primary motivation for the addition of these features to the 4GL was to give the 4GL the ability to compare and sort Unicode data with collation rules other than "binary". However, the ability to dynamically change collation is a significant feature for applications that support users with different languages, whether or not they are Unicode-based.

For example, using these features you can now have a single client product multiple reports, each one sorted using different collation rules such as those of the French, Spanish, German, etc. languages.

You can also present users data that is displayed in a browse that dynamically changes the ordering of the data. This is useful for applications that run in a kiosk. Users can walk-up to use the application, and begin by selecting a language, which will determine the collation, date format, and other presentation formats suitable for the user. The application can dynamically change collation, instead of having to exit and invoke a new application with different startup parameters. The features can also be used in an Application Server.

The collating of the query results is performed on the client (or the Application Server) and this can reduce performance relative to having data ordered and selected by an index. The impact on performance will depend on the size of the result set, the horsepower of the client machine and other factors. However, the COLLATE phrase and the COMPARE function are the only way you can offer dynamic collation in the 4GL. If you are looking for an alternative to binary collation for Unicode data, then the XenCraft XenPUC product is a solution.

To Top

Syntax of the COLLATE phrase

The COLLATE phrase is available in version 9.1A and later releases.
The COLLATE phrase can be used with any of the following query statements: FOR, OPEN QUERY, or PRESELECT.

The syntax of the COLLATE phrase is:

BY COLLATE(FIELDNAME, Strength[, CollationTableName]) [DESCENDING]

where square brackets ("[" and "]") indicate optional syntax and where:

  • FIELDNAME is the name of the field to be sorted by,
  • Strength is one of the strings: RAW, CASE-SENSITIVE, CASE-INSENSITIVE, CAPS,
  • CollationTableName is the name of one of the collation tables installed in convmap.cp and compatible with the current code page (SESSION:CPINTERNAL). Specifying the CollationTableName is optional.

The Strength determines the way strings are compared.

  • RAW uses the binary value of each character (i.e. it's code point) in the string for comparison. (No collation table is used. Any specified values for CollationTableName are ignored.)
  • CASE-SENSITIVE, CASE-INSENSITIVE use the character's binary value to index into the appropriate collation table (case-sensitive or case-insensitive) and retrieve the character's sort weight or ranking. The rankings are then compared to determine the sort order of the string.
  • CAPS uses the character's binary value to index into the SESSION:CPCASE upper-case table to retrieve the upper-case form of the letter. The upper-case character's code point is then used as the sort weight for comparison. This is the same approach that MATCHES uses for string comparison.
To Top

The COMPARE function

A related 4GL function is the COMPARE function. It compares two strings using the Strength and CollationTableName specified in the function.

To Top

Example Query using both COMPARE and COLLATE

An example of a query follows, that both

  • selects data in a query using dynamically selectable rules via the COMPARE function, and
  • orders the result set using dynamically selectable rules via the COLLATE phrase 
/* Return records where the field NAME is less than or equal to the string "z",
   using case-sensitive comparisons, and the collation rules of the collation table
   named in the CHAR variable collation.
   Order the resulting record set using the same collation rules */

DEF VAR collation AS CHAR INIT "BASIC".
FOR EACH CUSTOMER where COMPARE(NAME, "LE", "z", "CASE-SENSITIVE", collation)
 BY COLLATION(NAME, "CASE-SENSITIVE", collation):
...
END.
To Top

Browsing with Dynamic Collation

The program that follows demonstrates dynamically changing collation. The program is short, requires no database and can be downloaded. (See the download instructions.) The data being sorted consists of strings beginning with a character followed by its decimal value followed by the character again. (With some spaces and dashes added for ease of reading.)

Here are 3 screen shots from the program, showing the differences between the collations BINARY, GERMAN9, and NORWEGIAN. In the BINARY screen shot, the COLLATE phrase is using "RAW" strength to sort the data by the binary value of each character, or to say it another way, as the characters are ordered in the current code page (1252). Note that the upper case English characters sort together followed by some punctuation characters. The lower case English letters follow later.

 demonstration of binary ordering of text

The next screenshot shows some of the collations available with the current code page in the dropdown. The selected collation, Norwegian, causes the upper and lowercase characters to be mixed together. Also note that the characters A-umlaut "Ä" and AE ligature Æ sort after the letter "Z".

demonstration of norwegian ordering of text

Finally, here is the result of switching the collation to GERMAN9. The characters A-umlaut and AE ligature no longer sort after the "Z", and instead sort near the unaccented letter "A".

demonstration of binary ordering of text
To Top

Program Collate.p

Here is the program. Look at the very last procedure to see the use of the COLLATE phrase in the OPEN QUERY statement. 

CREATE WIDGET-POOL.

DEFINE TEMP-TABLE customer
    FIELD NAME AS CHAR FIELD bin AS INT
    INDEX bybib bin ASCENDING.

DEF VAR collation AS CHAR.

DEFINE VAR C-Win AS WIDGET-HANDLE NO-UNDO.

DEFINE BUTTON BUTTON-1 LABEL "Quit" SIZE 10 BY 1.2.

DEFINE VARIABLE COMBO-BOX-1 AS CHARACTER FORMAT "X(256)":U
     LABEL "Collation"
     VIEW-AS COMBO-BOX INNER-LINES 8
     LIST-ITEMS "Item 1","item 2","item 3"
     DROP-DOWN-LIST  SIZE 25 BY 1 NO-UNDO.

DEFINE QUERY BROWSE-1 FOR Customer FIELDS (NAME) SCROLLING.

DEFINE BROWSE BROWSE-1  QUERY BROWSE-1 NO-LOCK
    DISPLAY
      Customer.Name COLUMN-LABEL "My Sorted Sordid Data" FORMAT "x(50)":U
    WITH NO-ROW-MARKERS SEPARATORS SIZE 35 BY 10 EXPANDABLE TITLE "Sorted Data".

DEFINE FRAME DEFAULT-FRAME
     COMBO-BOX-1 AT ROW 2 COL 15 COLON-ALIGNED
     BUTTON-1 AT ROW 2 COL 55  BROWSE-1 AT ROW 5 COL 35
    WITH 1 DOWN NO-BOX KEEP-TAB-ORDER OVERLAY
         SIDE-LABELS NO-UNDERLINE THREE-D
         AT COL 1 ROW 1    SIZE 80 BY 16
         DEFAULT-BUTTON BUTTON-1 CANCEL-BUTTON BUTTON-1.

CREATE WINDOW C-Win ASSIGN
     HIDDEN             = YES
     TITLE              = "Demonstrate Collate Phrase"
     HEIGHT             = 16  WIDTH  = 80
     MAX-HEIGHT         = 16  MAX-WIDTH = 80
     VIRTUAL-HEIGHT     = 16  VIRTUAL-WIDTH = 80
     RESIZE             = yes
     SCROLL-BARS        = NO  STATUS-AREA = no
     BGCOLOR            = ?   FGCOLOR = ?
     KEEP-FRAME-Z-ORDER = YES  THREE-D = yes
     MESSAGE-AREA       = NO   SENSITIVE = yes.

IF SESSION:DISPLAY-TYPE = "GUI":U AND VALID-HANDLE(C-Win)
THEN C-Win:HIDDEN = no.

ON END-ERROR OF C-Win OR ENDKEY OF C-Win ANYWHERE DO:
END.

ON WINDOW-CLOSE OF C-Win DO:
  APPLY "CLOSE":U TO THIS-PROCEDURE.
  RETURN NO-APPLY.
END.

ON CHOOSE OF BUTTON-1 IN FRAME DEFAULT-FRAME /* Quit */ DO:
  APPLY "WINDOW-CLOSE" TO c-win.
END.

ON VALUE-CHANGED OF COMBO-BOX-1 IN FRAME DEFAULT-FRAME /* Select a collation */
DO:
  ASSIGN collation = combo-box-1:SCREEN-VALUE.
  RUN disp-names. /* change query */
END.

ASSIGN CURRENT-WINDOW                = C-Win
       THIS-PROCEDURE:CURRENT-WINDOW = C-Win.

ON CLOSE OF THIS-PROCEDURE
   RUN disable_UI.

PAUSE 0 BEFORE-HIDE.

DEF VAR i AS INT.
DO i = 65 TO 255:    /* populate the temp-table */
    IF i > 127 AND i < 160 THEN NEXT.
    CREATE customer.
    ASSIGN NAME = "    " + CHR(i) + "   -" + STRING(i, "999    ") + CHR(i)
        bin = i.
END.

MAIN-BLOCK:
DO ON ERROR   UNDO MAIN-BLOCK, LEAVE MAIN-BLOCK
   ON END-KEY UNDO MAIN-BLOCK, LEAVE MAIN-BLOCK:
   RUN enable_UI.
   WAIT-FOR CLOSE OF THIS-PROCEDURE.
END.

PROCEDURE disable_UI :
  IF SESSION:DISPLAY-TYPE = "GUI":U AND VALID-HANDLE(C-Win)
  THEN DELETE WIDGET C-Win.
END PROCEDURE.

PROCEDURE enable_UI :
  ASSIGN  /* initialize the collation combo-box */
      combo-box-1:LIST-ITEMS  IN FRAME DEFAULT-FRAME  = "Binary," + GET-COLLATIONS(SESSION:CPINTERNAL)
      combo-box-1:SCREEN-VALUE  IN FRAME DEFAULT-FRAME  = SESSION:CPCOLL.
      collation = SESSION:CPCOLL.
  DISPLAY COMBO-BOX-1
      WITH FRAME DEFAULT-FRAME IN WINDOW C-Win.
  ENABLE COMBO-BOX-1 BUTTON-1 BROWSE-1
      WITH FRAME DEFAULT-FRAME IN WINDOW C-Win.
 RUN disp-names.
  VIEW C-Win.
END PROCEDURE.

PROCEDURE disp-names.
    /* disp-names resets the browse to use new collation.
    If the collation is binary, the strength is changed to RAW to
    cause a binary collation to be used. Otherwise the collation table
    named in the VAR collation sets the sort ordering.*/
    DEF VAR strength AS CHAR.
    IF collation ="Binary" THEN strength = "RAW".
       ELSE strength = "CASE-INSENSITIVE".

    OPEN QUERY browse-1
        FOR EACH customer NO-LOCK BY COLLATE(name, strength , collation).
END PROCEDURE.
To Top
Copyright © 2002-2010 XenCraft. All rights reserved.