469,903 Members | 1,595 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Executing sql UDF in java

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

Dec 5 '06 #1
2 1664
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
Dec 5 '06 #2
Hi Knut

Thanks. That works.

Cheers

Dec 5 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

By using this site, you agree to our Privacy Policy and Terms of Use.