Chinese string and varchar size

14 Nov 2015

      Hi all,

Last week we find a strange case with chinese character and VARCHAR size.
We have in our database a field defined with VARCHAR(5) and a chinese
string "不要让早把" to be inserted.

If we insert the string with SQL insert everything works find, but if we
try to insert via BULK monet throw an error.
I made several test and i need to extend the varchar from 5 to 10 to get
the bulk insert working.

It looks like BULK somehow counts bits and not characters ?

I give you some infos if you want to reproduce the case:

create table test.varcharsize4
(
id int,
varchar105 varchar(4),
primary key (id)
);

create table test.varcharsize5
(
id int,
varchar106 varchar(5),
primary key (id)
);

create table test.varcharsize9
(
id int,
varchar200 varchar(8),
primary key (id)
);

create table test.varcharsize10
(
id int,
varchar200 varchar(10),
primary key (id)
);

*Insert SQL :*

insert into test.varcharsize4 values (1,'不要让早把');
insert into test.varcharsize5 values (1,'不要让早把');
insert into test.varcharsize9 values (1,'不要让早把');
insert into test.varcharsize10 values (1,'不要让早把');

*BULK Line to insert :*

"1","不要让早把"

*BULK executions :*

COPY 10 RECORDS INTO test.varcharsize4 FROM  '/root/test_varchar_chinois'
USING DELIMITERS ',','\n','\"' NULL AS   '\\N' ;

Result :
Failed to import table line 1 field 2 'varchar(4)' expected in '不要让早把'

------

COPY 10 RECORDS INTO test.varcharsize5 FROM  '/root/test_varchar_chinois'
USING DELIMITERS ',','\n','\"' NULL AS   '\\N' ;

Result :
Failed to import table line 1 field 2 'varchar(5)' expected in '不要让早把'

-------

COPY 10 RECORDS INTO test.varcharsize89 FROM  '/root/test_varchar_chinois'
USING DELIMITERS ',','\n','\"' NULL AS   '\\N' ;

Result :
Failed to import table line 1 field 2 'varchar(9)' expected in '不要让早把'

-------

COPY 10 RECORDS INTO test.varcharsize10 FROM  '/root/test_varchar_chinois'
USING DELIMITERS ',','\n','\"' NULL AS   '\\N' ;
OK

Regards

Mathieu

Mathieu Raillard

Sjoerd Mullender

tags

participants (2)