Kbase 21085: ODBC and Tables With Double Underscore in Field Name(s)
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
16/10/2008 |
|
Status: Unverified
GOAL:
How to workaround problems with MS Access, SQL-89 ODBC driver and fields with double underscore "__" in the field name.
FIX:
In DataDirect Connect Progress ODBC SQL-89 driver, a feature was implemented to support one-dimensional Arrayed Columns (Extents).
By default, the Progress driver will change all column names from the format a__n to a[n] (where n is the array index) when passing SQL statements to the Progress Database.
However it is also possible to create tables embedding double underscores in the column names. Inserting a backslash (\) just before each of the underscores in the column name instructs the driver not to change the column name ('cust\_\_num' refers to the column 'cust__num', not to cust[num]).
Since not all ODBC applications allow the end user to edit SQL statements directly, DataDirect has also added a hidden option, 'ProcessArrayData', to the Progress ODBC SQL-89 drivers (dated after 12/01/1998) to disable arrayed column support.
Its default value is TRUE (1), which means column names with double underscores will still be changed to the arrayed column syntax. Setting 'ProcessArrayData' to FALSE (0) will cause the ODBC driver not to process or change the column names containing double underscores.
To set the ProcessArrayData hidden option, use the Registry editor: 'regedit.exe' or 'regedt32.exe'.
(WARNING: Improper use of this tool may corrupt your system).
If using a USER ODBC DataSource, select the following registry key:
HKEY_CURRENT_USER\Software\ODBC\OBDC.INI\<yourdsnname>
If using a SYSTEM ODBC DataSource, select the following registry key:
HKEY_LOCAL_COMPUTER\Software\ODBC\OBDC.INI\<yourdsnname>.
Right click, and select 'Edit' > 'New' > 'String Value'. Set the name of the new string to 'ProcessArrayData' and set its value to 0 (zero) to turn OFF arrayed column support.
Two DataSources can be set: one with this feature enabled and the other which has it disabled. Those tables with fields having extents and fields having double underscore characters in the name as well, will not be accessible from MS Access.