The Case of the Ignore Case
by
, 6-Oct-2011 at 07:15 AM (18319 Views)
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:
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.
- 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)
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.How to Keep Using Uppercase Columns
- 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.
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.