Hi Hans,
Please allow me to ask one more question. For the error message below, it
causes some misunderstanding. How it comes that Source Type is 21 BYTES but the
Target Type is 24 BYTES?
As I know, one UTF8 character corresponds to 4 bytes, that means 7 UTF8
characaters is 7*4=28 bytes. This does no match with 21 BYTES, right? I think
the value of 21 should be 28.
SQL>insert into test_utf8 values('我我我我我我我');
*** ERROR[8402] A string overflow occurred during the evaluation of a character
expression. Conversion of Source Type:VARCHAR(REC_BYTE_V_ASCII,21 BYTES,UTF8)
Source Value:我我我我我我我 to Target Type:VARCHAR(REC_BYTE_V_ASCII,24 BYTES,UTF8).
[2017-06-28 11:23:42]
Best regards,
Yuan
Email: [email protected]<mailto:[email protected]>
Cellphone: (+86) 13671935540
From: Hans Zeller [mailto:[email protected]]
Sent: Wednesday, June 28, 2017 2:38 AM
To: [email protected]
Subject: RE: How many Chinese characters can be stored in varchar(n chars)
character set utf8?
Hi,
The problem is how sqlci interprets its input data. In your case, sqlci
interprets the data as ISO8859-1, that makes the input 3*3 bytes or 9 ISO
8859-1 characters. It then translates those 9 (junk) characters into UTF-8,
requiring 18 bytes, and those don't fit into the target table. Once you tell
sqlci to interpret its input as UTF-8, it works. Also, you need the terminal
that you are using to use UTF-8 (in my case that's a Linux xterm which is set
to UTF-8):
Apache Trafodion Conversational Interface 2.2.0
Copyright (c) 2015-2017 Apache Software Foundation
>>create table test_utf8(a varchar(6) character set utf8);
--- SQL operation complete.
>>insert into test_utf8 values('我我我');
*** ERROR[8402] A string overflow occurred during the evaluation of a character
expression. Conversion of Source Type:VARCHAR(REC_BYTE_V_ASCII,18 BYTES,UTF8)
Source Value:我我我 to Target Type:VARCHAR(REC_BYTE_V_ASCII,24 BYTES,UTF8).
--- 0 row(s) inserted.
>>set terminal_charset utf8;
>>insert into test_utf8 values('我我我');
--- 1 row(s) inserted.
Note the "set terminal_charset utf8" statement in sqlci that made it work.
When I ran trafci from a Linux prompt it worked - it seems trafci on Linux
assumes its input is UTF-8.
My recommendation: a) use a terminal (e.g. xterm) that is set to UTF-8, b) if
using sqlci, set the terminal charset of sqlci to utf8, c) type UTF-8
characters into the terminal.
Hans
From: Liu, Yuan (Yuan) [mailto:[email protected]]
Sent: Tuesday, June 27, 2017 12:52 AM
To:
[email protected]<mailto:[email protected]>
Subject: RE: How many Chinese characters can be stored in varchar(n chars)
character set utf8?
Hmm.. Especially the error message caused misunderstanding,
21 bytes can not be inserted into 24 bytes?
*** ERROR[8402] A string overflow occurred during the evaluation of a character
expression. Conversion of Source Type:VARCHAR(REC_BYTE_V_ASCII,21 BYTES,UTF8)
Source Value:我我我我我我我 to Target Type:VARCHAR(REC_BYTE_V_ASCII,24 BYTES,UTF8).
[2017-06-27 15:44:45]
Best regards,
Yuan
Email: [email protected]<mailto:[email protected]>
Cellphone: (+86) 13671935540
From: Xu, Kai-Hua (Kevin) [mailto:[email protected]]
Sent: Tuesday, June 27, 2017 3:49 PM
To:
[email protected]<mailto:[email protected]>
Subject: RE: How many Chinese characters can be stored in varchar(n chars)
character set utf8?
I got the same error on sqlci. I would suggest you use trafci. Ture, it an
issue.
[cid:[email protected]]
Best Regards,
Kevin Xu
From: Xu, Kai-Hua (Kevin)
Sent: Tuesday, June 27, 2017 3:41 PM
To:
[email protected]<mailto:[email protected]>
Subject: RE: How many Chinese characters can be stored in varchar(n chars)
character set utf8?
Hi Yuan,
Curious, it works for me.
[cid:[email protected]]
Best Regards,
Kevin Xu
From: Liu, Yuan (Yuan) [mailto:[email protected]]
Sent: Tuesday, June 27, 2017 2:54 PM
To:
[email protected]<mailto:[email protected]>
Subject: How many Chinese characters can be stored in varchar(n chars)
character set utf8?
Hi Trafodioneers,
I created one table as below,
>>create table test_utf8(a varchar(6) character set utf8);
--- SQL operation complete.
>>showddl test_utf8;
CREATE TABLE TRAFODION.SEABASE.TEST_UTF8
(
A VARCHAR(6 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
Then I tried to insert Chinese characters into the table,
>>insert into test_utf8 values('我我我');
*** ERROR[8402] A string overflow occurred during the evaluation of a character
expression. Conversion of Source Type:VARCHAR(REC_BYTE_V_ASCII,18 BYTES,UTF8)
Source Value:我我我 to Target Type:VARCHAR(REC_BYTE_V_ASCII,24 BYTES,UTF8).
>>insert into test_utf8 values('我我');
--- 1 row(s) inserted.
It seems that I can only insert 2 Chinese characters into the column, which is
not as expected as I thought.
So I‘d want to ask how should I define the data type if I want to store N
Chinese characters?
Another thing is that, from the error message, why the Source Type of ‘我我我’ is
18 bytes, utf8 while the target type is 24 bytes, utf8?
Best regards,
Yuan
Email: [email protected]<mailto:[email protected]>
Cellphone: (+86) 13671935540
