Skip to content

Ignore_Case_Support and Uppercase Columns

The Ignore_Ucase_Support flag controls whether special uppercased columns (U_ columns) are created.

If Ignore_Ucase_Support is set to true (the default), the Connectivity Kit will not create uppercased (U_) columns. Additionally, for existing tables with uppercase columns, the uppercased columns will be removed upon restructuring the table.

Reasons for Changing the Default

The main reasons for changing the default to no longer create uppercase columns include:

  • Specific Use Cases: Uppercase columns are only useful in very specific circumstances. For instance, when a case-insensitive collating sequence is used on the database backend, the uppercase columns are unnecessary.

  • Performance Impact: The use of uppercase columns can negatively impact performance.

  • User Confusion: Uppercase columns may confuse users of external tools (e.g., Crystal Reports).

Uppercase Columns

Uppercased (U_) columns were introduced to ensure full compatibility with the DataFlex embedded database.

In the DataFlex embedded database, the concept of uppercased index segments exists, which differs from other SQL-based databases where this concept does not apply.

In the DataFlex embedded database, columns appearing in an index can be marked as uppercased (or case insensitive) or not. The same column can be uppercased when used as a segment in one index and non-uppercased when used as a segment in another index.

In other databases, case sensitivity is defined at the column level (not at the index segment level) and is determined by the collating sequence, which can usually be either case sensitive or case insensitive.

Although the handling of case (in)sensitivity is not identical between the DataFlex embedded database and SQL-based databases, in most situations, the existence of special uppercase (U_) columns is unnecessary.

Specifically, when the database backend uses a case-insensitive collating sequence (the default on most backends), uppercase columns are not needed. During find operations, the same records will be found regardless of whether the searches are based on the original columns or the uppercased (U_) columns.

When using a case-sensitive collating sequence on the backend, the absence of U_ columns may lead to differences in behavior for existing applications. This will only occur if the Ignore_Case flag for an index segment is enabled (Df_Index_Segment_Case = Df_Case_Ignore).

Example

A find operation may locate the record with U_ columns but not find it without U_ columns:

Find eq 'aa'

This will find the row if the table has U_ columns but will not find it if the table lacks U_ columns.

Note that this behavior difference may also influence relationships and constraints.

Consequences for Existing Databases

The change to no longer generate uppercased (U_) columns may have consequences for existing applications or databases.

If you have an existing database with tables containing uppercased (U_) columns and you are upgrading from an earlier Connectivity Kit version, be aware of the following consequences:

  • The Connectivity Kit will no longer create uppercase (U_) columns during conversion or restructuring.

  • If existing tables with U_ columns are restructured, the U_ columns will be removed.

  • Existing tables still containing U_ columns will behave as before in Open, Find, and Save operations. The U_ columns will be used as before in the SQL statements generated by these operations.

  • The Ignore_case setting (Df_Index_Segment_Case attribute) will always be off. The flag is no longer meaningful, as case sensitivity is determined by the collating sequence used by the backend.

  • If tables are utilized by external tools, the removal of U_ columns may require changes. For example, in Crystal Reports, a "Verify Database" is necessary after removing columns.

  • When already using a case-insensitive collating sequence on the database backend, the removal of U_ columns will not cause existing programs to behave differently.

  • When using a case-sensitive collating sequence, existing applications may behave differently after the removal of U_ columns.

How to Keep Using Uppercase Columns

If you need to continue using uppercase columns, the Ignore_Ucase_Support flag can be set to false in the driver configuration file. Alternatively, the Df_Driver_Ignore_UCase_Support or Df_Database_Ignore_UCase_Support attributes can be set to false at runtime.

If Ignore_Ucase_Support is set to false, the Connectivity Kit will behave the same as earlier versions.