SYMPTOM:
Incorrect results return with additional S appended in the end against Oracle database with CHAR column and UTF8 encoding in Strategy 10.3 till 10.9, and 10.4.1 till 10.4.6.
REPRODUCING STEPS IN TUTORIAL:
1. Set NLS_CHARACTERSET is AL32UTF8 on Oracle DBS side.
Refer to Technical Document below on how to check the character set
KB16101: How to determine the character set or code page of an Oracle, SQL Server, Teradata or a DB2 database
2. Launch DBQuery tool and run a SQL query on the CHAR columns
3. Take one in-house case for example, the column is defined as CHAR(20); Notice the data fetched has an additional S appends in the end.
Line 4344: 160 20 325197 S
Line 4496: 160 20 unknown S
Line 4648: 160 20 354961 S
CAUSE:
This is a known issue in the Strategy ODBC Driver for Oracle Wire Protocol.
The driver tells the column has a precision of 80 instead of 20 in above example, and the cell data is wrongly written back by the driver.
ACTION:
Add a connection option ColumnSizeAsCharacter=1, so that the drive would tell the column precision as the character size defined, in this above example, it is 20.
For adding this option to the Strategy ODBC Driver for Oracle Wire Protocol on Windows, follow the steps below:
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.
Further readings:
Refer to Technical Document below for a similar Strategy ODBC Driver for Oracle Wire Protocol behavior:
KB13618: MicroStrategy ODBC Driver for Oracle Wire Protocol returns results with trailing spaces when using CHAR semantics and UTF8 encoding
Article Reference Number: KB442603
