KB13618: MicroStrategy ODBC Driver for Oracle Wire Protocol returns results with trailing spaces when using CHAR semantics and UTF8 encoding

SYMPTOM:
Strategy ODBC Driver for Oracle Wire Protocol returns results with trailing spaces when using CHAR semantics and UTF8 encoding.
 
There are two ways to specify usage of CHAR semantics on the Oracle database:
 

1. Explicitly set NLS_LENGTH_SEMANTICS = CHAR in the init.ora file
2. Specify the semantics during table creation, such as:
   create table testchar (c1 char (## char))
   
3. where ## represents the size of the Char datatype for column c1.
    
   

In this technical note, method (a) will be used.
 
STEPS TO REPRODUCE:
Follow the steps below to reproduce the issue:
 

• Launch the Strategy ODBC Test tool and connect to an Oracle database that has the following parameters set:
   
  
• NLS_CHARACTERSET = UTF8
• NLS_LENGTH_SEMANTICS = CHAR (set in the init.ora file on the database side)
   
  
• Run the following SQL statement:
  create table testchar (c1 integer, c2 char(32))
  
• From a native Oracle utility (such as SQLPlus) run the statement describe testchar. As seen in the image below, the second column definition appears as CHAR(32 CHAR) which affirms usage of CHAR semantics:
  

• Insert a 32-digit value into the second column such as:
  insert into testchar values (1,'640E752949A02D709B8F75B5DB9727D4')
  
• Run the statement select * from testchar. Double-click on the result cell. Notice the trailing spaces beyond the last letter of the string '640E752949A02D709B8F75B5DB9727D4', as shown in the following image:
  

This is not expected behavior because there should no trailing spaces beyond 32 characters (size of the Char datatype column). The user can export the result to Microsoft Excel and determine that the number of trailing spaces extends up to 96 characters. That is, upon highlighting the individual cell in the MS Excel file, the user will be able to move the cursor along up to triple the number of characters (96 characters). The number of trailing spaces is always twice the length defined. That is, if the column is defined as CHAR(32 CHAR) then the cursor can be moved up to 96 spaces (32 + 32*2).
 
This issue does not occur with a native oracle driver such as the Oracle 10g driver. Perform the above steps using the native oracle driver and it is found that the cursor cannot be moved beyond 32 characters.
 
The issue does not happen with BYTE semantics on the Oracle database.
 
CAUSE:
This is a known issue in the Strategy ODBC Driver for Oracle Wire Protocol. This driver is manufactured by DataDirect Technologies. The following document from the DataDirect Knowledge Base describes this issue:
 
http://knowledgebase.datadirect.com/kbase.nsf/ce24d9443437511f85256e57006bfe2a/a1d7f5b079ca490485256f6b006874ef?OpenDocument
ACTION:
As described in the above-mentioned DataDirect document, the workaround would be to add the connection option ColumnSizeAsCharacter=1. For adding this option to the Strategy ODBC Driver for Oracle Wire Protocol on Windows, follow the steps below:
 

1. Open the registry editor by going to Start -> Run and typing regedit.
2. Go the registry key HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI and go to the specific Data Source Name (DSN).
3. Add a new string called ColumnSizeAsCharacter. Set the value to 1.
4. Rerun select * from testchar and there should be no trailing spaces beyond 32 characters (for the example described in this technical note).

For the Strategy ODBC Driver for Oracle Wire Protocol on UNIX, add the connection option ColumnSizeAsCharacter=1 into the specific DSN section in the odbc.ini file.
 
NOTES:

• For this situation, there is no need to add the second connection option (ColumnsAsChar=1) that is described in the above-mentioned DataDirect document.
• The above workaround is not effective with the Oracle Wire Protocol driver version 5.00.00.50 that is shipped with Strategy release 8.0.1. However, it is effective with the Oracle Wire Protocol driver version 5.10.00.53 that is shipped with Strategy 8.0.2.
• The above workaround is not effective either with the Oracle Wire Protocol driver version 5.20.00.44 that is shipped with Strategy 8.1.0

Registry Modification:
WARNING:
Modifying registry values incorrectly may cause serious, system-wide problems that may require the re-installation of Microsoft Windows NT. Any edit of the registry is done at the user's own risk. Since these are user-initiated changes, they are not covered by any Strategy warranty. If using Microsoft Windows NT, the user should backup the registry and/or update an Emergency Repair Disk (ERD) prior to alterations.