Incorporate Comments 2
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/6d1c199b Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/6d1c199b Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/6d1c199b Branch: refs/heads/master Commit: 6d1c199b1a541a794733223bd4762d0026aa39f5 Parents: 083fcb0 Author: liu.yu <[email protected]> Authored: Thu Dec 21 14:40:30 2017 +0800 Committer: liu.yu <[email protected]> Committed: Thu Dec 21 14:40:30 2017 +0800 ---------------------------------------------------------------------- .../asciidoc/_chapters/working_with_lob.adoc | 70 ++++++++++++++++---- 1 file changed, 56 insertions(+), 14 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6d1c199b/docs/lob_guide/src/asciidoc/_chapters/working_with_lob.adoc ---------------------------------------------------------------------- diff --git a/docs/lob_guide/src/asciidoc/_chapters/working_with_lob.adoc b/docs/lob_guide/src/asciidoc/_chapters/working_with_lob.adoc index c05d670..6db7ea8 100644 --- a/docs/lob_guide/src/asciidoc/_chapters/working_with_lob.adoc +++ b/docs/lob_guide/src/asciidoc/_chapters/working_with_lob.adoc @@ -236,7 +236,7 @@ An unnamed parameter can be used to prepare a statement and then during an execu * `EMPTY_BLOB()` or `EMPTY_CLOB()` + -NOTE: If you want to insert `EMPTY_BLOB()` or `EMPTY_CLOB()` into a lob column, the CQD `TRAF_CLOB_AS_VARCHAR` which is *ON* by default must be turned *OFF* before creating the table. +NOTE: If you want to insert `EMPTY_BLOB()` or `EMPTY_CLOB()` into a lob column, the CQD `TRAF_BLOB_AS_VARCHAR` or `TRAF_CLOB_AS_VARCHAR` which is *ON* by default must be turned *OFF* before creating the table, otherwise an error will be raised and the column definition of the lob column is VARCHAR. ** If `EMPTY_BLOB()` or `EMPTY_CLOB()` is specified, then a dummy lob handle is created. @@ -269,6 +269,61 @@ For example, if you have a directory of pictures, you can specify the full hdfs [#examples] === Examples + +* This example uses a parameter. ++ +``` +PREPARE S FROM INSERT INTO t130lob2 VALUES (1, ?); +EXECUTE S USING 'fgfgfhfhfhfhhfhfhfhjfkkfkffllflflfll'; +``` + +* This example does not turn the CQD `TRAF_BLOB_AS_VARCHAR` OFF before creating the table test1, thus it fails to insert `EMPTY_BLOB()` into c2 whose column definition is VARCHAR. ++ +``` +>>CREATE TABLE test1(C1 INT, C2 BLOB); +--- SQL operation complete. + +>>CQD TRAF_BLOB_AS_VARCHAR 'OFF'; +--- SQL operation complete. + +>>INSERT INTO test1 VALUES(1, EMPTY_BLOB()); +*** ERROR[4035] Type LOB cannot be cast to type VARCHAR(100000). +*** ERROR[8822] The statement was not prepared. + +>>SHOWDDL TABLE test1; +CREATE TABLE TRAFODION.SEABASE.TEST1 + ( + C1 INT DEFAULT NULL NOT SERIALIZED + , C2 VARCHAR(100000) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED + ) + ATTRIBUTES ALIGNED FORMAT +; +--- SQL operation complete. +``` + +* This example turns the CQD `TRAF_CLOB_AS_VARCHAR` OFF before creating the table test2 and inserting `EMPTY_CLOB()` into c2 whose column definition is CLOB. ++ +``` +>>CQD TRAF_CLOB_AS_VARCHAR 'OFF'; +--- SQL operation complete. + +>>CREATE TABLE test2 (C1 INT, C2 CLOB); +--- SQL operation complete. + +>>INSERT INTO test2 VALUES(1, EMPTY_CLOB()); +--- 1 row(s) inserted. + +>>SHOWDDL TABLE test2; +CREATE TABLE TRAFODION.SEABASE.TEST2 + ( + C1 INT DEFAULT NULL NOT SERIALIZED + , C2 CLOB DEFAULT NULL NOT SERIALIZED + ) + ATTRIBUTES ALIGNED FORMAT +; +--- SQL operation complete. +``` + * This example uses the `STRINGTOLOB` function that converts a simple string literal into LOB format before inserting. + ``` @@ -295,19 +350,6 @@ FILETOLOB('hdfs:///lobs/lob_input_a1.txt'), EXTERNALTOLOB('hdfs:///lobs/lob_input_a1.txt')); ``` -* This example uses a parameter. -+ -``` -PREPARE S FROM INSERT INTO t130lob2 VALUES (1, ?); -EXECUTE S USING 'fgfgfhfhfhfhhfhfhfhjfkkfkffllflflfll'; -``` - -* This example uses the `EMPTY_BLOB` function to insert an empty lob and creates a dummy lob handle. -+ -``` -INSERT INTO t130lob2 VALUES (1, empty_blob()); -``` - [#inserting into a sql table containing lob columns using select clause] == Inserting into a SQL Table Containing LOB Columns Using Select Clause
