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 not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it 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 not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it 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 not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it 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, or DISTINCT, 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, or DISTINCT, 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, or DISTINCT, because comparisons are not supported for that type. ij> -- cleanup drop table a; 0 rows inserted/updated/deleted ij> drop table c; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'C' because it does not exist. ij> drop table d; 0 rows inserted/updated/deleted ij> -- ORDER tests on LOB types (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(5)); ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. ij> select 1 from b where cast(X'e0' as blob(5)) 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('fish' as clob(5))=cast('fish' as clob(5)); ERROR 42818: Comparisons between 'CLOB' and 'CLOB' are not supported. ij> select 1 from b where cast('fish' as clob(5))!=cast('fish' as clob(5)); ERROR 42818: Comparisons between 'CLOB' and 'CLOB' are not supported. ij> select 1 from b where cast('fish' as clob(5)) select 1 from b where cast('fish' as clob(5))>cast('fish' as clob(7)); ERROR 42818: Comparisons between 'CLOB' and 'CLOB' are not supported. ij> select 1 from b where cast('fish' as clob(5))<=cast('fish' as clob(7)); ERROR 42818: Comparisons between 'CLOB' and 'CLOB' are not supported. ij> select 1 from b where cast('fish' as clob(5))>=cast('fish' as clob(7)); ERROR 42818: Comparisons between 'CLOB' and 'CLOB' are not supported. ij> select 1 from b where cast('fish' as nclob(5))=cast('fish' as nclob(5)); ERROR 0A000: Feature not implemented: NCLOB. ij> select 1 from b where cast('fish' as nclob(5))!=cast('fish' as nclob(5)); ERROR 0A000: Feature not implemented: NCLOB. ij> select 1 from b where cast('fish' as nclob(5)) select 1 from b where cast('fish' as nclob(5))>cast('fish' as nclob(7)); ERROR 0A000: Feature not implemented: NCLOB. ij> select 1 from b where cast('fish' as nclob(5))<=cast('fish' as nclob(7)); ERROR 0A000: Feature not implemented: NCLOB. ij> select 1 from b where cast('fish' as nclob(5))>=cast('fish' as nclob(7)); ERROR 0A000: Feature not implemented: NCLOB. ij> -- 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)); 0 rows inserted/updated/deleted ij> INSERT INTO testoperatorclob VALUES (CAST('50' AS CLOB(1K))); 1 row inserted/updated/deleted ij> INSERT INTO testoperatorclob VALUES (CAST(cast('50' as varchar(80)) AS CLOB(1K))); 1 row inserted/updated/deleted ij> select * from testoperatorclob; COLONE -------------------------------------------------------------------------------------------------------------------------------- 50 50 ij> -- these select statements should raise an error but are successful in cloudscape select * from testoperatorclob where colone > 10; ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported. ij> select * from testoperatorclob where colone > 5; ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported. ij> select * from testoperatorclob where colone < 70; ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported. ij> select * from testoperatorclob where colone = 50; ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported. ij> select * from testoperatorclob where colone != 10; ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported. ij> select * from testoperatorclob where colone <= 70; ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported. ij> select * from testoperatorclob where colone >= 10; ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported. ij> select * from testoperatorclob where colone <> 10; ERROR 42818: Comparisons between 'CLOB' and 'INTEGER' are not supported. ij> select * from testoperatorclob where colone > '10'; ERROR 42818: Comparisons between 'CLOB' and 'CHAR' are not supported. ij> select * from testoperatorclob where colone > '5'; ERROR 42818: Comparisons between 'CLOB' and 'CHAR' are not supported. ij> select * from testoperatorclob where colone < '70'; ERROR 42818: Comparisons between 'CLOB' and 'CHAR' are not supported. ij> select * from testoperatorclob where colone = '50'; ERROR 42818: Comparisons between 'CLOB' and 'CHAR' are not supported. ij> select * from testoperatorclob where colone != '10'; ERROR 42818: Comparisons between 'CLOB' and 'CHAR' are not supported. ij> select * from testoperatorclob where colone <= '70'; ERROR 42818: Comparisons between 'CLOB' and 'CHAR' are not supported. ij> select * from testoperatorclob where colone >= '10'; ERROR 42818: Comparisons between 'CLOB' and 'CHAR' are not supported. ij> select * from testoperatorclob where colone <> '10'; ERROR 42818: Comparisons between 'CLOB' and 'CHAR' are not supported. ij> drop table testoperatorclob; 0 rows inserted/updated/deleted ij> -- BLOB testing CREATE TABLE testoperatorblob (colone blob(1K)); 0 rows inserted/updated/deleted ij> INSERT INTO testoperatorblob VALUES (CAST('50' AS BLOB(1K))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> INSERT INTO testoperatorblob VALUES (CAST(cast('50' as varchar(80)) AS BLOB(1K))); ERROR 42846: Cannot convert types 'VARCHAR' to 'BLOB'. ij> select * from testoperatorblob; COLONE -------------------------------------------------------------------------------------------------------------------------------- ij> -- these select statements should raise an error but are successful in cloudscape select * from testoperatorblob where colone > 10; ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. ij> select * from testoperatorblob where colone > 5; ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. ij> select * from testoperatorblob where colone < 999999; ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. ij> select * from testoperatorblob where colone = 00350030; ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. ij> select * from testoperatorblob where colone != 10; ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. ij> select * from testoperatorblob where colone <= 999999; ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. ij> select * from testoperatorblob where colone >= 10; ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. ij> select * from testoperatorblob where colone <> 10; ERROR 42818: Comparisons between 'BLOB' and 'INTEGER' are not supported. ij> select * from testoperatorblob where colone > '10'; ERROR 42818: Comparisons between 'BLOB' and 'CHAR' are not supported. ij> select * from testoperatorblob where colone > '5'; ERROR 42818: Comparisons between 'BLOB' and 'CHAR' are not supported. ij> select * from testoperatorblob where colone < '70'; ERROR 42818: Comparisons between 'BLOB' and 'CHAR' are not supported. ij> select * from testoperatorblob where colone = '50'; ERROR 42818: Comparisons between 'BLOB' and 'CHAR' are not supported. ij> select * from testoperatorblob where colone != '10'; ERROR 42818: Comparisons between 'BLOB' and 'CHAR' are not supported. ij> select * from testoperatorblob where colone <= '70'; ERROR 42818: Comparisons between 'BLOB' and 'CHAR' are not supported. ij> select * from testoperatorblob where colone >= '10'; ERROR 42818: Comparisons between 'BLOB' and 'CHAR' are not supported. ij> select * from testoperatorblob where colone <> '10'; ERROR 42818: Comparisons between 'BLOB' and 'CHAR' are not supported. ij> drop table testoperatorblob; 0 rows inserted/updated/deleted ij> -- NCLOB testing CREATE TABLE testoperatornclob (colone nclob(1K)); ERROR 0A000: Feature not implemented: NCLOB. ij> INSERT INTO testoperatornclob VALUES (CAST('50' AS NCLOB(1K))); ERROR 0A000: Feature not implemented: NCLOB. ij> INSERT INTO testoperatornclob VALUES (CAST(cast('50' as varchar(80)) AS NCLOB(1K))); ERROR 0A000: Feature not implemented: NCLOB. ij> select * from testoperatornclob; ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist. ij> -- these select statements should raise an error but are successful in cloudscape select * from testoperatornclob where colone > 10; ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone > 5; ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone < 70; ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone = 50; ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone != 10; ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone <= 70; ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone >= 10; ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone <> 10; ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone > '10'; ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone > '5'; ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone < '70'; ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone = '50'; ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone != '10'; ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone <= '70'; ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone >= '10'; ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist. ij> select * from testoperatornclob where colone <> '10'; ERROR 42X05: Table 'TESTOPERATORNCLOB' does not exist. ij> drop table testoperatornclob; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TESTOPERATORNCLOB' because it does not exist. ij> ----- test method invocations on LOB objects (should disallow) -- setup drop table b; 0 rows inserted/updated/deleted ij> create table b(b blob(77)); 0 rows inserted/updated/deleted ij> insert into b values(cast('33' as blob(77))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> create table c(c clob(77)); 0 rows inserted/updated/deleted ij> insert into c values(cast('33' as clob(77))); 1 row inserted/updated/deleted ij> -- LOB as main object for method invocation not allowed values (cast('1' as blob(1M)))->toString(); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> values (cast('1' as clob(1M)))->toString(); ERROR XJ082: BLOB/CLOB values are not allowed as method parameters or receiver. ij> values (cast('1' as nclob(1M)))->toString(); ERROR 0A000: Feature not implemented: NCLOB. ij> -- LOB column as parameter not allowed select b->equals('3') from b; ERROR XJ082: BLOB/CLOB values are not allowed as method parameters or receiver. ij> select c->equals('3') from c; ERROR XJ082: BLOB/CLOB values are not allowed as method parameters or receiver. ij> -- explicit LOB as parameter not allowed values '3'->equals(cast('3' as blob(7))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> values '3'->equals(cast('3' as clob(7))); ERROR XJ082: BLOB/CLOB values are not allowed as method parameters or receiver. ij> -- LOB column as parameter not allowed select '3'->equals(b) from b; ERROR XJ082: BLOB/CLOB values are not allowed as method parameters or receiver. ij> select '3'->equals(c) from c; ERROR XJ082: BLOB/CLOB values are not allowed as method parameters or receiver. ij> drop table b; 0 rows inserted/updated/deleted ij> drop table c; 0 rows inserted/updated/deleted ij> ------ TEST length functions on LOBs ---- BLOB values length(cast('foo' as blob(10))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> values {fn length(cast('foo' as blob(10)))}; ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> ---- CHAR values length(cast('foo' as char(10))); 1 ----------- 10 ij> values {fn length(cast('foo' as char(10)))}; 1 ----------- 3 ij> ---- CLOB values length(cast('foo' as clob(10))); 1 ----------- 3 ij> values {fn length(cast('foo' as clob(10)))}; 1 ----------- 3 ij> ---- NCLOB values length(cast('foo' as nclob(10))); ERROR 0A000: Feature not implemented: NCLOB. ij> values {fn length(cast('foo' as nclob(10)))}; ERROR 0A000: Feature not implemented: NCLOB. ij> -- Longvarchar negative tests create table testPredicate1 (c1 long varchar); 0 rows inserted/updated/deleted ij> create table testPredicate2 (c1 long varchar); 0 rows inserted/updated/deleted ij> insert into testPredicate1 (c1) values 'a'; 1 row inserted/updated/deleted ij> insert into testPredicate2 (c1) values 'a'; 1 row inserted/updated/deleted ij> -- UNION select * from testPredicate1 union select * from testPredicate2; ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported for that type. ij> -- IN predicate select c1 from testPredicate1 where c1 IN (select c1 from testPredicate2); ERROR 42818: Comparisons between 'LONG VARCHAR' and 'LONG VARCHAR' are not supported. ij> -- NOT IN predicate select c1 from testPredicate1 where c1 NOT IN (select c1 from testPredicate2); ERROR 42818: Comparisons between 'LONG VARCHAR' and 'LONG VARCHAR' are not supported. ij> -- ORDER BY clause select * from testPredicate1 order by c1; ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported for that type. ij> -- GROUP BY clause select substr(c1,1,2) from testPredicate1 group by c1; ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported for that type. ij> -- JOIN select * from testPredicate1 t1, testPredicate2 t2 where t1.c1=t2.c1; ERROR 42818: Comparisons between 'LONG VARCHAR' and 'LONG VARCHAR' are not supported. ij> select * from testPredicate1 LEFT OUTER JOIN testPredicate2 on testPredicate1.c1=testPredicate2.c1; ERROR 42818: Comparisons between 'LONG VARCHAR' and 'LONG VARCHAR' are not supported. ij> -- PRIMARY KEY create table testConst1(c1 long varchar not null primary key); ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported for that type. ij> -- UNIQUE KEY constraints CREATE TABLE testconst2 (col1 long varchar not null, CONSTRAINT uk UNIQUE (col1)); ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported for that type. ij> -- FOREIGN KEY constraints create table testConst3 (c1 char(10) not null, primary key (c1)); 0 rows inserted/updated/deleted ij> create table testConst4 (c1 long varchar not null, constraint fk foreign key (c1) references testConst3 (c1)); ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, or DISTINCT, because comparisons are not supported for that type. ij> drop table testConst3; 0 rows inserted/updated/deleted ij> -- MAX aggregate function select max(c1) from testPredicate1; ERROR 42Y22: Aggregate MAX cannot operate on type LONG VARCHAR. ij> -- MIN aggregate function select min(c1) from testPredicate1; ERROR 42Y22: Aggregate MIN cannot operate on type LONG VARCHAR. ij> drop table testpredicate1; 0 rows inserted/updated/deleted ij> drop table testpredicate2; 0 rows inserted/updated/deleted ij> -- CLOB/BLOB limits and sizes -- FAIL - bigger than 2G or 2Gb with no modifier create table DB2LIM.FB1(FB1C BLOB(3G)); ERROR 42X44: Invalid length '3G' in column specification. ij> create table DB2LIM.FB2(FB2C BLOB(2049M)); ERROR 42X44: Invalid length '2049M' in column specification. ij> create table DB2LIM.FB3(FB3C BLOB(2097153K)); ERROR 42X44: Invalid length '2097153K' in column specification. ij> create table DB2LIM.FB4(FB4C BLOB(2147483648)); ERROR 42X44: Invalid length '2147483648' in column specification. ij> -- OK 2G and end up as 2GB - 1 (with modifier) create table DB2LIM.GB1(GB1C BLOB(2G)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GB2(GB2C BLOB(2048M)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GB3(GB3C BLOB(2097152K)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GB4(GB4C BLOB(2147483647)); 0 rows inserted/updated/deleted ij> -- next lower value create table DB2LIM.GB5(GB5C BLOB(1G)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GB6(GB6C BLOB(2047M)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GB7(GB7C BLOB(2097151K)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GB8(GB8C BLOB(2147483646)); 0 rows inserted/updated/deleted ij> drop table DB2LIM.GB5; 0 rows inserted/updated/deleted ij> drop table DB2LIM.GB6; 0 rows inserted/updated/deleted ij> drop table DB2LIM.GB7; 0 rows inserted/updated/deleted ij> drop table DB2LIM.GB8; 0 rows inserted/updated/deleted ij> -- no length (default to 1Mb) create table DB2LIM.GB9(GB9C BLOB); 0 rows inserted/updated/deleted ij> create table DB2LIM.GB10(GB10C BINARY LARGE OBJECT); 0 rows inserted/updated/deleted ij> drop table DB2LIM.GB9; 0 rows inserted/updated/deleted ij> drop table DB2LIM.GB10; 0 rows inserted/updated/deleted ij> -- FAIL - bigger than 2G or 2Gb with no modifier create table DB2LIM.FC1(FC1C CLOB(3G)); ERROR 42X44: Invalid length '3G' in column specification. ij> create table DB2LIM.FC2(FC2C CLOB(2049M)); ERROR 42X44: Invalid length '2049M' in column specification. ij> create table DB2LIM.FC3(FC3C CLOB(2097153K)); ERROR 42X44: Invalid length '2097153K' in column specification. ij> create table DB2LIM.FC4(FC4C CLOB(2147483648)); ERROR 42X44: Invalid length '2147483648' in column specification. ij> -- OK 2G and end up as 2GC - 1 (with modifier) create table DB2LIM.GC1(GC1C CLOB(2G)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GC2(GC2C CLOB(2048M)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GC3(GC3C CLOB(2097152K)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GC4(GC4C CLOB(2147483647)); 0 rows inserted/updated/deleted ij> -- next lower value create table DB2LIM.GC5(GC5C CLOB(1G)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GC6(GC6C CLOB(2047M)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GC7(GC7C CLOB(2097151K)); 0 rows inserted/updated/deleted ij> create table DB2LIM.GC8(GC8C CLOB(2147483646)); 0 rows inserted/updated/deleted ij> drop table DB2LIM.GC5; 0 rows inserted/updated/deleted ij> drop table DB2LIM.GC6; 0 rows inserted/updated/deleted ij> drop table DB2LIM.GC7; 0 rows inserted/updated/deleted ij> drop table DB2LIM.GC8; 0 rows inserted/updated/deleted ij> -- no length (default to 1Mb) create table DB2LIM.GC9(GC9C CLOB); 0 rows inserted/updated/deleted ij> create table DB2LIM.GC10(GC10C CHARACTER LARGE OBJECT); 0 rows inserted/updated/deleted ij> create table DB2LIM.GC11(GC11C CHAR LARGE OBJECT); 0 rows inserted/updated/deleted ij> drop table DB2LIM.GC9; 0 rows inserted/updated/deleted ij> drop table DB2LIM.GC10; 0 rows inserted/updated/deleted ij> drop table DB2LIM.GC11; 0 rows inserted/updated/deleted ij> SELECT CAST (TABLENAME AS CHAR(10)) AS T, CAST (COLUMNNAME AS CHAR(10)) AS C, CAST (COLUMNDATATYPE AS CHAR(30)) AS Y FROM SYS.SYSTABLES T, SYS.SYSSCHEMAS S, SYS.SYSCOLUMNS C WHERE S.SCHEMAID = T.SCHEMAID AND S.SCHEMANAME = 'DB2LIM' AND C.REFERENCEID = T.TABLEID ORDER BY 1; T |C |Y ---------------------------------------------------- GB1 |GB1C |BLOB(2147483647) GB2 |GB2C |BLOB(2147483647) GB3 |GB3C |BLOB(2147483647) GB4 |GB4C |BLOB(2147483647) GC1 |GC1C |CLOB(2147483647) GC2 |GC2C |CLOB(2147483647) GC3 |GC3C |CLOB(2147483647) GC4 |GC4C |CLOB(2147483647) ij> --- CHAR/VARCHAR and LOBs. (beetle 5741) --- test that we can insert CHAR/VARCHAR directly CREATE TABLE b (colone blob(1K)); 0 rows inserted/updated/deleted ij> VALUES '50'; 1 -- 50 ij> INSERT INTO b VALUES '50'; ERROR 42821: Columns of type 'BLOB' cannot hold values of type 'CHAR'. ij> VALUES cast('50' as varchar(80)); 1 -------------------------------------------------------------------------------- 50 ij> INSERT INTO b VALUES cast('50' as varchar(80)); ERROR 42821: Columns of type 'BLOB' cannot hold values of type 'VARCHAR'. ij> VALUES (CAST('50' AS BLOB(1K))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> INSERT INTO b VALUES (CAST('50' AS BLOB(1K))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> VALUES (CAST(cast('50' as varchar(80)) AS BLOB(1K))); ERROR 42846: Cannot convert types 'VARCHAR' to 'BLOB'. ij> INSERT INTO b VALUES (CAST(cast('50' as varchar(80)) AS BLOB(1K))); ERROR 42846: Cannot convert types 'VARCHAR' to 'BLOB'. ij> VALUES cast('50' as long varchar); 1 -------------------------------------------------------------------------------------------------------------------------------- 50 ij> INSERT INTO b VALUES cast('50' as long varchar); ERROR 42821: Columns of type 'BLOB' cannot hold values of type 'LONG VARCHAR'. ij> -- test w LOBs VALUES (CAST('50' AS BLOB(1K))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> INSERT INTO b VALUES (CAST('50' AS BLOB(1K))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> VALUES (CAST('50' AS CLOB(1K))); 1 -------------------------------------------------------------------------------------------------------------------------------- 50 ij> INSERT INTO b VALUES (CAST('50' AS CLOB(1K))); ERROR 42821: Columns of type 'BLOB' cannot hold values of type 'CLOB'. ij> VALUES (CAST('50' AS NCLOB(1K))); ERROR 0A000: Feature not implemented: NCLOB. ij> INSERT INTO b VALUES (CAST('50' AS NCLOB(1K))); ERROR 0A000: Feature not implemented: NCLOB. ij> DROP TABLE b; 0 rows inserted/updated/deleted ij> CREATE TABLE c (colone clob(1K)); 0 rows inserted/updated/deleted ij> VALUES '50'; 1 -- 50 ij> INSERT INTO c VALUES '50'; 1 row inserted/updated/deleted ij> VALUES cast('50' as varchar(80)); 1 -------------------------------------------------------------------------------- 50 ij> INSERT INTO c VALUES cast('50' as varchar(80)); 1 row inserted/updated/deleted ij> VALUES (CAST('50' AS CLOB(1K))); 1 -------------------------------------------------------------------------------------------------------------------------------- 50 ij> INSERT INTO c VALUES (CAST('50' AS CLOB(1K))); 1 row inserted/updated/deleted ij> VALUES (CAST(cast('50' as varchar(80)) AS CLOB(1K))); 1 -------------------------------------------------------------------------------------------------------------------------------- 50 ij> INSERT INTO c VALUES (CAST(cast('50' as varchar(80)) AS CLOB(1K))); 1 row inserted/updated/deleted ij> VALUES cast('50' as long varchar); 1 -------------------------------------------------------------------------------------------------------------------------------- 50 ij> INSERT INTO c VALUES cast('50' as long varchar); 1 row inserted/updated/deleted ij> -- test w LOBs VALUES (CAST('50' AS BLOB(1K))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> INSERT INTO c VALUES (CAST('50' AS BLOB(1K))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> VALUES (CAST('50' AS CLOB(1K))); 1 -------------------------------------------------------------------------------------------------------------------------------- 50 ij> INSERT INTO c VALUES (CAST('50' AS CLOB(1K))); 1 row inserted/updated/deleted ij> VALUES (CAST('50' AS NCLOB(1K))); ERROR 0A000: Feature not implemented: NCLOB. ij> INSERT INTO c VALUES (CAST('50' AS NCLOB(1K))); ERROR 0A000: Feature not implemented: NCLOB. ij> DROP TABLE c; 0 rows inserted/updated/deleted ij> CREATE TABLE n (colone clob(1K)); 0 rows inserted/updated/deleted ij> VALUES '50'; 1 -- 50 ij> INSERT INTO n VALUES '50'; 1 row inserted/updated/deleted ij> VALUES cast('50' as varchar(80)); 1 -------------------------------------------------------------------------------- 50 ij> INSERT INTO n VALUES cast('50' as varchar(80)); 1 row inserted/updated/deleted ij> VALUES (CAST('50' AS CLOB(1K))); 1 -------------------------------------------------------------------------------------------------------------------------------- 50 ij> INSERT INTO n VALUES (CAST('50' AS CLOB(1K))); 1 row inserted/updated/deleted ij> VALUES (CAST(cast('50' as varchar(80)) AS CLOB(1K))); 1 -------------------------------------------------------------------------------------------------------------------------------- 50 ij> INSERT INTO n VALUES (CAST(cast('50' as varchar(80)) AS CLOB(1K))); 1 row inserted/updated/deleted ij> VALUES cast('50' as long varchar); 1 -------------------------------------------------------------------------------------------------------------------------------- 50 ij> INSERT INTO n VALUES cast('50' as long varchar); 1 row inserted/updated/deleted ij> -- test w LOBs VALUES (CAST('50' AS BLOB(1K))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> INSERT INTO n VALUES (CAST('50' AS BLOB(1K))); ERROR 42846: Cannot convert types 'CHAR' to 'BLOB'. ij> VALUES (CAST('50' AS CLOB(1K))); 1 -------------------------------------------------------------------------------------------------------------------------------- 50 ij> INSERT INTO n VALUES (CAST('50' AS CLOB(1K))); 1 row inserted/updated/deleted ij> VALUES (CAST('50' AS NCLOB(1K))); ERROR 0A000: Feature not implemented: NCLOB. ij> INSERT INTO n VALUES (CAST('50' AS NCLOB(1K))); ERROR 0A000: Feature not implemented: NCLOB. ij> DROP TABLE n; 0 rows inserted/updated/deleted ij>