Thursday, April 7, 2011

SQL naming must be used when calling user defined function in SQL Stored procedure on UDB DB2 for i5/OS

Today, I got a small tip, which let me aware that when SQL naming must be used when calling a Java User Defined Function in SQL stored procedure.

I wrote a UDF in Java for DB2 for i5/OS. A dummy code sample like below,

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
* @author Yiyu Jia
*/
public class DummyUDF {

 boolean debugtableOn = true;
 public static String helloJia(String name)throws SQLException, Exception {
  //Connection con = DriverManager.getConnection("jdbc:default:connection");
  return "hello" + name;
 }
}

Then, we register it as DB2 user defined function as below,
CREATE FUNCTION YOURSCHEMA.DummyUDF ( 
NAME VARCHAR(500) 
) 
RETURNS VARCHAR(1000)   
LANGUAGE JAVA 
SPECIFIC YOURSCHEMA.DummyUDF 
NOT DETERMINISTIC 
MODIFIES SQL DATA  
RETURNS NULL ON NULL INPUT 
EXTERNAL NAME 'DummyUDF.helloJia' 
PARAMETER STYLE JAVA ;

However, when I call it in either iNavigator or SQL stored procedure, I was reported error message, which says
SQL State: 42704
Vendor Code: -204
Message: [SQL0204] DummyUDF in *LIBL type *N not found. ...


The statement used to call DummyUDF is as below,
values(YOURSCHEMA/DummyUDF('Yiyu'));
Since my iNavigator is configured to use system naming. I use "/" to link the schema name and function name. However, this wont work!After research and trying, I found it is extremely simply to solve this problem. That is, using SQL naming instead of System naming to call UDF though iNavigator is set to use system naming already. Below code works,
values(YOURSCHEMA.DummyUDF('Yiyu'));

No comments:

Post a Comment