// ----------------------------------------------------------
// ---                                                    ---
// ---     FILE:    HTM_JAVA.HTML                         ---
// ---     AUTHOR:  G.BIRCHALL                            ---
// ---     DATE:    13/SEP/2006                           ---
// ---     NOTES:   Below is some very basic java code    ---
// ---              that demonstrates the use of simple   ---
// ---              DB2 user-defined functions to run     ---
// ---              SQL within SQL.  There are three      ---
// ---              types of function:                    ---
// ---                                                    ---
// ---               - Scalar functions  that return a   ---
// ---                 single value (different types).    ---
// ---               - Table functions  that return a    ---
// ---                 single column of data (different   ---
// ---                 data types), plus an INTEGER row-  ---
// ---                 number value.                      ---
// ---               - Table functions  that return more ---
// ---                 one column of data (VARCHAR only), ---
// ---                 plus an INTEGER row-number value.  ---
// ---                                                    ---
// ---     USAGE NOTES:                                   ---
// ---     #1  Save this file on your own machine.        ---
// ---     #2  Remove the few lines of HTML code at the   ---
// ---         top and bottom of this file.               ---
// ---     #3  Rename the file "Graeme1java".             ---
// ---     #4  Compile the java code:                     ---
// ---         javac Graeme1java                          ---
// ---     #5  Copy the generated Graeme1.class file to   ---
// ---         the relevant "jar" directory.              ---
// ---     #6  Create the matching DB2 user-defined       ---
// ---         functions (see: HTM_JSQL.HTM).             ---
// ---     #7  You are ready to go.  If they don't work,  ---
// ---         don't ask me, because I don't know much    ---
// ---         about java and DB2.                        ---
// ---                                                    ---
// ---     NOTE: I have defined the tabular functions     ---
// ---     to ignore columns of certain types and all     ---
// ---     columns longer than 254 bytes. Change this     ---
// ---     as you wish. Make sure to change the CREATE    ---
// ---     FUNCTION code to match.                        ---
// ---                                                    ---
// ----------------------------------------------------------

import java.lang.*;
import COM.ibm.db2.app.*;
import java.sql.*;
import java.math.*;
import java.io.*;

public class Graeme1 extends UDF {


  // --------------------------------------------------------
  // ---     SINGLE-DATA-VALUE SCALAR FUNCTIONS           ---
  // ---                                                  ---
  // ---     These functions accept a SQL stmt (query) as ---
  // ---     input.  The query is executed and the first  ---
  // ---     column and row fetched is returned to DB2.   ---
  // ---     All other columns and rows are ignored.      ---
  // ---                                                  ---
  // ---     There is one function per data-type:         ---
  // ---       - SMALLINT                                 ---
  // ---       - INTEGER                                  ---
  // ---       - BIGINT                                   ---
  // ---       - DOUBLE                                   ---
  // ---       - DECIMAL                                  ---
  // ---       - VARCHAR                                  ---
  // ---                                                  ---
  // --------------------------------------------------------


  public void get_Smallint(String inStmt,
                           short  outValue)
  throws Exception {
    try {
      Connection         con = DriverManager.getConnection
                               ("jdbc:default:connection");
      PreparedStatement stmt = con.prepareStatement(inStmt);
      ResultSet           rs = stmt.executeQuery();
      if (rs.next() == true  &&  rs.getString(1) != null) {
        set(2, rs.getShort(1));
      }
      rs.close();
      stmt.close();
      con.close();
    }
    catch (SQLException sqle) {
      setSQLstate("38999");
      setSQLmessage("SQLCODE = " + sqle.getSQLState());
      return;
    }
  }


  public void get_Integer(String inStmt,
                          int    outValue)
  throws Exception {
    try {
      Connection         con = DriverManager.getConnection
                               ("jdbc:default:connection");
      PreparedStatement stmt = con.prepareStatement(inStmt);
      ResultSet           rs = stmt.executeQuery();
      if (rs.next() == true  &&  rs.getString(1) != null) {
        set(2, rs.getInt(1));
      }
      rs.close();
      stmt.close();
      con.close();
    }
    catch (SQLException sqle) {
      setSQLstate("38999");
      setSQLmessage("SQLCODE = " + sqle.getSQLState());
      return;
    }
  }


  public void get_Bigint(String inStmt,
                         long   outValue)
  throws Exception {
    try {
      Connection         con = DriverManager.getConnection
                               ("jdbc:default:connection");
      PreparedStatement stmt = con.prepareStatement(inStmt);
      ResultSet           rs = stmt.executeQuery();
      if (rs.next() == true  &&  rs.getString(1) != null) {
        set(2, rs.getLong(1));
      }
      rs.close();
      stmt.close();
      con.close();
    }
    catch (SQLException sqle) {
      setSQLstate("38999");
      setSQLmessage("SQLCODE = " + sqle.getSQLState());
      return;
    }
  }


  public void get_Double(String inStmt,
                         double outValue)
  throws Exception {
    try {
      Connection         con = DriverManager.getConnection
                               ("jdbc:default:connection");
      PreparedStatement stmt = con.prepareStatement(inStmt);
      ResultSet           rs = stmt.executeQuery();
      if (rs.next() == true  &&  rs.getString(1) != null) {
        set(2, rs.getDouble(1));
      }
      rs.close();
      stmt.close();
      con.close();
    }
    catch (SQLException sqle) {
      setSQLstate("38999");
      setSQLmessage("SQLCODE = " + sqle.getSQLState());
      return;
    }
  }


  public void get_Decimal(String  inStmt,
                          BigDecimal outValue)
  throws Exception {
    try {
      Connection         con = DriverManager.getConnection
                               ("jdbc:default:connection");
      PreparedStatement stmt = con.prepareStatement(inStmt);
      ResultSet           rs = stmt.executeQuery();
      if (rs.next() == true  &&  rs.getString(1) != null) {
        set(2, rs.getBigDecimal(1));
      }
      rs.close();
      stmt.close();
      con.close();
    }
    catch (SQLException sqle) {
      setSQLstate("38999");
      setSQLmessage("SQLCODE = " + sqle.getSQLState());
      return;
    }
  }


  public void get_Varchar(String inStmt,
                          String outValue)
  throws Exception {
    try {
      Connection         con = DriverManager.getConnection
                               ("jdbc:default:connection");
      PreparedStatement stmt = con.prepareStatement(inStmt);
      ResultSet           rs = stmt.executeQuery();
      if (rs.next() == true  &&  rs.getString(1) != null) {
        set(2, rs.getString(1));
      }
      rs.close();
      stmt.close();
      con.close();
    }
    catch (SQLException sqle) {
      setSQLstate("38999");
      setSQLmessage("SQLCODE = " + sqle.getSQLState());
      return;
    }
  }


  // --------------------------------------------------------
  // ---     SINGLE-DATA-COLUMN TABLE FUNCTIONS           ---
  // ---                                                  ---
  // ---     These functions accept a SQL stmt (query) as ---
  // ---     input.  The query is executed and the first  ---
  // ---     column of data (all rows) is returned.  Any  ---
  // ---     other columns are ignored.                   ---
  // ---                                                  ---
  // ---     There is one function per data-type:         ---
  // ---       - SMALLINT                                 ---
  // ---       - INTEGER                                  ---
  // ---       - BIGINT                                   ---
  // ---       - DOUBLE                                   ---
  // ---       - DECIMAL                                  ---
  // ---       - VARCHAR                                  ---
  // ---                                                  ---
  // --------------------------------------------------------


  Connection        con;
  Statement         stmt;
  ResultSet         rs;
  ResultSetMetaData rsmtadta;
  int               rowNum;
  int               i;
  int               outLength;
  short             colNum;
  int               colCount;
  String[] colName  = new String[1100];
  String[] colType  = new String[1100];
  int[]    colSize  = new int[1100];


  public void tab_Smallint(String inStmt,
                           int    outNumber,
                           short  outValue)
  throws Exception {
    switch (getCallType()) {
      case SQLUDF_TF_FIRST:
        break;
      case SQLUDF_TF_OPEN:
        rowNum = 1;
        try {
          con  = DriverManager.getConnection
                 ("jdbc:default:connection");
          stmt = con.createStatement();
          rs   = stmt.executeQuery(inStmt);
        }
        catch(SQLException sqle) {
          setSQLstate("38999");
          setSQLmessage("SQLCODE = " + sqle.getSQLState());
          return;
        }
        break;
      case SQLUDF_TF_FETCH:
        if (rs.next() == true) {
          set(2, rowNum);
          if (rs.getString(1) != null) {
            set(3, rs.getShort(1));
          }
          rowNum++;
        }
        else {
          setSQLstate ("02000");
        }
        break;
      case SQLUDF_TF_CLOSE:
        rs.close();
        stmt.close();
        con.close();
        break;
      case SQLUDF_TF_FINAL:
        break;
    }
  }


  public void tab_Integer(String inStmt,
                          int    outNumber,
                          int    outValue)
  throws Exception {
    switch (getCallType()) {
      case SQLUDF_TF_FIRST:
        break;
      case SQLUDF_TF_OPEN:
        rowNum = 1;
        try {
          con  = DriverManager.getConnection
                 ("jdbc:default:connection");
          stmt = con.createStatement();
          rs   = stmt.executeQuery(inStmt);
        }
        catch(SQLException sqle) {
          setSQLstate("38999");
          setSQLmessage("SQLCODE = " + sqle.getSQLState());
          return;
        }
        break;
      case SQLUDF_TF_FETCH:
        if (rs.next() == true) {
          set(2, rowNum);
          if (rs.getString(1) != null) {
            set(3, rs.getInt(1));
          }
          rowNum++;
        }
        else {
          setSQLstate ("02000");
        }
        break;
      case SQLUDF_TF_CLOSE:
        rs.close();
        stmt.close();
        con.close();
        break;
      case SQLUDF_TF_FINAL:
        break;
    }
  }


  public void tab_Bigint(String inStmt,
                         int    outNumber,
                         long   outValue)
  throws Exception {
    switch (getCallType()) {
      case SQLUDF_TF_FIRST:
        break;
      case SQLUDF_TF_OPEN:
        rowNum = 1;
        try {
          con  = DriverManager.getConnection
                 ("jdbc:default:connection");
          stmt = con.createStatement();
          rs   = stmt.executeQuery(inStmt);
        }
        catch(SQLException sqle) {
          setSQLstate("38999");
          setSQLmessage("SQLCODE = " + sqle.getSQLState());
          return;
        }
        break;
      case SQLUDF_TF_FETCH:
        if (rs.next() == true) {
          set(2, rowNum);
          if (rs.getString(1) != null) {
            set(3, rs.getLong(1));
          }
          rowNum++;
        }
        else {
          setSQLstate ("02000");
        }
        break;
      case SQLUDF_TF_CLOSE:
        rs.close();
        stmt.close();
        con.close();
        break;
      case SQLUDF_TF_FINAL:
        break;
    }
  }


  public void tab_Double(String inStmt,
                         int    outNumber,
                         double outValue)
  throws Exception {
    switch (getCallType()) {
      case SQLUDF_TF_FIRST:
        break;
      case SQLUDF_TF_OPEN:
        rowNum = 1;
        try {
          con  = DriverManager.getConnection
                 ("jdbc:default:connection");
          stmt = con.createStatement();
          rs   = stmt.executeQuery(inStmt);
        }
        catch(SQLException sqle) {
          setSQLstate("38999");
          setSQLmessage("SQLCODE = " + sqle.getSQLState());
          return;
        }
        break;
      case SQLUDF_TF_FETCH:
        if (rs.next() == true) {
          set(2, rowNum);
          if (rs.getString(1) != null) {
            set(3, rs.getDouble(1));
          }
          rowNum++;
        }
        else {
          setSQLstate ("02000");
        }
        break;
      case SQLUDF_TF_CLOSE:
        rs.close();
        stmt.close();
        con.close();
        break;
      case SQLUDF_TF_FINAL:
        break;
    }
  }


  public void tab_Decimal(String     inStmt,
                          int        outNumber,
                          BigDecimal outValue)
  throws Exception {
    switch (getCallType()) {
      case SQLUDF_TF_FIRST:
        break;
      case SQLUDF_TF_OPEN:
        rowNum = 1;
        try {
          con  = DriverManager.getConnection
                 ("jdbc:default:connection");
          stmt = con.createStatement();
          rs   = stmt.executeQuery(inStmt);
        }
        catch(SQLException sqle) {
          setSQLstate("38999");
          setSQLmessage("SQLCODE = " + sqle.getSQLState());
          return;
        }
        break;
      case SQLUDF_TF_FETCH:
        if (rs.next() == true) {
          set(2, rowNum);
          if (rs.getString(1) != null) {
            set(3, rs.getBigDecimal(1));
          }
          rowNum++;
        }
        else {
          setSQLstate ("02000");
        }
        break;
      case SQLUDF_TF_CLOSE:
        rs.close();
        stmt.close();
        con.close();
        break;
      case SQLUDF_TF_FINAL:
        break;
    }
  }


  public void tab_Varchar(String inStmt,
                          int    outNumber,
                          String outValue)
  throws Exception {
    switch (getCallType()) {
      case SQLUDF_TF_FIRST:
        break;
      case SQLUDF_TF_OPEN:
        rowNum = 1;
        try {
          con  = DriverManager.getConnection
                 ("jdbc:default:connection");
          stmt = con.createStatement();
          rs   = stmt.executeQuery(inStmt);
        }
        catch(SQLException sqle) {
          setSQLstate("38999");
          setSQLmessage("SQLCODE = " + sqle.getSQLState());
          return;
        }
        break;
      case SQLUDF_TF_FETCH:
        if (rs.next() == true) {
          set(2, rowNum);
          if (rs.getString(1) != null) {
            set(3, rs.getString(1));
          }
          rowNum++;
        }
        else {
          setSQLstate ("02000");
        }
        break;
      case SQLUDF_TF_CLOSE:
        rs.close();
        stmt.close();
        con.close();
        break;
      case SQLUDF_TF_FINAL:
        break;
    }
  }


  // --------------------------------------------------------
  // ---     MULTI-DATA-COLUMN (VARCHAR) TABLE FUNCTIONS  ---
  // ---                                                  ---
  // ---     These functions accept a SQL stmt (query) as ---
  // ---     input.  The query is executed and the first  ---
  // ---     "n" columns of data (all rows) is returned.  ---
  // ---     Any other columns are ignored.               ---
  // ---                                                  ---
  // ---     All columns are converted to VARCHAR. Those  ---
  // ---     that cannot be converted are set to null.    ---
  // ---                                                  ---
  // ---     The number of columns returned is:           ---
  // ---       - 2                                        ---
  // ---       - 5                                        ---
  // ---       - 10                                       ---
  // ---                                                  ---
  // --------------------------------------------------------


  public void tab_2Varchar(String inStmt,
                           short  numColumns,
                           int    rowNumber,
                           String outValue01,
                           String outValue02)
  throws Exception {
    switch (getCallType()) {
      case SQLUDF_TF_FIRST:
        break;
      case SQLUDF_TF_OPEN:
        try {
          rowNum   = 0;
          con      = DriverManager.getConnection
                     ("jdbc:default:connection");
          stmt     = con.createStatement();
          rs       = stmt.executeQuery(inStmt);
          rsmtadta = rs.getMetaData();
          colCount = rsmtadta.getColumnCount();
          if (colCount > 2) {
            colCount = 2;
          }
          for (i=1; i <= colCount; i++) {
            colType[i] = rsmtadta.getColumnTypeName(i);
            colSize[i] = rsmtadta.getColumnDisplaySize(i);
          }
        }
        catch(SQLException sqle) {
          setSQLstate("38999");
          setSQLmessage("SQLCODE = " + sqle.getSQLState());
          return;
        }
        break;
      case SQLUDF_TF_FETCH:
        rowNum++;
        if (rs.next() == true) {
          int i;
          set(2, (short)colCount);
          set(3, rowNum);
          for (i=1; i<=colCount; i++) {
            if (colType[i].equals("XML")         || 
                colType[i].equals("BLOB")        ||
                colType[i].equals("CLOB")        ||
                colType[i].equals("DBLOB")       ||
                colType[i].equals("GRAPHIC")     ||
                colType[i].equals("VARGRAPHIC")  ||
                colSize[i] > 254                 ||
                rs.getString(i) == null) {
              // DON'T DISPLAY THIS VALUE
              return;
            }
            else {
              // DISPLAY THIS COLUMN VALUE
              set(i+3, rs.getString(i));
            }
          }
        }
        else {
          setSQLstate ("02000");
        }
        break;
      case SQLUDF_TF_CLOSE:
        rs.close();
        stmt.close();
        con.close();
        break;
      case SQLUDF_TF_FINAL:
        break;
    }
  }


  public void tab_5Varchar(String inStmt,
                           short  numColumns,
                           int    rowNumber,
                           String outValue01,
                           String outValue02,
                           String outValue03,
                           String outValue04,
                           String outValue05)
  throws Exception {
    switch (getCallType()) {
      case SQLUDF_TF_FIRST:
        break;
      case SQLUDF_TF_OPEN:
        try {
          rowNum   = 0;
          con      = DriverManager.getConnection
                     ("jdbc:default:connection");
          stmt     = con.createStatement();
          rs       = stmt.executeQuery(inStmt);
          rsmtadta = rs.getMetaData();
          colCount = rsmtadta.getColumnCount();
          if (colCount > 5) {
            colCount = 5;
          }
          for (i=1; i <= colCount; i++) {
            colType[i] = rsmtadta.getColumnTypeName(i);
            colSize[i] = rsmtadta.getColumnDisplaySize(i);
          }
        }
        catch(SQLException sqle) {
          setSQLstate("38999");
          setSQLmessage("SQLCODE = " + sqle.getSQLState());
          return;
        }
        break;
      case SQLUDF_TF_FETCH:
        rowNum++;
        if (rs.next() == true) {
          int i;
          set(2, (short)colCount);
          set(3, rowNum);
          for (i=1; i<=colCount; i++) {
            if (colType[i].equals("XML")         || 
                colType[i].equals("BLOB")        ||
                colType[i].equals("CLOB")        ||
                colType[i].equals("DBLOB")       ||
                colType[i].equals("GRAPHIC")     ||
                colType[i].equals("VARGRAPHIC")  ||
                colSize[i] > 254                 ||
                rs.getString(i) == null) {
              // DON'T DISPLAY THIS VALUE
              return;
            }
            else {
              // DISPLAY THIS COLUMN VALUE
              set(i+3, rs.getString(i));
            }
          }
        }
        else {
          setSQLstate ("02000");
        }
        break;
      case SQLUDF_TF_CLOSE:
        rs.close();
        stmt.close();
        con.close();
        break;
      case SQLUDF_TF_FINAL:
        break;
    }
  }


  public void tab_10Varchar(String inStmt,
                            short  numColumns,
                            int    rowNumber,
                            String outValue01,
                            String outValue02,
                            String outValue03,
                            String outValue04,
                            String outValue05,
                            String outValue06,
                            String outValue07,
                            String outValue08,
                            String outValue09,
                            String outValue10)
  throws Exception {
    switch (getCallType()) {
      case SQLUDF_TF_FIRST:
        break;
      case SQLUDF_TF_OPEN:
        try {
          rowNum   = 0;
          con      = DriverManager.getConnection
                     ("jdbc:default:connection");
          stmt     = con.createStatement();
          rs       = stmt.executeQuery(inStmt);
          rsmtadta = rs.getMetaData();
          colCount = rsmtadta.getColumnCount();
          if (colCount > 10) {
            colCount = 10;
          }
          for (i=1; i <= colCount; i++) {
            colType[i] = rsmtadta.getColumnTypeName(i);
            colSize[i] = rsmtadta.getColumnDisplaySize(i);
          }
        }
        catch(SQLException sqle) {
          setSQLstate("38999");
          setSQLmessage("SQLCODE = " + sqle.getSQLState());
          return;
        }
        break;
      case SQLUDF_TF_FETCH:
        rowNum++;
        if (rs.next() == true) {
          int i;
          set(2, (short)colCount);
          set(3, rowNum);
          for (i=1; i<=colCount; i++) {
            if (colType[i].equals("XML")         || 
                colType[i].equals("BLOB")        ||
                colType[i].equals("CLOB")        ||
                colType[i].equals("DBLOB")       ||
                colType[i].equals("GRAPHIC")     ||
                colType[i].equals("VARGRAPHIC")  ||
                colSize[i] > 254                 ||
                rs.getString(i) == null) {
              // DON'T DISPLAY THIS VALUE
              return;
            }
            else {
              // DISPLAY THIS COLUMN VALUE
              set(i+3, rs.getString(i));
            }
          }
        }
        else {
          setSQLstate ("02000");
        }
        break;
      case SQLUDF_TF_CLOSE:
        rs.close();
        stmt.close();
        con.close();
        break;
      case SQLUDF_TF_FINAL:
        break;
    }
  }


  // --------------------------------------------------------
  // ---     TRANSPOSE QUERY OUTPUT COLUMNS INTO ROWS     ---
  // ---                                                  ---
  // ---     These function accepts a SQL stmt (query) as ---
  // ---     input.  The query is executed and the output ---
  // ---     is transposed such that each column/row item ---
  // ---     becomes a row of output.                     ---
  // ---                                                  ---
  // ---     The output row has the following columns:    ---
  // ---       - row_number (of row fetched).             ---
  // ---       - num_columns (in query result).           ---
  // ---       - column_number (within row).              ---
  // ---       - column_name (if provided).               ---
  // ---       - column_type (source DB2 type).           ---
  // ---       - column_length (of column, not value).    ---
  // ---       - column_value (converted to VARCHAR).     ---
  // ---                                                  ---
  // ---     Two sample functions are provided:           ---
  // ---       - Return data up to 254 bytes long.        ---
  // ---       - Return data up to 4000 bytes long.       ---
  // ---     Columns that are longer than the max length, ---
  // ---     or of type XML, BLOB, CLOB, etc. are simply  ---
  // ---     ignored.  A null value is returned.          ---
  // ---                                                  ---
  // --------------------------------------------------------


  public void writeRow()
  throws Exception {
    set(2, rowNum);
    set(3, (short) colCount);
    set(4, colNum);
    set(5, colName[colNum]);
    set(6, colType[colNum]);
    set(7, colSize[colNum]);
    if (colType[colNum].equals("XML")         || 
        colType[colNum].equals("BLOB")        ||
        colType[colNum].equals("CLOB")        ||
        colType[colNum].equals("DBLOB")       ||
        colType[colNum].equals("GRAPHIC")     ||
        colType[colNum].equals("VARGRAPHIC")  ||
        colSize[colNum] > outLength) {
      // DON'T DISPLAY THIS VALUE
      return;
    }
    else if (rs.getString(colNum) !=  null) {
      // DISPLAY THIS COLUMN VALUE
      set(8, rs.getString(colNum));
    }
  }


  public void tab_Transpose(String inStmt
                           ,int    rowNumber
                           ,short  numColumns
                           ,short  outColNumber
                           ,String outColName
                           ,String outColtype
                           ,int    outColSize
                           ,String outColValue)
  throws Exception {
    switch (getCallType()) {
      case SQLUDF_TF_FIRST:
        break;
      case SQLUDF_TF_OPEN:
        try {
          con      = DriverManager.getConnection
                     ("jdbc:default:connection");
          stmt     = con.createStatement();
          rs       = stmt.executeQuery(inStmt);
          // GET COLUMN NAMES
          rsmtadta = rs.getMetaData();
          colCount = rsmtadta.getColumnCount();
          for (i=1; i <= colCount; i++) {
            colName[i] = rsmtadta.getColumnName(i);
            colType[i] = rsmtadta.getColumnTypeName(i);
            colSize[i] = rsmtadta.getColumnDisplaySize(i);
          }
          rowNum    = 1;
          colNum    = 1;
          outLength = 254;
        }
        catch(SQLException sqle) {
          setSQLstate("38999");
          setSQLmessage("SQLCODE = " + sqle.getSQLState());
          return;
        }
        break;
      case SQLUDF_TF_FETCH:
        if (colNum == 1 && rs.next() == true) {
          writeRow();
          colNum++;
          if (colNum > colCount) {
            colNum = 1; 
            rowNum++;
          }
        }
        else if (colNum > 1 && colNum <= colCount) {
          writeRow();
          colNum++;
          if (colNum > colCount) {
            colNum = 1; 
            rowNum++;
          }
        } 
        else {
          setSQLstate ("02000");
        }
        break;
      case SQLUDF_TF_CLOSE:
        rs.close();
        stmt.close();
        con.close();
        break;
      case SQLUDF_TF_FINAL:
        break;
    }
  }


  public void tab_Transpose_4K(String inStmt
                              ,int    rowNumber
                              ,short  numColumns
                              ,short  outColNumber
                              ,String outColName
                              ,String outColtype
                              ,int    outColSize
                              ,String outColValue)
  throws Exception {
    switch (getCallType()) {
      case SQLUDF_TF_FIRST:
        break;
      case SQLUDF_TF_OPEN:
        try {
          con      = DriverManager.getConnection
                     ("jdbc:default:connection");
          stmt     = con.createStatement();
          rs       = stmt.executeQuery(inStmt);
          // GET COLUMN NAMES
          rsmtadta = rs.getMetaData();
          colCount = rsmtadta.getColumnCount();
          for (i=1; i <= colCount; i++) {
            colName[i] = rsmtadta.getColumnName(i);
            colType[i] = rsmtadta.getColumnTypeName(i);
            colSize[i] = rsmtadta.getColumnDisplaySize(i);
          }
          rowNum    = 1;
          colNum    = 1;
          outLength = 4000;
        }
        catch(SQLException sqle) {
          setSQLstate("38999");
          setSQLmessage("SQLCODE = " + sqle.getSQLState());
          return;
        }
        break;
      case SQLUDF_TF_FETCH:
        if (colNum == 1 && rs.next() == true) {
          writeRow();
          colNum++;
          if (colNum > colCount) {
            colNum = 1; 
            rowNum++;
          }
        }
        else if (colNum > 1 && colNum <= colCount) {
          writeRow();
          colNum++;
          if (colNum > colCount) {
            colNum = 1; 
            rowNum++;
          }
        } 
        else {
          setSQLstate ("02000");
        }
        break;
      case SQLUDF_TF_CLOSE:
        rs.close();
        stmt.close();
        con.close();
        break;
      case SQLUDF_TF_FINAL:
        break;
    }
  }
}