我们知道Trafodion数据库中有trafci和sqlci两个CLI,trafci走JDBC,而sqlci不是。
今天用trafci和sqlci分别测试插入中文的时候,也遇到一些不同,
//创建测试表
>>create table test_utf8(a varchar(6) character set utf8);
//trafci插入6个中文,成功
SQL>insert into test_utf8 values('我我我我我我');
--- 1 row(s) inserted.
//trafci插入7个中文,失败
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,30 BYTES,UTF8) Source Value:我我我我我我我我我我 to Taget Type:VARCHAR(REC_BYTE_V_ASCII,24 BYTES,UTF8). [2017-06-28 11:12:20]
//sqlci插入6个中文,失败
>>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,36 BYTES,UTF8) Source Value:我我我我我我 to Target Type:VARCHAR(REC_BYTE_V_ASCII,24 BYTES,UTF8).
--- 0 row(s) inserted.
经测试,对于VARCHAR(6) CHARACTER SET UTF8数据类型,从trafci中可以正常的插入6个中文,这符合预期,而从sqlci却无法插入。这是因为,sqlci默认认为插入数据的字符集为ISO88591,而插入中文需要设置字符集为UTF8,因为如果希望从sqlci中正确的插入中文,需要使用以下设置,
set terminal_charset utf8;
如下,在sqlci中,设置charset之前插入失败,而设置charset之后,插入成功!
>>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,36 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.