You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by db...@apache.org on 2017/12/23 04:30:50 UTC
[2/3] incubator-trafodion git commit: Incorporate Comments 2
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 <yu...@esgyn.cn>
Authored: Thu Dec 21 14:40:30 2017 +0800
Committer: liu.yu <yu...@esgyn.cn>
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