Non English Characters Truncated with Qlik ODBC Connector And CASE Statement In Data Load Editor

Daniel_Seo — Tue, 20 Oct 2020 15:26:54 GMT

When using CASE statement in "Data load editor". Non-English characters ( Korean characters, Cyrillic string) are being truncated in reloading data through Qlik ODBC connector. English characters work fine.

Environment:

Qlik Sense (all Qlik Sense versions up to November 2019)
Qlik ODBC connecter
Oracle Database (all Oracle Database versions )

Example of SQL Select Statement

LIB CONNECT TO 'Oracle_QlikServer6.domain.local (domain_qvservice)'; SELECT "GDS_CD", "GDS_NM", "GDS_SLE_STCD", "GDS_BZWK_TYPE_CD", "SLE_STCD", "SLE_EDDT", "GLS_CLCD", "GLS_LCCD", "GLS_MCCD", "GLS_SCCD" , CASE WHEN GDS_BZWK_TYPE_CD IN ('212','213') THEN '??????' WHEN GDS_BZWK_TYPE_CD IN ('221','324', '631') THEN '??????' WHEN GDS_BZWK_TYPE_CD IN ('111','211') THEN '???' WHEN GDS_BZWK_TYPE_CD IN ('741','742') THEN '???? ???' ELSE '???????' END AS "????_????" ,CASE WHEN GDS_BZWK_TYPE_CD IN ('101', '201', '106', '206', '102') THEN '??' WHEN GDS_BZWK_TYPE_CD IN ('104', '204') THEN '???' WHEN GDS_BZWK_TYPE_CD IN ('301', '401', '302', '402', '304', '404') THEN '???????' WHEN GLS_LCCD IN ('5', '9') THEN '???' WHEN GDS_BZWK_TYPE_CD IN ('213') THEN '??? ???' WHEN GDS_CD IN ('2120010011','2120010003') THEN '???? ???' WHEN GLS_CLCD IN ('212001') THEN '????' WHEN GDS_BZWK_TYPE_CD IN ('221') THEN '???? ???' WHEN GDS_BZWK_TYPE_CD IN ('324') THEN '????' WHEN GDS_BZWK_TYPE_CD IN ('631') THEN '???' WHEN GDS_BZWK_TYPE_CD IN ('111', '211') THEN '???' WHEN GDS_BZWK_TYPE_CD IN ('741', '742') THEN '????' ELSE '_' END AS "????_????" FROM "GDS";

Cause:

Confirmed the problem with encoding for oracle driver. It is planned to add auto-detect encoding feature for this driver in future releases.

Resolution:

Confirmed the problem with encoding for oracle driver. It is planned to add auto-detect encoding feature for this driver in future releases.

Currently, we can use the following workarounds to avoid this kind of truncation (choose 1 of 3):

Increase maximum char count for problem column in database (as customer said)
Change VARCHAR and VARCHAR2 problem column type to NVARCHAR or NVARCHAR2
Update all existing queries with CAST statement (which have truncated data) with following syntax:

SELECT "COLUMN1", "COLUMN2", CAST(COLUMN3 AS NVARCHAR2(100)) as "COLUMN3" , CAST(COLUMN4 AS NVARCHAR2(100)) as "COLUMN4", "COLUMN5" FROM "OWNER_NAME"."TABLE_NAME";

where:

COLUMN1, COLUMN2 and COLUMN5 are normal columns without problems.
COLUMN3 and COLUMN4 are columns with truncated data.

We can use any value instead of 100 - this number was chosen because the customer has the same string size.

article Non English Characters Truncated with Qlik ODBC Connector And CASE Statement In Data Load Editor in Official Support Articles

Non English Characters Truncated with Qlik ODBC Connector And CASE Statement In Data Load Editor

Environment:

Cause:

Resolution: