Skip to content

Uppercase Columns and Connectivity

Uppercase Columns

Uppercased (U_) columns were introduced to be fully compatible with the DataFlex embedded database.

In the DataFlex embedded database, we know the concept of uppercased index segments. This is different from other (SQL-based) databases where this concept does not exist.

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

In other databases, case sensitivity is defined on the column (not on the index segment). It is defined by the used collating sequence that usually can be case sensitive or case insensitive.

Although the handling of case (in)sensitivity is not exactly the same in the DataFlex embedded database and SQL-based databases, in most situations, this does not require the existence of special uppercase (U_) columns. Specifically, when the database backend uses a case insensitive collating sequence (the default on most backends), the uppercase columns are not needed. During find operations, the same records will be found. It makes no difference if these finds are based on the original columns or on the uppercased (U_) columns.

When using a case sensitive collating sequence on the backend, not having U_ columns may cause differences in behavior for existing applications. This will only be the case if the Ignore_Case flag for an index segment is on (Df_Index_Segment_Case = Df_Case_Ignore).

Find operations may find the record with U_ columns but not find it without U_ columns:

Example: If a table has a row with ‘AA’:

Find eq 'aa'

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

Note this behavior difference may also influence relates 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 that has tables with 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 restructure.
  • If existing tables with U_ columns are restructured, the U_ columns will be removed.
  • Existing tables still having 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 has no meaning anymore. Case sensitivity is determined by the used collating sequence of the backend.
  • If tables are used by external tools, removal of U_ columns may require changes.
  • When already using a case insensitive collating sequence on the database backend, 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.

Keep Using Uppercase Columns

If you need to keep using uppercase columns, the Ignore_Ucase_Support 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.

See Also

  • Connectivity Improvements