Forum

[PatStribley]

Hi All
We have an issue with how the Console is handling special characters. In this case 'É'

When I run a query (on version 8.01.99-s170913) featuring something like:
Where data_name = 'CAFÉ'
It works fine and finds the CAFÉ data

However when we run the same query on version 8.01.99-s180109
It returns no data because it can't read the 'É', (even if we copy it into the query from the console's own output).

Is this part of an intentional change?
Is there a way of future proofing the query to force it to correctly handle this type of character?

It's a bit of a problem if different users get different results based on their console version!

Thanks for any help

[Simon.Darkin]

I have tried reproducing the problem using version 8.01.99-s180109 of Kognitio Console, both 32-bit and 64-bit against a Kognitio database running 8.1, and separately a Kognitio database running 8.2, and in all cases the É is displayed correctly and can be specified in the WHERE clause to correctly filter the appropriate rows. I'd like to make sure I'm using the same version of the ODBC driver and server software that is in place on you renvironment and so can you look to the bottom right of the Kognitio Console window and confirm the System Version and Driver version please?

[Simon.Darkin]

Whilst using Console version 8.01.99-s180109 are you able to retrieve the relevant row using some other predicate and post a screenshot please?

[MikeAtkinson]

I'm looking at this at the moment. There definitely seems to be something wrong, it should take me a few hours to track down.

[MikeAtkinson]

I'm looking at this at the moment. There definitely seems to be something wrong, it should take me a few hours to track down.

My mistake the following script works OK in the recent versions of Console I have tested:

80199s170913
drop table mike.utf8_test;
create table mike.utf8_test (a varchar(1000) character set "UTF8");
insert into mike.utf8_test values ('euro €');
insert into mike.utf8_test values ('u acute ú');
insert into mike.utf8_test values ('e accute é');
insert into mike.utf8_test values ('all greek to me Δδη');
insert into mike.utf8_test values ('ﮐﮋﭪ'); <-- FAILS error: CI1013: Syntax error at character with ASCII value 240, offset 37
insert into mike.utf8_test values ('⇐⇒');
select * from mike.utf8_test where a = 'euro €'; <-- wrong character displayed in output
select * from mike.utf8_test where a = 'u acute ú'; <-- wrong character displayed in output
select * from mike.utf8_test where a = 'e accute é'; <-- wrong character displayed in output
select * from mike.utf8_test where a = 'all greek to me Δδη'; <-- wrong character displayed in output
select * from mike.utf8_test where a = 'ﮐﮋﭪ'; <-- FAILS error: CI1013: Syntax error at character with ASCII value 240, offset 41
select * from mike.utf8_test where a = '⇐⇒'; <-- wrong character displayed in output-- fails for 80199s170913

80199s171128
Fails in the same way as 80199s170913

80199s180109
Fails in the same way as 80199s170913

Fixed bug: Dealing with special characters (UTF16) in Kogscript e.g. €

80199s180327
Script runs OK

80199s180411
Script runs OK

80200s180412
Script runs OK

80200s180416
Script runs OK

[dloinmar]

We are having issues with our nightly refresh from three systems. The source mysql data is not coming in as utf8 so the special is coming in as ENTR�E vs. Entrée. I have attempted to create a new table, then create the view, then create the image and it is failing on creating the image. Any direction is appreciated.

CREATE EXTERNAL TABLE "ADMIN"."T_PRODUCT_CATEGORY" (
"ID" INTEGER,
"PRODUCT_CATEGORY_ID" INTEGER,
"PRODUCT_CATEGORY_DESCRIPTION" VARCHAR(32000) CHARACTER SET UTF16,
"AUDIT_DATE" TIMESTAMP(6),
"AUDIT_USER" VARCHAR(32000) CHARACTER SET UTF16,
"PARENT_ID" INTEGER,
"CLIENTID" SMALLINT) FROM "ORC_LOCAL" TARGET 'uri_path "/data/networks/promotions/digital/dpn/admin/Product_Category"';


--- Create View in Admin schema
CREATE VIEW "ADMIN"."PRODUCT_CATEGORY" as
select
id
,product_category_id
,product_category_description
,audit_date
,audit_user
,parent_id
,clientid
from "ADMIN".T_PRODUCT_CATEGORY;


--Create Image in admin schema
CREATE VIEW IMAGE "ADMIN"."PRODUCT_CATEGORY";

I'm looking at this at the moment. There definitely seems to be something wrong, it should take me a few hours to track down.

My mistake the following script works OK in the recent versions of Console I have tested:

80199s170913
drop table mike.utf8_test;
create table mike.utf8_test (a varchar(1000) character set "UTF8");
insert into mike.utf8_test values ('euro €');
insert into mike.utf8_test values ('u acute ú');
insert into mike.utf8_test values ('e accute é');
insert into mike.utf8_test values ('all greek to me Δδη');
insert into mike.utf8_test values ('ﮐﮋﭪ'); <-- FAILS error: CI1013: Syntax error at character with ASCII value 240, offset 37
insert into mike.utf8_test values ('⇐⇒');
select * from mike.utf8_test where a = 'euro €'; <-- wrong character displayed in output
select * from mike.utf8_test where a = 'u acute ú'; <-- wrong character displayed in output
select * from mike.utf8_test where a = 'e accute é'; <-- wrong character displayed in output
select * from mike.utf8_test where a = 'all greek to me Δδη'; <-- wrong character displayed in output
select * from mike.utf8_test where a = 'ﮐﮋﭪ'; <-- FAILS error: CI1013: Syntax error at character with ASCII value 240, offset 41
select * from mike.utf8_test where a = '⇐⇒'; <-- wrong character displayed in output-- fails for 80199s170913

80199s171128
Fails in the same way as 80199s170913

80199s180109
Fails in the same way as 80199s170913

Fixed bug: Dealing with special characters (UTF16) in Kogscript e.g. €

80199s180327
Script runs OK

80199s180411
Script runs OK

80200s180412
Script runs OK

80200s180416
Script runs OK

[dloinmar]

So I've have confirmed the changes work for utf8 for the table. Now I want to just alter the table for the column to character set of utf8 without dropping the table and invalidate the dependent views (which we have alot of). When running the following, I get an error. What is the proper syntax for altering the table with the correct character set?

ALTER TABLE "ADMIN"."T_PRODUCT_CATEGORY"
ALTER COLUMN "PRODUCT_CATEGORY_DESCRIPTION" ALTER CHARACTER SET AS UTF8
CASCADE INVALIDATE DEPENDENT VIEWS ON ERRORS;


alter TABLE "ADMIN"."T_PRODUCT_CATEGORY"
ALTER COLUMN "PRODUCT_CATEGORY_DESCRIPTION" ALTER CHARACTER SET UTF8
CASCADE INVALIDATE DEPENDENT VIEWS ON ERRORS
42000: [Kognitio][WX2 Driver][servername] CI2013: Expected TREAT at or near UTF8, offset 105 "..IPTION" ALTER CHARACTER SET -->UTF8<--"

We are having issues with our nightly refresh from three systems. The source mysql data is not coming in as utf8 so the special is coming in as ENTR�E vs. Entrée. I have attempted to create a new table, then create the view, then create the image and it is failing on creating the image. Any direction is appreciated.

CREATE EXTERNAL TABLE "ADMIN"."T_PRODUCT_CATEGORY" (
"ID" INTEGER,
"PRODUCT_CATEGORY_ID" INTEGER,
"PRODUCT_CATEGORY_DESCRIPTION" VARCHAR(32000) CHARACTER SET UTF16,
"AUDIT_DATE" TIMESTAMP(6),
"AUDIT_USER" VARCHAR(32000) CHARACTER SET UTF16,
"PARENT_ID" INTEGER,
"CLIENTID" SMALLINT) FROM "ORC_LOCAL" TARGET 'uri_path "/data/networks/promotions/digital/dpn/admin/Product_Category"';


--- Create View in Admin schema
CREATE VIEW "ADMIN"."PRODUCT_CATEGORY" as
select
id
,product_category_id
,product_category_description
,audit_date
,audit_user
,parent_id
,clientid
from "ADMIN".T_PRODUCT_CATEGORY;


--Create Image in admin schema
CREATE VIEW IMAGE "ADMIN"."PRODUCT_CATEGORY";

I'm looking at this at the moment. There definitely seems to be something wrong, it should take me a few hours to track down.

My mistake the following script works OK in the recent versions of Console I have tested:

80199s170913
drop table mike.utf8_test;
create table mike.utf8_test (a varchar(1000) character set "UTF8");
insert into mike.utf8_test values ('euro €');
insert into mike.utf8_test values ('u acute ú');
insert into mike.utf8_test values ('e accute é');
insert into mike.utf8_test values ('all greek to me Δδη');
insert into mike.utf8_test values ('ﮐﮋﭪ'); <-- FAILS error: CI1013: Syntax error at character with ASCII value 240, offset 37
insert into mike.utf8_test values ('⇐⇒');
select * from mike.utf8_test where a = 'euro €'; <-- wrong character displayed in output
select * from mike.utf8_test where a = 'u acute ú'; <-- wrong character displayed in output
select * from mike.utf8_test where a = 'e accute é'; <-- wrong character displayed in output
select * from mike.utf8_test where a = 'all greek to me Δδη'; <-- wrong character displayed in output
select * from mike.utf8_test where a = 'ﮐﮋﭪ'; <-- FAILS error: CI1013: Syntax error at character with ASCII value 240, offset 41
select * from mike.utf8_test where a = '⇐⇒'; <-- wrong character displayed in output-- fails for 80199s170913

80199s171128
Fails in the same way as 80199s170913

80199s180109
Fails in the same way as 80199s170913

Fixed bug: Dealing with special characters (UTF16) in Kogscript e.g. €

80199s180327
Script runs OK

80199s180411
Script runs OK

80200s180412
Script runs OK

80200s180416
Script runs OK

[markc]

I suspect you've tried ALTER TABLE x ALTER COLUMN y ALTER CHARACTER SET TREAT AS UTF8 CASCADE INVALIDATE DEPENDENT VIEWS ON ERRORS, as you included that in a message outside this forum.

As you will have seen, that gives "CI413C: Operation not valid on external table" in 8.2.0rel180412, so that will be handled as an enhancement in future.

In the interim, you can use "DROP TABLE x INVALIDATE DEPENDENT VIEWS" followed by "CREATE EXTERNAL TABLE x (new definition)" and "RECREATE INVALIDATED VIEWS ON <table_id>", where table_id is the id you see for immediate dependent views when doing "EXPLAIN <viewname>". As previously discussed, it is best practice to do this first on a test system before using in production.

So I've have confirmed the changes work for utf8 for the table. Now I want to just alter the table for the column to character set of utf8 without dropping the table and invalidate the dependent views (which we have alot of). When running the following, I get an error. What is the proper syntax for altering the table with the correct character set?

ALTER TABLE "ADMIN"."T_PRODUCT_CATEGORY"
ALTER COLUMN "PRODUCT_CATEGORY_DESCRIPTION" ALTER CHARACTER SET AS UTF8
CASCADE INVALIDATE DEPENDENT VIEWS ON ERRORS;


alter TABLE "ADMIN"."T_PRODUCT_CATEGORY"
ALTER COLUMN "PRODUCT_CATEGORY_DESCRIPTION" ALTER CHARACTER SET UTF8
CASCADE INVALIDATE DEPENDENT VIEWS ON ERRORS
42000: [Kognitio][WX2 Driver][servername] CI2013: Expected TREAT at or near UTF8, offset 105 "..IPTION" ALTER CHARACTER SET -->UTF8<--"

We are having issues with our nightly refresh from three systems. The source mysql data is not coming in as utf8 so the special is coming in as ENTR�E vs. Entrée. I have attempted to create a new table, then create the view, then create the image and it is failing on creating the image. Any direction is appreciated.

CREATE EXTERNAL TABLE "ADMIN"."T_PRODUCT_CATEGORY" (
"ID" INTEGER,
"PRODUCT_CATEGORY_ID" INTEGER,
"PRODUCT_CATEGORY_DESCRIPTION" VARCHAR(32000) CHARACTER SET UTF16,
"AUDIT_DATE" TIMESTAMP(6),
"AUDIT_USER" VARCHAR(32000) CHARACTER SET UTF16,
"PARENT_ID" INTEGER,
"CLIENTID" SMALLINT) FROM "ORC_LOCAL" TARGET 'uri_path "/data/networks/promotions/digital/dpn/admin/Product_Category"';


--- Create View in Admin schema
CREATE VIEW "ADMIN"."PRODUCT_CATEGORY" as
select
id
,product_category_id
,product_category_description
,audit_date
,audit_user
,parent_id
,clientid
from "ADMIN".T_PRODUCT_CATEGORY;


--Create Image in admin schema
CREATE VIEW IMAGE "ADMIN"."PRODUCT_CATEGORY";