Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    5

    Unanswered: Connectivity Error in external java function with JDBC connectivity

    Hi All,

    I need to create a external jave user defined function in db2 (db2 9.5 and AIX 5.1).
    In my java class I need to establish a jdbc connectivity with the database .
    following is my java code

    import java.sql.*;
    public class TestDataInsert {
    public static String doInsertData(int id,String pcdata){
    Connection conn = null;
    String driver="com.ibm.db2.jcc.DB2Driver";
    String url="jdbc:db2://server_ip:50000/MYDB";
    String sql="insert into MYSCHEMA.MYTAB values(?,?)";
    String msg="data inserted";
    int status=0;
    /*get the database connection */


    try {
    Class.forName(driver);
    }catch (Exception e){
    msg=e.getLocalizedMessage();
    }
    try{
    conn =DriverManager.getConnection(url,"db2inst1","pass@ 123");
    System.out.println("after connection ");
    PreparedStatement st=conn.prepareStatement(sql);
    System.out.println("after prepared statement ");
    st.setInt(1,id);
    st.setString(2,pcdata);
    status=st.executeUpdate();
    if(status==1){
    conn.commit();
    conn.close();
    msg="data inserted";
    }
    } catch (Exception ex) {
    msg=ex.getLocalizedMessage();
    }
    return msg;
    }
    }

    This java class uses two jar files inorder to do the JDBC connectivity.
    Those are

    db2jcc.jar and db2jcc_license_cu.jar

    This java code is working properly from my java tool (Eclipse) and inserting data into the table also.
    i have created the external java user defined function as follows.


    CREATE FUNCTION "MYSCHEMA"."JAVAFUN_INS" (
    "ID" INTEGER,
    "NAME" VARCHAR(50) )
    RETURNS VARCHAR(20)
    SPECIFIC "INS"
    LANGUAGE JAVA
    EXTERNAL NAME 'TestDataInsert.doInsertData'
    PARAMETER STYLE JAVA
    DETERMINISTIC
    NOT FENCED
    THREADSAFE
    NO EXTERNAL ACTION
    NO SQL
    RETURNS NULL ON NULL INPUT
    NO SCRATCHPAD
    NO FINAL CALL
    ALLOW PARALLEL
    NO DBINFO
    INHERIT SPECIAL REGISTERS;

    I involked the UDF as follows:

    select MYSCHEMA.JAVAFUN_INS(1,'test') from sysibm.sysdummy1

    It throws me an error as follows.

    No suitable driver

    But i have done the following settings also like,

    I put the .class file in /home/db2inst1/sqllib/function
    I put both the jar files in /home/db2inst1/sqllib/function

    and I have set the classpath as follows

    /home/db2inst1/sqllib/java/db2java.zip:/home/db2inst1/sqllib/java/db2jcc.jar:
    /home/db2inst1/sqllib/java/sqlj.zip:/home/db2inst1/sqllib/function/db2jcc.jar:
    /home/db2inst1/sqllib/function/db2jcc_license_cu.jar:/home/db2inst1/sqllib/java/db2jcc_license_cu.jar

    Still it is unable to find the driver.

    Can any one help me out?
    Thanks in advance.

    Ratheesh Nellikkal


    Last edited by ratheeshnellikkal; 08-29-09 at 01:46.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Check the manual for Java UDF examples - you are missing a few points.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Mar 2009
    Posts
    5
    Thanks Nick,
    Could u plz suggest me some usefull url for the same?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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