Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2003
    Posts
    30

    Unanswered: Sample Java Table UDF???

    Hi,

    i think i am going mad with this. I am just trying to write a simple java table udf returning an input parameter string as a table row. It just to learn hot to do it. The udf i wrote always ends up in infinitely returning the string.

    I read the IBM sample and tried to modify it for the purpose, but ended in the same problem.

    Can anybody provide such a simple udf ( Create + Java Code ) so i can see where my mistakes are.

    The call should look like this : select StringTable.result from table(GetString('HOHOHOHO')) as StringTable

    Any comments are welcome!!!!

    Thanks

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Why not post your code for others to see ?



    Quote Originally Posted by tmoeller
    Hi,

    i think i am going mad with this. I am just trying to write a simple java table udf returning an input parameter string as a table row. It just to learn hot to do it. The udf i wrote always ends up in infinitely returning the string.

    I read the IBM sample and tried to modify it for the purpose, but ended in the same problem.

    Can anybody provide such a simple udf ( Create + Java Code ) so i can see where my mistakes are.

    The call should look like this : select StringTable.result from table(GetString('HOHOHOHO')) as StringTable

    Any comments are welcome!!!!

    Thanks
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Apr 2003
    Posts
    30
    Hi,

    yes you are right! My first intention was to get some general examples it there were some.

    Here is the latest Version i tried to use:


    CREATE FUNCTION WINEX(LONG VARCHAR)
    RETURNS TABLE (result LONG VARCHAR)
    EXTERNAL NAME 'WindowsExec!getResult'
    LANGUAGE JAVA
    PARAMETER STYLE DB2GENERAL
    NOT DETERMINISTIC
    FENCED
    NO SQL
    NO EXTERNAL ACTION
    SCRATCHPAD
    NO FINAL CALL
    DISALLOW PARALLEL
    NO DBINFO;

    GRANT EXECUTE ON FUNCTION WINEX TO PUBLIC WITH GRANT OPTION;


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

    public class WindowsExec extends UDF {

    public void getResult(String str,
    String outStr
    )
    throws Exception
    {
    int intRow = 0;
    byte[] scratchpad = getScratchpad();

    // variables to read from SCRATCHPAD area
    ByteArrayInputStream
    byteArrayIn = new ByteArrayInputStream(scratchpad);
    DataInputStream
    dataIn = new DataInputStream(byteArrayIn);

    // variables to write into SCRATCHPAD area
    byte[] byteArrayRow;
    int i;
    ByteArrayOutputStream
    byteArrayOut = new ByteArrayOutputStream(10);
    DataOutputStream
    dataOut = new DataOutputStream(byteArrayOut);

    switch (getCallType())
    {
    case SQLUDF_TF_FIRST:
    // do initialization for the whole statement
    // (the statement may invoke tableUDF more than once)
    break;
    case SQLUDF_TF_OPEN:
    // do initialization valid for this invokation of tableUDF

    intRow = 1;
    // save data in SCRATCHPAD area
    dataOut.writeInt(intRow);
    byteArrayRow = byteArrayOut.toByteArray();
    for(i = 0; i < byteArrayRow.length; i++)
    {
    scratchpad[i] = byteArrayRow[i];
    }
    setScratchpad(scratchpad);
    break;
    case SQLUDF_TF_FETCH:
    // get data from SCRATCHPAD area
    intRow = dataIn.readInt();
    // work with data
    if(intRow > 1) //MOE
    {
    // Set end-of-file signal and return
    setSQLstate ("02000");
    }
    else
    {
    // Set the current output row and increment the row number
    set(2, str);
    intRow++;
    }

    // save data in SCRATCHPAD area
    dataOut.writeInt(intRow);
    byteArrayRow = byteArrayOut.toByteArray();
    for(i = 0; i < byteArrayRow.length; i++)
    {
    scratchpad[i] = byteArrayRow[i];
    }
    setScratchpad(scratchpad);
    break;
    case SQLUDF_TF_CLOSE:
    break;
    case SQLUDF_TF_FINAL:
    break;
    }
    }
    }

  4. #4
    Join Date
    Mar 2004
    Posts
    46
    It seems you cannot "return" a table.
    Here's what the Application Development Guide says -

    ================================================== =====
    Java routines
    When developing routines in Java™, it is strongly recommended that you register them using the PARAMETER STYLE JAVA clause in the CREATE statement. With PARAMETER STYLE JAVA, a routine will use a parameter passing convention that conforms to the Java language and SQLJ Routines specification.

    There are some UDF and method features that cannot be implemented with PARAMETER STYLE JAVA. These are as follows:
    - table functions
    - scratchpads
    ================================================== =====

  5. #5
    Join Date
    Apr 2003
    Posts
    30
    Hi,

    As you can see the UDF is created with parameter style DB2GENERAL, which is especially for returning tables and some other features.

    So this is not the reason.

    But, thanks for your comment!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •