View RSS Feed

Development Team Blog

The Case of the Ignore Case

Rating: 2 votes, 5.00 average.
The Database Connectivity Kits used to add an uppercase (U_<ORIGINAL name column>) column when tables were converted from DataFlex native databases to an SQL backend. That column was there just to fulfill the need of an uppercase index segment.

However, starting with build 5.0.0.63 (find the latest builds available at ftp://ftp.dataaccess.com/pub/products/connectivity/ ), the Connectivity Kits were changed and by default they simply use the collating sequence defined for the database on the backend and no longer create uppercase columns.

The change was made based on the following factors:
  • Uppercase columns are only useful in some very specific circumstances.
  • When a Case Insensitive collating sequence is used on the database backend, the uppercase columns are not needed.
  • Use of uppercase columns has negative impact on performance
  • Uppercase columns can be confusing to users of external tools (e.g Crystal Reports)
You may control this behavior (i.e. turn on/off the default behavior) by setting the keyword Ignore_Ucase_Support to 0 (turn off) or 1 (turn on) in the Connectivity Kit configuration file or by using Df_Driver_Ignore_UCase_Support or Df_Database_Ignore_UCase_Support attributes in your application.

If you turn off the default behavior, all will behave how it used to in the earlier builds of the Connectivity Kits, but you will need to have good reasons to make that change.

Do You have a reason to change the default behavior?
Since this change may have consequences for existing databases/applications, you should consider whether the best option is to use the default or not. So go over the full explanation on this change to make sure you choose the best option to be applied to your environment.

This is the full explanation on the change (found in SQL Connectivity Recent Changes and Fixes.pdf on the ftp site and also installed with Visual DataFlex):
The Ignore_Ucase_Support flag controls whether special uppercased columns (U_ columns) are created.

If Ignore_Ucase_Support is set to true (the new default), the Connectivity Kit will not create uppercased (U_) columns. Also, for existing tables that have uppercase columns, the uppercased columns will be removed when restructuring those tables.

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.

When the database backend uses a case insensitive collating sequence (the default on most backends), the uppercase columns are not needed--during find operations, for example, 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.

Be aware that 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 (Df_Index_Segment_Case = Df_Case_Ignore) for an index segment is on. Find operations may find the record with U_ columns but not find it without U_ Columns. For example: If a table has a row with ‘AA’, a Find eq ‘aa’ will find the row if table has U_ columns, but will not find it if the table has no U_ columns.

Note that this behavior difference may also influence relates and constrains.

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 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. For example: In Crystal Reports a Verify Database is necessary after removing columns.
  • When already using Case Insensitive collating sequence on the database backend, removal of U_ Columns will NOTcause existing programs to behave differently.
  • When using Case Sensitive collating sequence, existing applications may behave differently after removal of U_ columns.
How to Keep Using Uppercase Columns
If you need to keep using uppercase columns, you will need to change the default driver settings. You may do that by setting Ignore_Ucase_Support to false in the driver configuration file that is used when loading the driver OR by setting the Df_Driver_Ignore_UCase_Support or Df_Database_Ignore_UCase_Support attributes to false at runtime.

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

Other Consequences of the new Default
As a consequence of that default change, if the Connectivity Kit is set to use the database server collate, changes made to the Ignore Case option of index segments will not be saved to the tables.

If you try to make the change to a segment using Visual DataFlex Studio's Table Editor, the Studio seems to save everything (no errors; the output displays dropping the table and recreating everything), but nothing is really changed and the "Ignore Case" check box becomes unchecked again when you click away and come back to the index you had just modified.

Note
Once you converted your DataFlex files to SQL tables, we usually recommend that you make table changes in the tools available for the backend server. This will guarantee that the database objects you create on the server will not be affected by changes you make to the tables through the Studio or Database Builder.

Comments

  1. A Paul Anthony's Avatar
    Sorry to be the bearer of bad news but ftp://ftp.dataaccess.com/pub/products/connectivity/ does NOT contain the latest builds of the connectivity kit. Take MSSQLDRV for example, the last modified date on the FTP site is 19/11/2009 and I know there have been updates since then: VDF16.1 for example which was with version 5.1.0.83 of the driver.

    There's a bit of an assumption that case isn't important and that its the default on the backends and so must be right. Surely by default we as (VDF) developers should to keep our options open, maintain a case sensitive DB and only explicitly enabling IN-sensitive behaviour when it suits us - like the old CK mechanism did.

    The issue of case is important when case-insensitivity adversely affects validation logic, which it will. Consider the use of CODEMAST validation tables, which uses the define indexes to locate valid records and so will inherit the case sensitivity-ness of the database's collation sequence: "AA" is NOT the same as "aa" but there validation table will claim that it is.

    How from the perspective of the application can we know what collation sequence is in use by the backend? We could write embedded SQL to find out, but that's both long winded and misleading (as you pointed out, individual columns may have it set differently). There's nothing to indicate to me that this behaviour is in action - getting the uppercase attribute of the index segment won't help, nor will looking at the DDO's DD_Capslock option which would otherwise have given me a clue.
  2. FrankValcarcel's Avatar
    The statement "Once you converted your DataFlex files to SQL tables, we usually recommend that you make table changes in the tools available for the backend server. This will guarantee that the database objects you create on the server will not be affected by changes you make to the tables through the Studio or Database Builder" is scary.

    In MS-SQL - I can make changes in the SQL Manager that mess up the database from a VDF standpoint. I am confused as don't you want to guarantee that the change syou make with the Studio/DBB are reflected in the database. Is this just a lazy way to get around makign the Studio/DDB handle the different databases correctly.

    At a very minium the above comment should indicate that after making a change to a file with the backend tool you need to re-import it to create the proper INT and CCH files.
    fv
  3. Peter van Mil's Avatar
    Although you might run into problems with a case-insensitive collating sequence (like stated above) I like it very much. I dropped the support for uppercased (U_) columns in alle my applications. One strange thing is the presence of the checkbox "Ignore Case" in Database Builder. With the default setting of Ignore_Ucase_Support this checkbox has no function at all (AFAIK). This might be changed.