stringtypes.sql

derby database source code.good for you.

-- insert-select from long varchar columns into char columns with trunc. errors
insert into ct select * from lvt;
select * from ct;

-- insert-select from long varchar columns into char columns without trunc errs
insert into ct select * from lvt where cast(substr(c1,1,30) as varchar(30)) = '1' or cast(substr(c1,1,30) as varchar(30)) = '2';
select * from ct;

-- insert-select from long varchar columns into varchar columns with trunc. errs
insert into vt select * from lvt;
select * from vt;

-- insert-select from long varchar columns into varchar cols without trunc errs
insert into vt select * from lvt where cast(substr(c1,1,30) as varchar(30)) = '1' or cast(substr(c1,1,30) as varchar(30)) = '2';
select * from vt;

-- Now try insert-select with type conversion where column lengths don't match
-- but there are no truncation errors.  Need new tables for this.
create table ct2 (c1 char(5), c2 char(10));
insert into ct2 values ('111', '111');

create table vt2 (c1 varchar(5), c2 varchar(10));
insert into vt2 values ('222', '222');

create table lvt2 (c1 long varchar, c2 long varchar);
insert into lvt2 values ('333', '333');

insert into ct2 select * from vt2;
insert into ct2 select * from lvt2;
select * from ct2;

insert into vt2 select * from ct2;
insert into vt2 select * from lvt2;
select * from vt2;

insert into lvt2 select * from ct2;
insert into lvt2 select * from vt2;
select * from lvt2;

-- Now try string constants that contain the ' character