16.20 - Clob and Blob Classes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

Teradata implements java.sql.Clob and java.sql.Blob as serializable classes. Thus, a table function can use them, passing their objects between iterations of table function execution.

However, writing a table UDF involves special considerations when the table UDF uses Clob or Blob objects to hold the data in the intermediate storage.

Consider a table UDF that retains data between iterations using an item_clob object:

class item_clob implements Serializable
{
   int id;
   java.sql.Clob myclob;

   ...
}

To ensure that Tbl.allocCtx() allocates intermediate storage space big enough to hold all the data of the object for all iterations of the table function, the table UDF can implement the writeObject() method to write a dummy byte array into the serialized binary stream if member myclob is null.

The size of the dummy byte array is 64, which is the size of a fully serialized Teradata java.sql.Clob and java.sql.Blob object.

class item_clob implements Serializable
{
   int id;
   java.sql.Clob myclob;

   private void writeObject(java.io.ObjectOutputStream out)
   throws IOException
   {
      byte[] dummy = new byte[64];
      out.defaultWriteObject();
      /* Allocate storage for lob by writing the dummy bytes  */
      /* into its serialized binary stream if myclob is null. */
      if (myclob == null)
         out.write(dummy);
   }

   private void readObject(java.io.ObjectInputStream in)
   throws IOException, ClassNotFoundException
   {
      in.defaultReadObject();
   }
}

For a complete example, see Example: Table UDF Using Clob to Hold Data in Intermediate Storage.