I am trying to write a UDF which has "select" and "insert" actions that operate the data in DB2.

At first, I generated the jar file from a project (contains multiple classes) with the "export" function of Eclipse, when I just use "Java -jar udftest.jar" to run the jar file, it works well.

And then, I used the "sqlj.install_jar" to install the UDF. The UDF works pretty well when it only interact with some online REST service.

However, when I added in the code of database operations, which is encapsulated in another class, the UDF give me error: sqlstate： SQL4302N 38501.

Here is the create function statement that I have used:

----------------------------------------------------------------
CONNECT TO SAMPLE;
CREATE FUNCTION utest(ID VARCHAR(100), term VARCHAR(100))
RETURNS TABLE (terms VARCHAR(100))
EXTERNAL NAME 'utestjar:UDFtest!getTerms'
LANGUAGE JAVA
PARAMETER STYLE DB2GENERAL
NOT DETERMINISTIC
FENCED
READS SQL DATA
EXTERNAL ACTION
SCRATCHPAD
NO FINAL CALL
DISALLOW PARALLEL
NO DBINFO;
CONNECT RESET;
------------------------------------------------------------------------

I have worked on this problems for several days. Any help will be appreciated sincerely!

PS: the following is the code of the class that interact with database:

-------------------------------------------------------------------------


import java.util.ArrayList;
import java.util.Map;
import java.util.Set;
import java.util.Properties;
import java.util.Scanner;
import javax.swing.SwingUtilities;
import java.lang.*; // for String class
import COM.ibm.db2.app.*; // UDF and associated classes
import java.sql.*;
import java.io.*;
import org.apache.commons.lang.ArrayUtils;

public class CatchManager {


private Connection conn;

public CatchManager() {

String databaseURL = "jdbc:db2://localhost:50000/CATCHDB";

try {
Class.forName("com.ibm.db2.jcc.DB2Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

// Set user and password properties
Properties properties = new Properties();
properties.put("user", "ROOT");
properties.put("password", "a");

// Get a connection
try {
conn = DriverManager.getConnection(databaseURL, properties);

} catch (SQLException e) {

e.printStackTrace();
}

}


public ArrayList retrivalResult(String id, String type, String relation, String termID)
{

ArrayList resultList = new ArrayList();

try {

String query = "select * from " + dbTable + " where id = id +"' and relation = '"+relation+"' and START_TERM = '"+termID+"'";
PreparedStatement stmt = conn.prepareStatement(query);

ResultSet rs = stmt.executeQuery();

int i = 0;
while(rs.next())
{
resultList.add(rs.getString("END_TERM"));

}
resultList = new String[2][i];


rs.close();
stmt.close();
conn.close();

} catch (SQLException e) {

}

return resultList;
}

}
------------------------------------------------------------------------

You cannot connect to an external database from within a Java routine; you can only connect to the database where the UDF is running - see an example here: Provide UDFs to be called by UDFsqlcl.java

Thanks a lot for your information. I have a further question: so if the UDF connects to the database where the UDF is currently running, will such a structure (UDF call a function to connect database) works?

No. Like I said, you cannot connect to an external database from within a Java routine.

This is for your own safety: DB2 cannot guarantee transaction control if database connections are made behind its back.

Really really appreciate your help! Or I will try on a wrong direction for another week. I guess I should think about rewrite my code right now.

Thanks a lot! You are so patient and nice!

If you describe what you are trying to achieve, you will likely get a few suggestions.

yeah. So basically, I am trying to create a UDF, which can use some online REST service and also cache the result locally to improve efficiency when next time I need to query the same result.

So right now, the part of retrieving information online works well. But when the local cache part been added in, the UDF get the error that I mentioned above.

So, why do you cache data in a separate database? Wouldn't it be easier to store it in the same database where you have your UDF?

Yes, I would like to store the cache also in DB2, the database that I am running the UDF. The code that I show above is trying to establish a connection with the local DB2

The structure of the system looks like this:

The UDF class call the "adapter" class to get information.

The "adapter" class connect to rest service or CatchManager class (the code mentioned above) to get result and send it to UDF.

Without CatchManager class, it works. Using CatchManager to "select" or "insert" results in error.

Did you check the link that I provided earlier? It shows how to obtain a Connection object for the local database.

Yes. I have checked the code. I wonder that if call another class, instead of from UDF class directly, to connect db2 if possible or not.

Thanks a lot. I tried to adapt the sample code into the mode that I want to use, it works well. Even though my own code still cannot work in this mode, I will tried to make it.

Thanks a lot for your encouragement. Your patient makes me carry on, or I have already given up.

Sincerely thanks!