This is an example of a Java table function with simple mapping corresponding to the previous SQL function definition with simple mapping.
import com.teradata.fnc.*;
import java.io.*;
import java.sql.*;
/*************************************/
/* The definition of the scratch pad */
/*************************************/
class item implements Serializable
{
int custid;
int itemid;
}
class local_ctx implements Serializable{
int Num_Items;
int Cur_Item;
int store_num;
item[] Item_List;
public local_ctx() {}
public local_ctx(int Num_Items,
int Cur_Item,
int store_num,
item[] Item_List)
{
this.Num_Items = Num_Items;
this.Cur_Item = Cur_Item;
this.store_num = store_num;
this.Item_List = Item_List;
}
}
public class UserDefinedFunctions {
/****************************/
/* Reset the context block. */
/****************************/
static void Reset(local_ctx info)
{
info.Num_Items = 0;
info.Cur_Item = 0;
info.Item_List = null;
}
/*************************************************************/
/* Extract all of the data now. Actually this routine just */
/* takes the items that Prescan built and transfers the data */
/* out one item at a time. */
/*************************************************************/
static int Extract(local_ctx info,
int[] custid,
int[] store,
int[] itemid)
{
/* check to see if there is something left to extract */
if (info.Cur_Item == info.Num_Items)
return 0;
/* okay let's set the output data only if they want it */
custid[0] = info.Item_List[info.Cur_Item].custid;
store[0] = info.store_num;
itemid[0] = info.Item_List[info.Cur_Item].itemid;
/* set up for next item the next time */
info.Cur_Item++;
return 1;
}
/*************************************************************/
/* Do a pre-scan of the text and save the data. */
/* The text data that this function processes is in a very */
/* simple format: */
/* <storenum>,<num items>:<customer id>,<item number>, ... ; */
/* <storenum>,<num items>: ... */
/*************************************************************/
static int Prescan(local_ctx info,
String Text,
int frmstore) throws IOException
{
int storenum = 0;
int num_items = 0;
/* find the data for the store we are interested in */
String Tscan = Text;
int startpos = 0;
while (startpos != (-1)) {
int numpos = Tscan.indexOf(',',startpos);
if (numpos == -1) return -1;
storenum = Integer.parseInt(Tscan.substring(startpos,numpos));
startpos = numpos+1;
if(frmstore == storenum) {
num_items = Integer.parseInt(Tscan.substring(startpos,
Tscan.indexOf(':',startpos)));
break;
}
if (Tscan.indexOf(';') == -1) return -1;
else {
Tscan = Tscan.substring(Tscan.indexOf(';')+1,
Tscan.length());
startpos = 0;
if(Tscan.equals("")) return 0;
}
}
if (num_items != 0) {
info.Item_List = new item[num_items];
}
else {
info.Num_Items = 0;
return 0;
}
/* Now let's find all the entries for the store that we are */
/* interested in. Skip to first item. */
if (Tscan.indexOf(':')==-1 || Tscan.indexOf(';')==-1) return -1;
Tscan = Tscan.substring(Tscan.indexOf(':')+1,Tscan.indexOf(';'));
int pos = 0;
for (int i=0; i<num_items; i++) {
info.Item_List[i] = new item();
int nextcust = Tscan.indexOf(',',pos);
if(nextcust == -1) return -1;
info.Item_List[i].custid=
Integer.parseInt(Tscan.substring(pos,nextcust));
pos = nextcust + 1;
int nextitem = Tscan.indexOf(',',pos);
if (nextitem != -1) {
info.Item_List[i].itemid =
Integer.parseInt(Tscan.substring(pos,nextitem));
pos = nextitem + 1;
}
else {
info.Item_List[i].itemid =
Integer.parseInt(Tscan.substring(pos,Tscan.length()));
break;
}
}
info.Num_Items = num_items;
info.store_num = frmstore;
return num_items;
}
public static void extract_field(String Text, /* field decode */
int frmStore, /* data to extract */
int[] custid, /* 1st output column for row */
int[] store, /* 2nd output column */
int[] item)
throws SQLException {
local_ctx state_info;
int status;
int[] phase = new int[1];
Tbl tbl = new Tbl();
try{
/* make sure the function is called in the supported context */
switch (tbl.getPhase(phase))
{
/******************************************************/
/* Process the constant expression case. Only one AMP */
/* will participate for this example. */
/******************************************************/
case Tbl.TBL_MODE_CONST:
/* depending on the phase decide what to do */
switch(phase[0])
{
case Tbl.TBL_PRE_INIT:
if(tbl.firstParticipant()){
/* participant */
return;
} else {
/* don't participate */
if(!tbl.optout()){
throw new SQLException("Opt-out failed.",
"38U06");
}
return;
}
case Tbl.TBL_INIT:
state_info = new local_ctx();
/* Get scratch memory to keep track of things */
Reset(state_info);
/* Pre-process the Text */
status = Prescan(state_info, Text, frmStore );
if (status == -1) {
throw new SQLException(
"Text had pre-scan errors","38U08");
}
tbl.allocCtx(state_info);
tbl.setCtxObject(state_info);
break;
case Tbl.TBL_BUILD:
state_info = (local_ctx)tbl.getCtxObject();
status = Extract(state_info,
custid,
store,
item);
if (status == 0)
/* Have no more data, return no data sqlstate */
throw new SQLException("no more data","02000");
else if (status == -1){
throw new SQLException(
"Text had extract error","38U09");
}
tbl.setCtxObject(state_info);
break;
case Tbl.TBL_END:
/* everyone done */
state_info = (local_ctx)tbl.getCtxObject();
break;
}
break;
/**********************************/
/* Process the varying expression */
/**********************************/
case Tbl.TBL_MODE_VARY:
switch(phase[0])
{
case Tbl.TBL_PRE_INIT:
item[] itemarr = new item[3];
for (int i=0; i < itemarr.length; i++)
itemarr[i] = new item();
state_info = new local_ctx(0,0,0,itemarr);
/* get scratch memory to use from now on */
tbl.allocCtx(state_info);
break;
case Tbl.TBL_INIT:
state_info = new local_ctx();
/* Pre-process the Text */
status = Prescan(state_info, Text, frmStore );
if (status == -1) {
if (tbl.abort()) {
throw new SQLException(
"Text had pre-scan errors","38U08");
}
}
tbl.setCtxObject(state_info);
break;
case Tbl.TBL_BUILD:
state_info = (local_ctx)tbl.getCtxObject();
status = Extract(state_info,
custid,
store,
item);
if (status == 0)
/* Have no more data return no data sqlstate */
throw new SQLException("no more data","02000");
else if (status == -1) {
/* if I was the first then report the error */
if(tbl.abort()) {
throw new SQLException(
"Text had extract error","38U09");
}
return;
}
tbl.setCtxObject(state_info);
break;
case Tbl.TBL_FINI:
/* initialize for the next set of data */
state_info = (local_ctx)tbl.getCtxObject();
Reset(state_info);
break;
case Tbl.TBL_END:
break;
case Tbl.TBL_ABORT:
break;
}
return;
}
} catch(ClassNotFoundException e) {
e.printStackTrace();
} catch(IOException e) {
e.printStackTrace();
}
}
}