Executing sql UDF in java

[Mahesh S]

Hi

I have created a UDF which sits in db2 database.

I want to call that UDF from within a java program and capture the
result.

The UDF takes a string parameter and returns a double value.

This is the code I came up with but it doesnt work and I dont know
whats wrong with it.

CallableStatement cstmt = DB2Connection.getConnection().prepareCall("{?
= CALL HEALTHCAREDB.RISK_EQUATION_1(?)}");

cstmt.registerOutParameter(1,Types.DOUBLE);
cstmt.setString(2, rs.getString("PATIENT_ID"));
cstmt.execute();

Here is the error message:
com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -440, SQLSTATE:
42884, SQLERRMC: HEALTHCAREDB.RISK_EQUATION_1;PROCEDURE
at com.ibm.db2.jcc.a.rf.e(rf.java:1680)
at com.ibm.db2.jcc.a.rf.a(rf.java:1239)
at com.ibm.db2.jcc.b.jb.h(jb.java:139)
at com.ibm.db2.jcc.b.jb.d(jb.java:71)
at com.ibm.db2.jcc.b.w.d(w.java:54)
at com.ibm.db2.jcc.b.cc.j(cc.java:207)
at com.ibm.db2.jcc.a.rf.o(rf.java:1236)
at com.ibm.db2.jcc.a.sf.ib(sf.java:1805)
at com.ibm.db2.jcc.a.sf.d(sf.java:2264)
at com.ibm.db2.jcc.a.tf.Z(tf.java:159)
at com.ibm.db2.jcc.a.tf.execute(tf.java:142)
at Test.main(Test.java:24)

Could you please tell me whats wrong with teh java code?

Thanks
Mahesh

[Knut Stolze]

Mahesh S wrote:

> Hi
>
> I have created a UDF which sits in db2 database.
>
> I want to call that UDF from within a java program and capture the
> result.
>
> The UDF takes a string parameter and returns a double value.
>
> This is the code I came up with but it doesnt work and I dont know
> whats wrong with it.
>
> CallableStatement cstmt = DB2Connection.getConnection().prepareCall("{?
> = CALL HEALTHCAREDB.RISK_EQUATION_1(?)}");

CALL is used to invoke stored procedures. UDFs are to be used like other
built-in functions and not with the CALL statement. To test a UDF (or to
use it stand-alone), you can employ the VALUES statement:

VALUES your_udf(parameter)

Note that you may have to CAST your parameter if you use parameter markers.
Otherwise, DB2's function resolution does not know which function (depends
on the signature) you really want to use. The cast provides the necessary
type information.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Mahesh S]

Hi Knut

Thanks. That works.

Cheers