Results 1 to 13 of 13
  1. #1
    Join Date
    May 2011
    Posts
    8

    Unhappy Unanswered: problem of connecting database from UDF

    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;
    }

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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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

  3. #3
    Join Date
    May 2011
    Posts
    8
    Quote Originally Posted by n_i View Post
    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?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  5. #5
    Join Date
    May 2011
    Posts
    8
    Quote Originally Posted by n_i View Post
    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!

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If you describe what you are trying to achieve, you will likely get a few suggestions.

  7. #7
    Join Date
    May 2011
    Posts
    8
    Quote Originally Posted by n_i View Post
    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.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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?

  9. #9
    Join Date
    May 2011
    Posts
    8
    Quote Originally Posted by n_i View Post
    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

  10. #10
    Join Date
    May 2011
    Posts
    8
    Quote Originally Posted by udfbuilder View Post
    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.

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Did you check the link that I provided earlier? It shows how to obtain a Connection object for the local database.

  12. #12
    Join Date
    May 2011
    Posts
    8
    Quote Originally Posted by n_i View Post
    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.

  13. #13
    Join Date
    May 2011
    Posts
    8
    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!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •