Key/Index Case Sensitivity

BBj uses keys (or indexes in SQL terminology) to skip over portions of the data file during READ RECORD operations in BBj programs and when performing SQL queries with a WHERE clause. This method optimizes the process by returning results in a much quicker fashion rather than looking at all records in a file.

Prior to BBj 12.0, BBx and BBj data files were limited to case sensitive keys on data files as well as newer file types such as XKEYED, VKEYED, and ESQL. Key values were stored in the same case as the data inside the record as all uppercase, lowercase, or mixed case characters. The challenge was to match the exact case used in the key value with the key lookup. For example consider the following code snippet:

XKEYED "MYFILE",[1:6],0,6

DIM rec$:"ID:C(6)"

rec.ID$ = "AbCdEf"

OPEN(1) "MYFILE"

WRITE RECORD(1) rec$

READ RECORD(1, KNUM=0, KEY="abcdef") rec$

The result of this READ RECORD call was a “Missing or duplicate key” error because “AbCdEf” is not considered the same value as “abcdef”.

In BBj 12.0 and higher, case insensitive keys (any combination of case) will find the desired record. So using the same example above with a few modifications, the READ RECORD calls will now return data from the file:

XKEYED "MYFILE",[1:6:"I"],0,6

DIM rec$:"ID:C(6)"

rec.ID$ = "AbCdEf"

OPEN(1) "MYFILE"

WRITE RECORD(1) rec$

READ RECORD(1, KNUM=0, KEY="abcdef") rec$

READ RECORD(1, KNUM=0, KEY="aBCDeF") rec$

In this example, both READ RECORD calls will return the same record since the case is ignored for key lookup. The addition of :"I" in the first line of this code sample modifies this key segment definition.

Case Insensitive Keys With Direct File Access

When accessing data files outside of the BBj SQL engine, READ RECORD is the most common read operation. To create a file, programs typically use the MKEYED, XKEYED, and VKEYED verbs.

Use case insensitive keys in a BBj program

Create the data file specifying the "I" flag on any key segments that should be case insensitive
XKEYED "MYFILE",[1:6:"I"],0,6
Access the records exactly the same way as when using case sensitive segments.
READ RECORD(1, KNUM=0, KEY="abcdef") rec$

NOTE: BBj only supports case insensitive keys in XKEYED, VKEYED, and ESQL files.

Case Insensitive Key With SQL

The BBj SQL engine recognizes the case sensitivity configuration of the data file. If a file created from a BBj program contains case insensitive keys, the SQL engine honors this setting when properly configured. The image below shows where the “Case Insensitive Keys” setting should be set in the Enterprise Manager for a database configuration.

The Enterprise Manager table editing dialog provides an interface for defining indexes which should use case insensitive indexes.

Change an index segment to be case insensitive

Select the database where the table resides.
Select the “Tables” tab.
Double click on the desired table.
Select the “Indices” tab on the table editing dialog.
Create a new index or modify an existing one by selecting it from the list.
Create a new segment or modify an existing one by double clicking the segment definition.
Select the “Case Insensitive” check box on the segment editing dialog.
Click the [Save & Update Data File] button to make the changes to the data file.

Considerations and Recommendations