lob.out

derby database source code.good for you.

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, INTERSECT, EXCEPT or DISTINCT statements 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, INTERSECT, EXCEPT or DISTINCT statements 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, INTERSECT, EXCEPT or DISTINCT statements 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>