lob.out

derby database source code.good for you.

ij> -- 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);
0 rows inserted/updated/deleted
ij> insert into blob values(3);
1 row inserted/updated/deleted
ij> select blob.a from blob;
A          
-----------
3          
ij> create table clob(a int);
0 rows inserted/updated/deleted
ij> insert into clob values(3);
1 row inserted/updated/deleted
ij> select clob.a from clob;
A          
-----------
3          
ij> create table nclob(a int);
0 rows inserted/updated/deleted
ij> insert into nclob values(3);
1 row inserted/updated/deleted
ij> select nclob.a from nclob;
A          
-----------
3          
ij> create table a(blob int, clob int, nclob int);
0 rows inserted/updated/deleted
ij> insert into a values(1,2,3);
1 row inserted/updated/deleted
ij> insert into a(blob, clob, nclob) values(1,2,3);
1 row inserted/updated/deleted
ij> select a.blob, a.clob, a.nclob from a;
BLOB       |CLOB       |NCLOB      
-----------------------------------
1          |2          |3          
1          |2          |3          
ij> select a.blob, a.clob, a.nclob from a where a.blob = 1;
BLOB       |CLOB       |NCLOB      
-----------------------------------
1          |2          |3          
1          |2          |3          
ij> select a.blob, a.clob, a.nclob from a where a.clob = 2;
BLOB       |CLOB       |NCLOB      
-----------------------------------
1          |2          |3          
1          |2          |3          
ij> select a.blob, a.clob, a.nclob from a where a.nclob = 3;
BLOB       |CLOB       |NCLOB      
-----------------------------------
1          |2          |3          
1          |2          |3          
ij> select a.blob, a.clob, a.nclob from a where a.blob = 1 and a.clob = 2 and a.nclob = 3;
BLOB       |CLOB       |NCLOB      
-----------------------------------
1          |2          |3          
1          |2          |3          
ij> create table b(blob blob(3K), clob clob(2M));
0 rows inserted/updated/deleted
ij> insert into b values(cast(X'0031' as blob(3K)),cast('2' as clob(2M)));
1 row inserted/updated/deleted
ij> insert into b(blob, clob, nclob) values(cast(X'0031' as blob(3K)),cast('2' as clob(2M)));
ERROR 42X14: 'NCLOB' is not a column in table or VTI 'APP.B'.
ij> select b.blob, b.clob, b.nclob from b;
ERROR 42X04: Column 'B.NCLOB' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'B.NCLOB' is not a column in the target table.
ij> -- equal tests are not allowed
select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(5));
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported.
ij> select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(7));
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported.
ij> select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(7));
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported.
ij> select 1 from b where cast(X'e0' as blob(5))=cast(X'e0' as blob(7));
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported.
ij> select 1 from b where cast(X'e0' as blob(5))=cast(X'e000' as blob(7));
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported.
ij> select 1 from b where X'80' = cast(X'80' as blob(1));
ERROR 42818: Comparisons between 'CHAR () FOR BIT DATA' and 'BLOB' are not supported.
ij> select 1 from b where cast(X'80' as blob(1)) = X'80';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR () FOR BIT DATA' are not supported.
ij> select 1 from b where cast(X'80' as blob(1)) = cast(X'80' as blob(1));
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported.
ij> select 1 from b where '1' = cast('1' as clob(1));
ERROR 42818: Comparisons between 'CHAR' and 'CLOB' are not supported.
ij> select 1 from b where cast('1' as clob(1)) = '1';
ERROR 42818: Comparisons between 'CLOB' and 'CHAR' are not supported.
ij> select 1 from b where cast('1' as clob(1)) = cast('1' as clob(1));
ERROR 42818: Comparisons between 'CLOB' and 'CLOB' are not supported.
ij> select 1 from b where '1' = cast('1' as nclob(1));
ERROR 0A000: Feature not implemented: NCLOB.
ij> select 1 from b where cast('1' as nclob(1)) = '1';
ERROR 0A000: Feature not implemented: NCLOB.
ij> select 1 from b where cast('1' as nclob(1)) = cast('1' as nclob(1));
ERROR 0A000: Feature not implemented: NCLOB.
ij> -- NCLOB is comparable with CLOB
select 1 from b where cast('1' as nclob(10)) = cast('1' as clob(10));
ERROR 0A000: Feature not implemented: NCLOB.
ij> select 1 from b where cast('1' as clob(10)) = cast('1' as nclob(10));
ERROR 0A000: Feature not implemented: NCLOB.
ij> -- comparsion using tables
select * from b as b1, b as b2 where b1.blob=b2.blob;
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported.
ij> select * from b as b1, b as b2 where b1.blob!=b2.blob;
ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported.
ij> select * from b as b1, b as b2 where b1.blob=X'20';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR () FOR BIT DATA' are not supported.
ij> select * from b as b1, b as b2 where X'20'=b1.blob;
ERROR 42818: Comparisons between 'CHAR () FOR BIT DATA' and 'BLOB' are not supported.
ij> select * from b as b1, b as b2 where X'20'!=b1.blob;
ERROR 42818: Comparisons between 'CHAR () FOR BIT DATA' and 'BLOB' are not supported.
ij> select * from b as b1, b as b2 where b1.blob=X'7575';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR () FOR BIT DATA' are not supported.
ij> select * from b as b1, b as b2 where X'7575'=b1.blob;
ERROR 42818: Comparisons between 'CHAR () FOR BIT DATA' and 'BLOB' are not supported.
ij> select b.blob, b.clob, b.nclob from b where b.blob = '1' and b.clob = '2' and b.nclob = '3';
ERROR 42X04: Column 'B.NCLOB' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'B.NCLOB' is not a column in the target table.
ij> select b.blob from b where b.blob = '1';
ERROR 42818: Comparisons between 'BLOB' and 'CHAR' are not supported.
ij> -- however it works for types which cloudscape autocasts to char
select b.clob from b where b.clob = '2';
ERROR 42818: Comparisons between 'CLOB' and 'CHAR' are not supported.
ij> select b.nclob from b where b.nclob = '3';
ERROR 42X04: Column 'B.NCLOB' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'B.NCLOB' is not a column in the target table.
ij> -- test insert of NULL
insert into b values(null, null, null);
ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns.
ij> select * from b;
BLOB                                                                                                                            |CLOB                                                                                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0031                                                                                                                            |2                                                                                                                               
ij> -- cleanup
drop table blob;
0 rows inserted/updated/deleted
ij> drop table clob;
0 rows inserted/updated/deleted
ij> drop table nclob;
0 rows inserted/updated/deleted
ij> drop table a;
0 rows inserted/updated/deleted
ij> drop table b;
0 rows inserted/updated/deleted
ij> -- test insert limitations
create table b(b blob(5));
0 rows inserted/updated/deleted
ij> create table c(c clob(5));
0 rows inserted/updated/deleted
ij> create table n(n nclob(5));
ERROR 0A000: Feature not implemented: NCLOB.
ij> insert into b values(cast(X'01020304' as blob(10)));
1 row inserted/updated/deleted
ij> insert into b values(cast(X'0102030405' as blob(10)));
1 row inserted/updated/deleted
ij> insert into b values(cast(X'010203040506' as blob(10)));
ERROR 22001: A truncation error was encountered trying to shrink BLOB 'XXXX' to length 5.
ij> -- truncate before insert, no errors
insert into b values(cast(X'01020304' as blob(5)));
1 row inserted/updated/deleted
ij> insert into b values(cast(X'0102030405' as blob(5)));
1 row inserted/updated/deleted
ij> insert into b values(cast(X'010203040506' as blob(5)));
1 row inserted/updated/deleted
ij> -- clob/nclob
--   ok in spite of not being cast
insert into c values('1234');
1 row inserted/updated/deleted
ij> insert into c values('12345');
1 row inserted/updated/deleted
ij> insert into c values('123456');
ERROR 22001: A truncation error was encountered trying to shrink CLOB '123456' to length 5.
ij> insert into n values('1234');
ERROR 42X05: Table 'N' does not exist.
ij> insert into n values('12345');
ERROR 42X05: Table 'N' does not exist.
ij> insert into n values('123456');
ERROR 42X05: Table 'N' does not exist.
ij> --   ok
insert into c values(cast('1234' as clob(5)));
1 row inserted/updated/deleted
ij> insert into c values(cast('12345' as clob(5)));
1 row inserted/updated/deleted
ij> insert into c values(cast('123456' as clob(5)));
1 row inserted/updated/deleted
ij> insert into n values(cast('1234' as nclob(5)));
ERROR 0A000: Feature not implemented: NCLOB.
ij> insert into n values(cast('12345' as nclob(5)));
ERROR 0A000: Feature not implemented: NCLOB.
ij> insert into n values(cast('123456' as nclob(5)));
ERROR 0A000: Feature not implemented: NCLOB.
ij> select * from b;
B         
----------
01020304  
0102030405
01020304  
0102030405
0102030405
ij> select * from c;
C    
-----
1234 
12345
1234 
12345
1234&
ij> select * from n;
ERROR 42X05: Table 'N' does not exist.
ij> -- concatenate
values cast('12' as clob(2)) || cast('34' as clob(2));
1   
----
1234
ij> values cast('12' as nclob(2)) || cast('34' as nclob(2));
ERROR 0A000: Feature not implemented: NCLOB.
ij> select 1 from b where cast('12' as clob(2)) || cast('34' as clob(2)) = '1234';
ERROR 42818: Comparisons between 'CLOB' and 'CHAR' are not supported.
ij> select 1 from b where cast('12' as nclob(2)) || cast('34' as nclob(2)) = '1234';
ERROR 0A000: Feature not implemented: NCLOB.
ij> select 1 from b where cast('12' as clob(2)) || cast('34' as clob(2)) = cast('1234' as clob(4));
ERROR 42818: Comparisons between 'CLOB' and 'CLOB' are not supported.
ij> select 1 from b where cast('12' as nclob(2)) || cast('34' as nclob(2)) = cast('1234' as clob(4));
ERROR 0A000: Feature not implemented: NCLOB.
ij> -- like
select * from b where b like '0102%';
ERROR 42884: No authorized routine named 'LIKE' of type 'FUNCTION' having compatible arguments was found.
ij> select * from c where c like '12%';
C    
-----
1234 
12345
1234 
12345
1234&
ij> select * from n where n like '12%';
ERROR 42X05: Table 'N' does not exist.
ij> select * from b where b like cast('0102%' as blob(10));
ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'.
ij> select * from c where c like cast('12%' as clob(10));
C    
-----
1234 
12345
1234 
12345
1234&
ij> select * from n where n like cast('12%' as nclob(10));
ERROR 0A000: Feature not implemented: NCLOB.
ij> -- cleanup
drop table b;
0 rows inserted/updated/deleted
ij> drop table c;
0 rows inserted/updated/deleted
ij> drop table n;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'N' because it does not exist.
ij> -- test syntax of using long type names
create table a(a binary large object(3K));
0 rows inserted/updated/deleted
ij> create table b(a character large object(3K));
0 rows inserted/updated/deleted
ij> create table c(a national character large object(3K));
ERROR 0A000: Feature not implemented: NCLOB.
ij> create table d(a char large object(204K));
0 rows inserted/updated/deleted
ij> -- create index (not allowed)
create index ia on a(a);
ERROR X0X67: Columns of type 'BLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> create index ib on b(a);
ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> create index ic on c(a);
ERROR 42Y55: 'CREATE INDEX' cannot be performed on 'C' because it does not exist.
ij> create index id on d(a);
ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> -- cleanup
drop table a;
0 rows inserted/updated/deleted