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 :
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