Using Alternate Collating Sequences in Pervasive.SQL
Pervasive.SQL sorts indexes. Alternate Collating Sequences (ACSes) make it possible for characters with accents and other inflections to be sorted as if there were no accent. You can create your own collating sequence.
By default, Pervasive.SQL uses the ASCII table to sort string segments in an index and binary values for sorting numeric data. Using an ACS, you can change the collating sequence for string fields only. Fields known as Overlap fields to DataFlex are sorted as strings.
Creating a table, converting an existing table to Pervasive.SQL, or restructuring a table causes the driver to create indexes. When an ACS is active at that moment, it will use the ACS for each created index. An ACS cannot be dropped by itself. To drop an ACS, you must drop the complete index and recreate it when no ACS is active for the driver.
Creating an ACS File
Before you can use an ACS, you need to define the sorting for it. To do this, you need to create an ACS file, which contains all information about a collation sequence.
An ACS file has a static length of 265 characters:
- The first byte identifies the file as an ACS and should contain the value
0xAC(hexadecimal) or172(decimal). - The next 8 bytes should be used to give the ACS a name. For example, if you create an ACS for German, you might call it
GERMAN. - Unused space should be set to spaces, leaving 256 bytes for the collating table. In the bytes of the collating table, you put the sort weight for each character. The weight (a value between 0 and 255) determines where a character appears when a value is being sorted.
For example, if you want the character ë (ASCII 137) to sort the same as the character e (ASCII 101), you would put the value 101 in Position 146 (137 plus the 9-byte header) of your file. This way, you can build a completely new collating sequence for your indexes.
You can also define that control characters (ASCII 0 to 31) in a string should be sorted at the end. To do this, put the values 0xE0 through 0xFF into the first 32 bytes of your collating table.
Format of an Alternate Collating Sequence File
| Byte Range | Description |
|---|---|
| 0 | Signature byte, always 0xAC (172) |
| 1-8 (8 bytes) | 8-byte name that identifies the table |
| 9-264 (256 bytes) | Collating table |
Making an ACS Active
Once an ACS file has been created, you can make it active by adding a line to your DFBTRDRV.INT file stating:
ACS "MYACS.ACS"
The driver will read the DFBTRDRV.INT file when it is loaded. Any index created by the driver when an ACS is active will be sorted by the collating table specified in the ACS file. If the table is used later without the ACS being active, it will still be sorted using the ACS that was active when the table was created.
Using an ACS with Case-Insensitive Index Segments
An ACS is only used on index segments that are case-sensitive. When the flag for a case-insensitive segment is used, the segment will sort following the ASCII collating sequence. If you want case-insensitive index segments to be sorted by an ACS as well, you need to specify an extra ACS for this by adding a line to your DFBTRDRV.INT file stating:
ACS_UPPER "MYUPACS.ACS"
This will use this ACS for every string-based index segment.
Restructuring a File
When a table is being restructured, the driver will attempt to reuse the ACS an index was using before. This means that restructuring an existing Pervasive.SQL table will keep its original ACS, regardless of whether you have an active ACS or not.
Switching the ACS of an Index
To switch the ACS an index is using, you must drop every index that is using the ACS, reopen the table with the new ACS active, and recreate the indexes.
If you are adding an index and the name of the active ACS is the same as the name of an ACS already in use by the table, then the ACS already in use will be applied. It does not check whether the contents of the ACS are different; it only checks the name of the ACS.
Removing the ACS of an Index
To delete the ACS an index is using, you must drop every index that is using the ACS, reopen the table with no ACS active, and recreate the indexes.
Note
Overlap columns were replaced by multi-segment relationships in DataFlex 11.0 and are now obsolete.