Sandhya Sundaresan created TRAFODION-2408:
---------------------------------------------
Summary: SUpport for empty_blob() and update syntax using lobhandle
Key: TRAFODION-2408
URL: https://issues.apache.org/jira/browse/TRAFODION-2408
Project: Apache Trafodion
Issue Type: Sub-task
Components: sql-exe
Reporter: Sandhya Sundaresan
Assignee: Sandhya Sundaresan
This subtask in SQL is to help JDBC implementation for LOBs. They require the
eimplementation of 2 interfaces
.EMPTY_BLOB() , EMPTY_CLOB()
update done directly to a lobhandle . This update syntax is different form the
regular updates to lob columns using position.
Assume a lob table of type :
Create table tlob(c1 int , c2 blob, primary key (c1));
Insert :
Insert into tlob values (1, empty_blob());
Insert into tlob values (1,empty_clob());
This will create a lob handle . It will also create an empty descriptor handle
entry and 1 chunk in the descriptor chunks table that is of size 0.
The “type” of the lob handle will be the same as the underlying lob type of the
column.
Eg if we had created the table to hold external lobs
Create table tlob(c1 int , c2 blob storage ‘external’ , primary key (c1));
Insert into tlob values (1, empty_blob());
The lob handle for this tlobext will contain the type ‘8’ instead of ‘2’ (as is
the case for tlob.)
But looking at the lob handle you will not be able to say if it’s empty or not.
But if lob data is selected out it will be empty.
Update:
Assume tlob has some lob data in it.
Update tlob set c2=empty_blob();
This will replace all lob data in the table with the empty lob.
The lob handle remains the same as before.
Update tlob set c2 = stringtolob(‘xxxx’);
This will replace the empty lob entry inth
descriptor handle and descriptor chunks table with a new entry.
OR
Update tlob set c2 – stringtolob(‘xxxx’, append);
This will append the new data to the empty lob. So it
will leave 2 entries in the descriptor handle and descriptor chunks table.
But the result when you select the data will be the same in both cases
above.
Update directly using a lobhandle using a buffer programmatically :
New syntax has been introduced to allow updating a lob entry using lob
handle directory. This is not positional syntax.
The syntax is as follows :
update lob (LOB ‘<lob handle>’, LOCATION < int64 address>, SIZE < int64
size>);
This will take a lob handle as input (it could be an empty lob or have
existing data). It will replace the contents with the data specified in
LOCATION.
update lob (LOB ‘<lob handle>’, LOCATION < int64 address>, SIZE < int64
size>, APPEND);
This will take a lob handle as input (it could be an empty lob or have
existing data). It will append the contents with the data specified in
LOCATION.
update lob (LOB ‘<lob handle>’, TRUNCATE);
This will truncate the existing data and initialize the lob to empty_lob
for the provided lob handle.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
