-
PatStribley
- Single Poster
- Offline
-
- Posts: 1
- Joined: Thu May 10, 2018 2:01 pm
-
Simon.Darkin
- Contributor
- Offline
- Posts: 23
- Joined: Thu May 23, 2013 5:11 pm
-
Simon.Darkin
- Contributor
- Offline
- Posts: 23
- Joined: Thu May 23, 2013 5:11 pm
-
MikeAtkinson
- Contributor
- Offline
- Posts: 185
- Joined: Wed May 29, 2013 2:10 pm
- Location: Bracknell
-
MikeAtkinson
- Contributor
- Offline
- Posts: 185
- Joined: Wed May 29, 2013 2:10 pm
- Location: Bracknell
-
dloinmar
- Multiple Poster
- Offline
-
- Posts: 2
- Joined: Fri Nov 02, 2018 1:08 pm
-
dloinmar
- Multiple Poster
- Offline
-
- Posts: 2
- Joined: Fri Nov 02, 2018 1:08 pm
-
markc
- Contributor
- Offline
-
- Posts: 386
- Joined: Thu May 23, 2013 4:48 pm
Kognitio Console issues, advice, etc.
Issue handling special characters
by PatStribley » Thu May 10, 2018 2:13 pm
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
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
Re: Issue handling special characters
by Simon.Darkin » Fri May 11, 2018 9:48 am
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?
Re: Issue handling special characters
by Simon.Darkin » Fri May 11, 2018 10:15 am
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?
Re: Issue handling special characters
by MikeAtkinson » Mon May 14, 2018 9:07 am
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.
Re: Issue handling special characters
by MikeAtkinson » Mon May 14, 2018 1:12 pm
My mistake the following script works OK in the recent versions of Console I have tested:MikeAtkinson wrote: ↑Mon May 14, 2018 9:07 amI'm looking at this at the moment. There definitely seems to be something wrong, it should take me a few hours to track down.
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
Re: Issue handling special characters
by dloinmar » Fri Nov 02, 2018 1:12 pm
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";
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";
MikeAtkinson wrote: ↑Mon May 14, 2018 1:12 pmMy mistake the following script works OK in the recent versions of Console I have tested:MikeAtkinson wrote: ↑Mon May 14, 2018 9:07 amI'm looking at this at the moment. There definitely seems to be something wrong, it should take me a few hours to track down.
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
Re: Issue handling special characters
by dloinmar » Wed Nov 07, 2018 9:15 pm
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<--"
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<--"
dloinmar wrote: ↑Fri Nov 02, 2018 1:12 pmWe 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";
MikeAtkinson wrote: ↑Mon May 14, 2018 1:12 pmMy mistake the following script works OK in the recent versions of Console I have tested:MikeAtkinson wrote: ↑Mon May 14, 2018 9:07 amI'm looking at this at the moment. There definitely seems to be something wrong, it should take me a few hours to track down.
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
Re: Issue handling special characters
by markc » Thu Nov 08, 2018 8:42 am
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.
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.
dloinmar wrote: ↑Wed Nov 07, 2018 9:15 pmSo 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<--"
dloinmar wrote: ↑Fri Nov 02, 2018 1:12 pmWe 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";
8 posts
• Page 1 of 1
Who is online
Users browsing this forum: No registered users and 1 guest