[ https://issues.apache.org/jira/browse/TRAFODION-2408?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Sandhya Sundaresan closed TRAFODION-2408. ----------------------------------------- > 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 > Priority: Major > Fix For: 2.1-incubating > > > 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 (v7.6.3#76005)