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

Why not post your code for others to see ?

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;
}
}
}

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
================================================== =====

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!!