Websphere message broker makes a connection to Database using both ODBC and JDBC connections. ODBC driver is using 1208(UTF-8) code page while interacting with Database.
There is a scenario that a message flow uses ODBC DSN to connect to Oracle Database and invokes a stored procedure to retrieve information. One of the fields (description) returned by stored procedure is of Char datatype. The value returned in this field needs to display Trade mark symbol. For example ™. But when the value returned by stored procedure referred the value is changed to ¿.
This is because the ODBC driver which invokes the stored procedure uses a Unicode(UTF-8) encoding and ™ is not present in UTF-8 character set Hence it is returned as a garbage value since the character is unidentified in the UTF-8 character set.
In the table, it is stored properly.
The reason being database is using a different character set 1252 as shown below and 1252 code page supports ™.
Solution Proposed:
The solution proposed is create a new stored procedure by updating the current stored procedure to return a RAW value of the text and after the response is received we would want to convert it to the required code page which would be 1252 in this case. This requires changes to stored procedure and esql.
Changes:
• New procedure to be created by updating existing procedure as below:
CREATE OR REPLACE PROCEDURE TEST
(PROD_ID IN NUMBER,
PROD_COLOR IN VARCHAR2,
— DESC OUT VARCHAR2, (commented the existing)
V_PROD_TXT OUT LONG RAW, (updated field to return as BLOB)
SELECT PROD_ID,
PROD_COLOR,
PROD_TXT, (commented the existing)
SYS.UTL_RAW.CAST_TO_RAW(PROD_TXT), (updated field to return as BLOB)
• Esql mapping to CAST it as character with 1252 code page.
SET OutputRoot.XMLNSC.Prod.ProductDesc =CAST (productDesc AS CHARARCTER CCSID 1252);
