Comment 5 for bug 1430040

Verified on the v0330 build installed on a workstation. Tried both CQD TRAF_BLOB_AS_VARCHAR 'ON' and 'OFF', the behavior is now as designed:

>>create schema mytest3;

--- SQL operation complete.
>>set schema mytest3;

--- SQL operation complete.
>>
>>cqd TRAF_BLOB_AS_VARCHAR 'ON';

--- SQL operation complete.
>>
>>create library qaTmudfLib file '<mydir>/qaTMUdfTest.so';

--- SQL operation complete.
>>
>>create table_mapping function qa_tmudf()
+>external name 'QA_TMUDF'
+>language cpp
+>library qaTmudfLib;

--- SQL operation complete.
>>
>>create table mytable (c_clob clob, c_blob blob);

--- SQL operation complete.
>>showddl mytable;

CREATE TABLE TRAFODION.MYTEST3.MYTABLE
  (
    C_CLOB VARCHAR(100000) CHARACTER SET ISO88591
      COLLATE DEFAULT DEFAULT NULL
  , C_BLOB VARCHAR(100000) CHARACTER SET ISO88591
      COLLATE DEFAULT DEFAULT NULL
  )
;

--- SQL operation complete.
>>insert into mytable values ('CLOB_1', 'BLOB_1');

--- 1 row(s) inserted.
>>select CAST(c_clob as CHAR(10)), CAST(c_blob as CHAR(10)) from mytable;

(EXPR) (EXPR)
---------- ----------

CLOB_1 BLOB_1

--- 1 row(s) selected.
>>select CAST(c_clob as CHAR(10)), CAST(c_blob as CHAR(10)) from UDF(qa_tmudf(TABLE(select * from mytable)));

(EXPR) (EXPR)
---------- ----------

CLOB_1 BLOB_1

--- 1 row(s) selected.
>>
>>drop schema mytest3 cascade;

--- SQL operation complete.
>>
>>create schema mytest4;

--- SQL operation complete.
>>set schema mytest4;

--- SQL operation complete.
>>
>>cqd TRAF_BLOB_AS_VARCHAR 'OFF';

--- SQL operation complete.
>>
>>create library qaTmudfLib file '<mydir>/qaTMUdfTest.so';

--- SQL operation complete.
>>
>>create table_mapping function qa_tmudf()
+>external name 'QA_TMUDF'
+>language cpp
+>library qaTmudfLib;

--- SQL operation complete.
>>
>>create table mytable (c_clob clob, c_blob blob);

--- SQL operation complete.
>>showddl mytable;

CREATE TABLE TRAFODION.MYTEST4.MYTABLE
  (
    C_CLOB CLOB DEFAULT NULL
  , C_BLOB BLOB DEFAULT NULL
  )
;

--- SQL operation complete.
>>insert into mytable values ('CLOB_1', 'BLOB_1');

*** ERROR[4039] Column C_CLOB is of type LOB, incompatible with the value's type, CHAR(6).

*** ERROR[8822] The statement was not prepared.

>>select CAST(c_clob as CHAR(10)), CAST(c_blob as CHAR(10)) from mytable;

*** ERROR[4035] Type LOB cannot be cast to type CHAR(10).

*** ERROR[8822] The statement was not prepared.

>>select CAST(c_clob as CHAR(10)), CAST(c_blob as CHAR(10)) from UDF(qa_tmudf(TABLE(select * from mytable)));

*** ERROR[11151] Unable to use 'type' 'LOB ALLOWS NULLS' in a user-defined routine. Details: unsupported type class.

*** ERROR[8822] The statement was not prepared.

>>
>>drop schema mytest4 cascade;

*** ERROR[1069] Schema TRAFODION.MYTEST4 could not be dropped.

--- SQL operation failed with errors.