stringtypes.out

derby database source code.good for you.

ij> --
-- this test shows the current supported char, varchar, and long varchar
-- functionality
--
-- create a table with null and non-null char columns of different lengths
create table ct (c1 char, c2 char(5) not null, c3 char(30) default null);
0 rows inserted/updated/deleted
ij> -- insert some rows
-- first, try values that fill each column with non-blanks
insert into ct values ('1', '11111', '111111111111111111111111111111');
1 row inserted/updated/deleted
ij> -- now try some values that are shorter than the columns
insert into ct values ('', '22', '222');
1 row inserted/updated/deleted
ij> -- now try some values that are longer than the columns, where the excess
-- characters are blanks
insert into ct values ('3         ', '33333      ', '333333333333333333333333333333          ');
1 row inserted/updated/deleted
ij> -- now try some values that are longer than the columns, where the excess
-- characters are non-blanks.  These should get errors
insert into ct values ('44', '4', '4');
ERROR 22001: A truncation error was encountered trying to shrink CHAR '44' to length 1.
ij> insert into ct values ('5', '555555', '5');
ERROR 22001: A truncation error was encountered trying to shrink CHAR '555555' to length 5.
ij> insert into ct values ('6', '66666', '6666666666666666666666666666666');
ERROR 22001: A truncation error was encountered trying to shrink CHAR '6666666666666666666666666666666' to length 30.
ij> -- now try inserting some nulls, first in columns that accept them
insert into ct values (null, '77777', null);
1 row inserted/updated/deleted
ij> -- now try inserting nulls into columns that don't accept them
insert into ct values ('8', null, '8');
ERROR 23502: Column 'C2'  cannot accept a NULL value.
ij> -- now check the rows that made it into the table successfully
select * from ct;
C1  |C2   |C3                            
-----------------------------------------
1   |11111|111111111111111111111111111111
    |22   |222                           
3   |33333|333333333333333333333333333333
NULL|77777|NULL                          
ij> -- now try the char_length function on the columns
select {fn length(c1)}, {fn length(c2)}, {fn length(c3)} from ct;
1          |2          |3          
-----------------------------------
1          |5          |30         
0          |2          |3          
1          |5          |30         
NULL       |5          |NULL       
ij> -- now create a table with varchar columns
create table vt (c1 varchar(1), c2 varchar(5) not null, c3 varchar(30) default null);
0 rows inserted/updated/deleted
ij> -- insert some rows
-- first, try values that fill each column with non-blanks
insert into vt values ('1', '11111', '111111111111111111111111111111');
1 row inserted/updated/deleted
ij> -- now try some values that are shorter than the columns
insert into vt values ('', '22', '222');
1 row inserted/updated/deleted
ij> -- now try some values that are longer than the columns, where the excess
-- characters are blanks
insert into vt values ('3         ', '33333      ', '333333333333333333333333333333          ');
1 row inserted/updated/deleted
ij> -- now try some values that are longer than the columns, where the excess
-- characters are non-blanks.  These should get errors
insert into vt values ('44', '4', '4');
ERROR 22001: A truncation error was encountered trying to shrink VARCHAR '44' to length 1.
ij> insert into vt values ('5', '555555', '5');
ERROR 22001: A truncation error was encountered trying to shrink VARCHAR '555555' to length 5.
ij> insert into vt values ('6', '66666', '6666666666666666666666666666666');
ERROR 22001: A truncation error was encountered trying to shrink VARCHAR '6666666666666666666666666666666' to length 30.
ij> -- now try inserting some nulls, first in columns that accept them
insert into vt values (null, '77777', null);
1 row inserted/updated/deleted
ij> -- now try inserting nulls into columns that don't accept them
insert into vt values ('8', null, '8');
ERROR 23502: Column 'C2'  cannot accept a NULL value.
ij> -- now check the rows that made it into the table successfully
select * from vt;
C1  |C2   |C3                            
-----------------------------------------
1   |11111|111111111111111111111111111111
    |22   |222                           
3   |33333|333333333333333333333333333333
NULL|77777|NULL