469,327 Members | 1,335 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,327 developers. It's quick & easy.

Java UDF: Checking if anything in scratchpad

Folks,

I've got a java UDF that uses a scratchpad (based on Knut's dW article on user-defined aggregate functions). The problem is that when it tries to read from the scratchpad on the first invocation of the UDF, it fails:

select myUdf(myColumn) from myTable

1

---------------------------------------------------------------------------
SQL4302N Procedure or user-defined function "LMENARD.MYUDF", specific name
"SQL051121193742200" aborted with an exception "". SQLSTATE=38501

The pertinent java code is:

public class myClass extends UDF
{
public void myMethod ( String inVar,
Blob intermResult )
throws Exception
{
// test for SQL NULLs in the input parameters and
// the structured value itself
if (isNull(1))
{
return;
}

// access the scratchpad and decode the previous
// intermediate result stored there
byte[] scratchpad = getScratchpad();
ByteArrayInputStream scratchIn = new ByteArrayInputStream(scratchpad);
DataInputStream dataIn = new DataInputStream(scratchIn);

// initialize variables
byte[] workBuffer = null;
ByteArrayOutputStream scratchOut = new ByteArrayOutputStream();
DataOutputStream dataOut = new DataOutputStream(scratchOut);

// Read the current scratchpad value
if (dataIn.read(workBuffer, 0, 32) == 0) // <-- It fails here
{
// Copy the current column value into workBuffer
dataOut.writeChars(inVar);
}
else
{
//dataOut.write(workBuffer, 0, 32);
dataOut.writeChars(inVar);
}
....

I've confirmed that the writeChars() calls work and the UDF functions as expected if done unconditionally (i.e., the "if (dataIn.read(...)" is commented out).

select myUdf(myColumn) from myTable

1

--------------------------------------------------------------------------------
---------------------------------------------------
x'003100320033'

x'003400350036'

x'003700380039'
3 record(s) selected.

So I believe the culprit is either the read() or the definition of workBuffer.

Column mycolumn is defined as VARCHAR(32) FOR BIT DATA, and contains 3 rows ('123', '456', and '789'). The UDF is cataloged as follows:

drop function myUdf (VARCHAR(32));
create function myUdf (VARCHAR(32))
external name 'myClass!myMethod'
returns varchar(64) for bit data
returns null on null input
fenced
not variant
no sql
parameter style db2general
language java
no external action
scratchpad -- size defaults to 100 bytes
disallow parallel
;

Any ideas why the read() is failing? The javadoc for the DataInputStream class says:

read(byte[] b, int off, int len)
Reads up to len bytes of data from the contained input stream into an array of bytes

... so I think my call is syntactically (if not logically) correct.

In Knut's article his input column is numeric, so he uses readInt(), and I presume that worked for him. ;-)

Appreciate any pointers. Thanks.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
Nov 23 '05 #1
2 1505
Hello.

You have to initialize workBuffer variable before using it.
Something like:
byte[] workBuffer = new byte[32];
instead of:
byte[] workBuffer = null;

Tip: you can use class variables instead of scratchpad - this is
significantly easier I think...

Sincerely,
Mark B.

The pertinent java code is:

public class myClass extends UDF
{
public void myMethod ( String inVar,
Blob intermResult )
throws Exception
{
// test for SQL NULLs in the input parameters and
// the structured value itself
if (isNull(1))
{
return;
}

// access the scratchpad and decode the previous
// intermediate result stored there
byte[] scratchpad = getScratchpad();
ByteArrayInputStream scratchIn = new ByteArrayInputStream(scratchpad);
DataInputStream dataIn = new DataInputStream(scratchIn);

// initialize variables
byte[] workBuffer = null;
ByteArrayOutputStream scratchOut = new ByteArrayOutputStream();
DataOutputStream dataOut = new DataOutputStream(scratchOut);

// Read the current scratchpad value
if (dataIn.read(workBuffer, 0, 32) == 0) // <-- It fails here
{
// Copy the current column value into workBuffer
dataOut.writeChars(inVar);
}
else
{
//dataOut.write(workBuffer, 0, 32);
dataOut.writeChars(inVar);
}
....


Nov 23 '05 #2
Ah, thank you Mark. I thought "byte" was a primitive datatype, I didn't
realize it was actually the "Byte" class that requires instantiation. (And
I wondered how java would know how much space to reserve for the array. ;-)

I'm not familiar with class variables, but will also look into it.
--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
<4.****@mail.ru> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Hello.

You have to initialize workBuffer variable before using it.
Something like:
byte[] workBuffer = new byte[32];
instead of:
byte[] workBuffer = null;

Tip: you can use class variables instead of scratchpad - this is
significantly easier I think...

Sincerely,
Mark B.

The pertinent java code is:

public class myClass extends UDF
{
public void myMethod ( String inVar,
Blob intermResult )
throws Exception
{
// test for SQL NULLs in the input parameters and
// the structured value itself
if (isNull(1))
{
return;
}

// access the scratchpad and decode the previous
// intermediate result stored there
byte[] scratchpad = getScratchpad();
ByteArrayInputStream scratchIn = new
ByteArrayInputStream(scratchpad);
DataInputStream dataIn = new DataInputStream(scratchIn);

// initialize variables
byte[] workBuffer = null;
ByteArrayOutputStream scratchOut = new ByteArrayOutputStream();
DataOutputStream dataOut = new DataOutputStream(scratchOut);

// Read the current scratchpad value
if (dataIn.read(workBuffer, 0, 32) == 0) // <-- It fails here
{
// Copy the current column value into workBuffer
dataOut.writeChars(inVar);
}
else
{
//dataOut.write(workBuffer, 0, 32);
dataOut.writeChars(inVar);
}
....

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Thomas | last post: by
4 posts views Thread by Rhino | last post: by
5 posts views Thread by jcccs05 | last post: by
6 posts views Thread by Rhino | last post: by
6 posts views Thread by Rhino | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by listenups61195 | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.