For this example, consider the table clob_tbl, where the definition and data is as follows:
CREATE TABLE clob_tbl(id INTEGER, clob_column_2 CLOB(20)) PRIMARY INDEX(id); INSERT clob_tbl(1, 'First 3 New Rows'); INSERT clob_tbl(2, 'Second 3 New Rows');
The table function ClobTbl is created as follows:
CALL SQLJ.INSTALL_JAR('CJ!ClobFunctions.jar', 'ClobFunctions',0);
REPLACE FUNCTION ClobTbl(in_parm CLOB(20))
RETURNS TABLE (id INTEGER,
clobcontent VARCHAR(20))
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA
EXTERNAL NAME 'ClobFunctions:ClobFunctions.ClobTbl';
The source code of the function ClobTbl() appears below. The following comments highlight code of interest.
| Comments | Details |
|---|---|
/** PROBLEM **/ ... /** END PROBLEM **/ |
These comments delimit code that instantiates a local_ctx_clob class object clob_info and then calls allocCtx(clob_info) to allocate the storage space for it. The method allocCtx() serializes clob_info into a binary stream and reserves the storage with the size equal to the size of the binary stream. The class local_ctx_clob has an array of java.sql.Clob[] type. But in the TBL_PRE_INIT phase, all entries in the java.sql.Clob[] array default to null. Thus, when the call to allocCtx() serializes clob_info, there is no java.sql.Clob object for serialization. This results in a much smaller serialized binary stream than what is needed. Thus, the storage space allocated is also smaller than what is needed. Later, when the first lob data coming is processed in the TBL_INIT phase, the call Tbl.setCtxObject() causes an error because the UDF is trying to write more data than what is available in the reserved storage space. |
/** SOLUTION **/ ... /** END SOLUTION **/ |
These comments delimit code that solves the problem. The code checks each item_clob object and if its member myclob is null, then it writes a dummy byte array into the serialized binary stream so that its size can be big enough for later usage. 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. |
import java.io.*;
import java.sql.*;
import com.teradata.fnc.*;
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();
/************************* SOLUTION *************************/
/* Allocate storage for lob by writing the dummy bytes */
/* into its serialized binary stream if myclob is null. */
if (myclob == null)
out.write(dummy);
/*********************** END SOLUTION ***********************/
}
private void readObject(java.io.ObjectInputStream in)
throws IOException, ClassNotFoundException
{
in.defaultReadObject();
}
}
class local_ctx_clob implements Serializable
{
int Num_Row;
int Cur_Row;
item_clob[] Clob_List;
public local_ctx_clob(){}
public local_ctx_clob(int NR, int CR, item_clob[] Clob_List)
{
this.Num_Row=NR;
this.Cur_Row=CR;
this.Clob_List=Clob_List;
}
}
public class ClobFunctions
{
static int cnt = 0;
/* ClobTbl uses each lob value in clob_tbl.clob_column_2 */
/* to generate 3 new rows for the return table. */
public static void ClobTbl( java.sql.Clob in_parm, int[] id,
String[] clobcontent )
throws SQLException
{
local_ctx_clob clob_info;
int[] phase = new int[1];
Tbl tbl = new Tbl();
try
{
switch (tbl.getPhase(phase))
{
case Tbl.TBL_MODE_CONST:
throw new SQLException("not for const mode", "U0006");
case Tbl.TBL_MODE_VARY:
switch(phase[0])
{
case Tbl.TBL_PRE_INIT:
{
/******************* PROBLEM *******************/
/* Allocate storage space to hold intermediate */
/* data which is used to generate 3 new rows. */
item_clob[] clob_arr = new item_clob[3];
for( int i=0; i<clob_arr.length; i++)
clob_arr[i]=new item_clob();
clob_info = new local_ctx_clob(3, 0, clob_arr);
tbl.allocCtx(clob_info);
/***************** END PROBLEM *****************/
}
break;
case Tbl.TBL_INIT:
{
/* Each lob passed in is stored in an */
/* intermediate structure that is used to */
/* construct 3 new rows in TBL_BUILD phase. */
item_clob[] clob_arr = new item_clob[3];
for( int i=0; i<clob_arr.length; i++) {
clob_arr[i] = new item_clob();
clob_arr[i].myclob = in_parm;
if (in_parm != null) clob_arr[i].id = cnt++;
}
clob_info = new local_ctx_clob(3, 0, clob_arr);
/* Store the intermediate data */
tbl.setCtxObject(clob_info);
}
break;
case Tbl.TBL_BUILD:
{
/* Get intermediate data stored in TBL_INIT */
/* phase and use it to generate 3 rows */
clob_info = (local_ctx_clob)tbl.getCtxObject();
if ( clob_info.Cur_Row >= clob_info.Num_Row ||
clob_info.Clob_List[0].myclob == null) {
throw new SQLException("no more data","02000");
} else {
id[0] =
clob_info.Clob_List[clob_info.Cur_Row].id;
Reader R = clob_info.Clob_List[clob_info.Cur_Row].myclob.getCharacterStream();
char[] chars = new char[20];
R.read(chars);
R.close();
clobcontent[0] = (new String(chars)).trim();
clob_info.Cur_Row++;
}
tbl.setCtxObject(clob_info);
}
break;
case Tbl.TBL_FINI:
clob_info = (local_ctx_clob)tbl.getCtxObject();
clob_info.Cur_Row = 0;
clob_info.Num_Row = 0;
clob_info.Clob_List= null;
break;
case Tbl.TBL_END:
clob_info = (local_ctx_clob)tbl.getCtxObject();
break;
case Tbl.TBL_ABORT:
clob_info = (local_ctx_clob)tbl.getCtxObject();
break;
}
return;
}
}catch(ClassNotFoundException e){
throw new SQLException("Class not located Error", "U0006");
}catch(StreamCorruptedException e){
throw new SQLException("Stream Corrupt Error", "U0006");
}catch(IOException e){
e.printStackTrace();
}
}
}
The content of table clob_tbl is:
BTEQ -- Enter your DBC/SQL request or BTEQ command:
sel * from clob_tbl;
*** Query completed. 2 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
id clob_column_2
----------- --------------------
1 First 3 New Rows
2 Second 3 New Rows
Without the solution code, calling ClobTbl() produces an error.
BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT new_tbl.id, new_tbl.clobcontent
FROM TABLE (ClobTbl(clob_tbl.clob_column_2)) AS new_tbl
ORDER by new_tbl.id;
*** Failure 7828 Unexpected Java Exception SQLSTATE 38000: An
java.lang.Error (Tbl.setCtxObject failed because object was larger
than general scratchpad.) exception was thrown.
Statement# 1, Info =0
*** Total elapsed time was 3 seconds.
With the solution code, calling ClobTbl() produces the correct answer:
BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT new_tbl.id, new_tbl.clobcontent
FROM TABLE (ClobTbl(clob_tbl.clob_column_2)) AS new_tbl
ORDER by new_tbl.id;
*** Query completed. 6 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
id clobcontent
----------- --------------------
0 First 3 New Rows
1 First 3 New Rows
2 First 3 New Rows
3 Second 3 New Rows
4 Second 3 New Rows
5 Second 3 New Rows