lob.sql

derby database source code.good for you.

-- This test lang/LOB.sql still includes tests for 
-- DB2 UDB incompatible datatype NCLOB.
-- Still waiting for DB2 UDB compatible functionality for NCLOB to be implemented

-- Also note that in DB2 UDB, to create BLOB and CLOB strings greater than 1 gigabyte,
-- the NOT LOGGED option must be specified (SQLSTATE 42993).


-- test that BLOB/CLOB are not reserved words
create table blob(a int);
insert into blob values(3);
select blob.a from blob;

create table clob(a int);
insert into clob values(3);
select clob.a from clob;

create table nclob(a int);
insert into nclob values(3);
select nclob.a from nclob;

create table a(blob int, clob int, nclob int);
insert into a values(1,2,3);
insert into a(blob, clob, nclob) values(1,2,3);
select a.blob, a.clob, a.nclob from a;
select a.blob, a.clob, a.nclob from a where a.blob = 1;
select a.blob, a.clob, a.nclob from a where a.clob = 2;
select a.blob, a.clob, a.nclob from a where a.nclob = 3;
select a.blob, a.clob, a.nclob from a where a.blob = 1 and a.clob = 2 and a.nclob = 3;

create table b(blob blob(3K), clob clob(2M));
insert into b values(cast(X'0031' as blob(3K)),cast('2' as clob(2M)));
insert into b(blob, clob, nclob) values(cast(X'0031' as blob(3K)),cast('2' as clob(2M)));
select b.blob, b.clob, b.nclob from b;

-- equal tests are not allowed
select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(5));
select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(7));
select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(7));
select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(7));
select 1 from b where cast(X'e0' as blob(5))=cast(X'e000' as blob(7));

select 1 from b where X'80' = cast(X'80' as blob(1));
select 1 from b where cast(X'80' as blob(1)) = X'80';
select 1 from b where cast(X'80' as blob(1)) = cast(X'80' as blob(1));

select 1 from b where '1' = cast('1' as clob(1));
select 1 from b where cast('1' as clob(1)) = '1';
select 1 from b where cast('1' as clob(1)) = cast('1' as clob(1));

select 1 from b where '1' = cast('1' as nclob(1));
select 1 from b where cast('1' as nclob(1)) = '1';
select 1 from b where cast('1' as nclob(1)) = cast('1' as nclob(1));

-- NCLOB is comparable with CLOB

select 1 from b where cast('1' as nclob(10)) = cast('1' as clob(10));
select 1 from b where cast('1' as clob(10)) = cast('1' as nclob(10));

-- comparsion using tables
select * from b as b1, b as b2 where b1.blob=b2.blob;
select * from b as b1, b as b2 where b1.blob!=b2.blob;

select * from b as b1, b as b2 where b1.blob=X'20';
select * from b as b1, b as b2 where X'20'=b1.blob;
select * from b as b1, b as b2 where X'20'!=b1.blob;

select * from b as b1, b as b2 where b1.blob=X'7575';
select * from b as b1, b as b2 where X'7575'=b1.blob;

select b.blob, b.clob, b.nclob from b where b.blob = '1' and b.clob = '2' and b.nclob = '3';
select b.blob from b where b.blob = '1';
-- however it works for types which cloudscape autocasts to char
select b.clob from b where b.clob = '2';
select b.nclob from b where b.nclob = '3';

-- test insert of NULL
insert into b values(null, null, null);
select * from b;

-- cleanup
drop table blob;
drop table clob;
drop table nclob;
drop table a;
drop table b;

-- test insert limitations
create table b(b blob(5));
create table c(c clob(5));
create table n(n nclob(5));

insert into b values(cast(X'01020304' as blob(10)));
insert into b values(cast(X'0102030405' as blob(10)));
insert into b values(cast(X'010203040506' as blob(10)));

-- truncate before insert, no errors
insert into b values(cast(X'01020304' as blob(5)));
insert into b values(cast(X'0102030405' as blob(5)));
insert into b values(cast(X'010203040506' as blob(5)));

-- clob/nclob
--   ok in spite of not being cast
insert into c values('1234');
insert into c values('12345');
insert into c values('123456');

insert into n values('1234');
insert into n values('12345');
insert into n values('123456');

--   ok
insert into c values(cast('1234' as clob(5)));
insert into c values(cast('12345' as clob(5)));
insert into c values(cast('123456' as clob(5)));

insert into n values(cast('1234' as nclob(5)));
insert into n values(cast('12345' as nclob(5)));
insert into n values(cast('123456' as nclob(5)));

select * from b;
select * from c;
select * from n;

-- concatenate
values cast('12' as clob(2)) || cast('34' as clob(2));
values cast('12' as nclob(2)) || cast('34' as nclob(2));
select 1 from b where cast('12' as clob(2)) || cast('34' as clob(2)) = '1234';
select 1 from b where cast('12' as nclob(2)) || cast('34' as nclob(2)) = '1234';
select 1 from b where cast('12' as clob(2)) || cast('34' as clob(2)) = cast('1234' as clob(4));
select 1 from b where cast('12' as nclob(2)) || cast('34' as nclob(2)) = cast('1234' as clob(4));

-- like
select * from b where b like '0102%';
select * from c where c like '12%';
select * from n where n like '12%';

select * from b where b like cast('0102%' as blob(10));
select * from c where c like cast('12%' as clob(10));
select * from n where n like cast('12%' as nclob(10));

-- cleanup
drop table b;
drop table c;
drop table n;

-- test syntax of using long type names
create table a(a binary large object(3K));
create table b(a character large object(3K));
create table c(a national character large object(3K));
create table d(a char large object(204K));

-- create index (not allowed)
create index ia on a(a);
create index ib on b(a);
create index ic on c(a);
create index id on d(a);

-- cleanup
drop table a;
drop table c;
drop table d;

-- ORDER tests on LOB types (not allowed)
select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(5));
select 1 from b where cast(X'e0' as blob(5))!=cast(X'e0' as blob(5));
select 1 from b where cast(X'e0' as blob(5))<cast(X'e0' as blob(5));
select 1 from b where cast(X'e0' as blob(5))>cast(X'e0' as blob(7));
select 1 from b where cast(X'e0' as blob(5))<=cast(X'e0' as blob(7));
select 1 from b where cast(X'e0' as blob(5))>=cast(X'e0' as blob(7));

select 1 from b where cast('fish' as clob(5))=cast('fish' as clob(5));
select 1 from b where cast('fish' as clob(5))!=cast('fish' as clob(5));
select 1 from b where cast('fish' as clob(5))<cast('fish' as clob(5));
select 1 from b where cast('fish' as clob(5))>cast('fish' as clob(7));
select 1 from b where cast('fish' as clob(5))<=cast('fish' as clob(7));
select 1 from b where cast('fish' as clob(5))>=cast('fish' as clob(7));

select 1 from b where cast('fish' as nclob(5))=cast('fish' as nclob(5));
select 1 from b where cast('fish' as nclob(5))!=cast('fish' as nclob(5));
select 1 from b where cast('fish' as nclob(5))<cast('fish' as nclob(5));
select 1 from b where cast('fish' as nclob(5))>cast('fish' as nclob(7));
select 1 from b where cast('fish' as nclob(5))<=cast('fish' as nclob(7));
select 1 from b where cast('fish' as nclob(5))>=cast('fish' as nclob(7));

-- test operands on autocast
-- beetle 5282
-- <,> <=, >= operands are not supported in db2 but supported in cloudscape
-- compare w. integer/char types are also not ok

-- CLOB testing
CREATE TABLE testoperatorclob (colone clob(1K));
INSERT INTO testoperatorclob VALUES (CAST('50' AS CLOB(1K)));
INSERT INTO testoperatorclob VALUES (CAST(cast('50' as varchar(80)) AS CLOB(1K)));
select * from testoperatorclob;

-- these select statements should raise an error but are successful in cloudscape
select * from testoperatorclob where colone > 10;
select * from testoperatorclob where colone > 5;
select * from testoperatorclob where colone < 70;
select * from testoperatorclob where colone = 50;
select * from testoperatorclob where colone != 10;
select * from testoperatorclob where colone <= 70;
select * from testoperatorclob where colone >= 10;
select * from testoperatorclob where colone <> 10;

select * from testoperatorclob where colone > '10';
select * from testoperatorclob where colone > '5';
select * from testoperatorclob where colone < '70';
select * from testoperatorclob where colone = '50';
select * from testoperatorclob where colone != '10';
select * from testoperatorclob where colone <= '70';
select * from testoperatorclob where colone >= '10';
select * from testoperatorclob where colone <> '10';

drop table testoperatorclob;

-- BLOB testing
CREATE TABLE testoperatorblob (colone blob(1K));
INSERT INTO testoperatorblob VALUES (CAST('50' AS BLOB(1K)));
INSERT INTO testoperatorblob VALUES (CAST(cast('50' as varchar(80)) AS BLOB(1K)));
select * from testoperatorblob;

-- these select statements should raise an error but are successful in cloudscape
select * from testoperatorblob where colone > 10;
select * from testoperatorblob where colone > 5;
select * from testoperatorblob where colone < 999999;
select * from testoperatorblob where colone = 00350030;
select * from testoperatorblob where colone != 10;
select * from testoperatorblob where colone <= 999999;
select * from testoperatorblob where colone >= 10;
select * from testoperatorblob where colone <> 10;

select * from testoperatorblob where colone > '10';
select * from testoperatorblob where colone > '5';
select * from testoperatorblob where colone < '70';
select * from testoperatorblob where colone = '50';
select * from testoperatorblob where colone != '10';
select * from testoperatorblob where colone <= '70';
select * from testoperatorblob where colone >= '10';
select * from testoperatorblob where colone <> '10';

drop table testoperatorblob;

-- NCLOB testing
CREATE TABLE testoperatornclob (colone nclob(1K));
INSERT INTO testoperatornclob VALUES (CAST('50' AS NCLOB(1K)));
INSERT INTO testoperatornclob VALUES (CAST(cast('50' as varchar(80)) AS NCLOB(1K)));
select * from testoperatornclob;

-- these select statements should raise an error but are successful in cloudscape
select * from testoperatornclob where colone > 10;
select * from testoperatornclob where colone > 5;
select * from testoperatornclob where colone < 70;
select * from testoperatornclob where colone = 50;
select * from testoperatornclob where colone != 10;
select * from testoperatornclob where colone <= 70;
select * from testoperatornclob where colone >= 10;
select * from testoperatornclob where colone <> 10;

select * from testoperatornclob where colone > '10';
select * from testoperatornclob where colone > '5';
select * from testoperatornclob where colone < '70';