Java UDF Problem

[Hakan Hacigumus]

Hello,

Since I want to populate some fields in “varchar(n) for bit data”, I am
trying to return a byte array from a UDF written in Java. (DB2 v7.1 with
fixpak 2 on Win NT with Service pack 6). According to the documentation,
the mapping between the Java types and the DB2 schema definition is
byte[] from java routine to Varchar(n) for bit data (or Char(n) for bit
data). So, when I define my Java udf as follows:

public void abc (String in, byte[] res)throws Exception
{
…
….
byte[] messbuf=………..
……..

set(2,messbuf);
}

I am getting the following compile error.

---------- Java Compiler ----------
C:\java:1116: Incompatible type for method. Can't convert byte[] to
COM.ibm.db2.app.Clob.
set(2,messbuf);
----------------------------------------

I changed the code, just by assuming the documentation is missing
something (based on the error message mentions about Clob). I defined an
internal Clob object send the values through that as follows:

public void abc (String in, byte[] res)throws Exception
{
…
….
COM.ibm.db2.app.Clob rB = COM.ibm.db2.app.Lob.newClob();
Writer out = rB.getWriter();

for (int i=0;i<messbuf.length ;i++ )
{
out.write(messbuf[i]);
}

set(2,rB);
}

Interestingly now this compiles without any error. I registered the UDF
by using the following:

CREATE FUNCTION abc (varchar(254))
RETURNS varchar(254) for bit data
EXTERNAL NAME 'test!abc'
LANGUAGE JAVA
PARAMETER STYLE db2general
DETERMINISTIC
NOT FENCED
NOT NULL CALL
NO SQL
NO EXTERNAL ACTION
DISALLOW PARALLEL;

No error is reported for this. But when I try to run the UDF I receive
the following error message from the command line:

SQL4306N Java stored procedure or user-defined function "HAKAN.ABC",
specific
name "SQL010212142011287" could not call Java method "abc", signature
"(Ljava/lang/String;LCOM/ibm/db2/app/B". SQLSTATE=42724

Any ideas?

Thanks in advance,

-Hakan Hacigumus

[Dirk Wollscheid]

Hi Hakan,

where did you find your information about the byte[] mapping?

The Application Development Guide, Appendix C, section "Supported SQL Types"
doesn't list byte[] anywhere. If you want to return VARCHAR/VARCHAR FOR BIT
DATA, you have to pass it in String/Blob (note this is a DB2 Blob, not a
Java 2 java.sql.Blob)

The compile error occurs, because there's no DB2Udf.set(byte[]) method.

In you second example, the second paramter should be a Blob and you have to
change the creation to
COM.ibm.db2.app.Blob rB = COM.ibm.db2.app.Lob.newBlob();

DB2 ships with examples for this.

Hope this helps, Dirk

--
Dirk Wollcheid, IBM Silicon Valley Labs
Websphere Database Development

Get DB2 help here: http://www-4.ibm.com/software/data/db2/library/
Get WebSphere help here: http://www-4.ibm.com/software/webservers/appserv/

"Hakan Hacigumus" <ha...@ics.uci.edu> wrote in message
news:3A886388...@ics.uci.edu...

> Hello,
>
> Since I want to populate some fields in "varchar(n) for bit data", I am
> trying to return a byte array from a UDF written in Java. (DB2 v7.1 with
> fixpak 2 on Win NT with Service pack 6). According to the documentation,
> the mapping between the Java types and the DB2 schema definition is
> byte[] from java routine to Varchar(n) for bit data (or Char(n) for bit
> data). So, when I define my Java udf as follows:
>
>
> public void abc (String in, byte[] res)throws Exception
> {
> .

> ..
> byte[] messbuf=.....
> ....

>
>
> set(2,messbuf);
> }
>
> I am getting the following compile error.
>
> ---------- Java Compiler ----------
> C:\java:1116: Incompatible type for method. Can't convert byte[] to
> COM.ibm.db2.app.Clob.
> set(2,messbuf);
> ----------------------------------------
>
> I changed the code, just by assuming the documentation is missing
> something (based on the error message mentions about Clob). I defined an
> internal Clob object send the values through that as follows:
>
> public void abc (String in, byte[] res)throws Exception
> {
> .

> ..

[Dan Scott]

Hakan got the mapping from the chapter in the Application Development
Guide called "Programming in Java", which contains information about
PARAMETER STYLE JAVA stored procedures and UDFs. However, in his example
CREATE FUNCTION statement, he's using PARAMETER STYLE DB2GENERAL, which,
as you pointed out, are documented with different mappings in the
"DB2DARI and DB2GENERAL Stored Procedures and UDFs" appendix.

Dan

[Hakan Hacigumus]

Yes, this is correct. I got that information from the chapter you referred. But
even if you define paramater style Java, it does not work with "byte[]/Varchar(n)
for bit data" mapping, because of compile error I mentioned earlier. Do you think
there is a problem in DB2 lib ?

I changed the definitions according the message from Dirk (thanks to him again). I
compiles and I can invoke the UDF. I am still having problem with the internal
conversion from byte[] to Blob. Since my UDF code process byte array, I have to
put everything into blob object at the end, in order to use it in "set(...)"
statement. I followed the guidelines which are given in one of the examples
shipped with DB2. But apperantly it does not put anything into blob and the result
becomes null after the execution.

Thanks for the help and time,

-Hakan

[Dirk Wollscheid]

Hakan,

the "set" method is only avalable in parameter style DB2GENERAL! (I admit
this is confusing.) In this case you derive your UDF class from DB2Udf and
inherit that method.

For parameter style JAVA would be (this is an approximation, look in the
SQLJ Part1 spec for details):

public static void abc (String in, byte[][] res)throws Exception

I think for output parameters you have an arry of the output type in the
signature. This is because the UDF allocates the objects or values and the
this will be passed back in the first element of the array. You would set
your output parameter of your UDF with

res[0]=<some byte[] value>;

About the DB2GENERAL/Blob problem:
in teh DB2Udf sample there's a method called getBlob that does exactly what
you do (return a result in a Blob). Does it work for you?
I would try to print the contents of your Blob to a file before you pass it
back from the UDF. I've used this mechanism a couple of times and would be
suprised if there's a problem in DB2.

Hope this helps, Dirk
--
Dirk Wollcheid, IBM Silicon Valley Labs
Websphere Database Development

Get DB2 help here: http://www-4.ibm.com/software/data/db2/library/
Get WebSphere help here: http://www-4.ibm.com/software/webservers/appserv/

"Hakan Hacigumus" <ha...@ics.uci.edu> wrote in message

news:3A897D8C...@ics.uci.edu...