XenCraft Making e-business work around the world!
Tip! Demonstration of the Progress 4GL COLLATE Phrase
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.
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.
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.
Syntax of the COLLATE phrase
The COLLATE phrase is available in version 9.1A and later releases.
The syntax of the COLLATE phrase is:
BY COLLATE(FIELDNAME, Strength[, CollationTableName]) [DESCENDING]
where square brackets ("[" and "]") indicate optional syntax and where:
The Strength determines the way strings are compared.
The COMPARE function
A related 4GL function is the COMPARE function. It compares two strings using the Strength and CollationTableName specified in the function.
Example Query using both COMPARE and COLLATE
An example of a query follows, that both
/* 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.
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.
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".
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".
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.