Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2002
    Location
    Sacramento, CA
    Posts
    2

    Unanswered: JDBC Connection from Java scalar User Defined Function (UDF)

    My original post disappeared over the weekend, so this is a repost.

    I'm trying to get a JDBC Connection to the database from within a User Defined Function written in Java. My database is DB2 6.1.0

    Here is the call I'm making:

    Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
    Connection connect = DriverManager.getConnection("jdbc:db2:ALIASNAME", userId, password);

    Then I am trying to get a Statement from that Connection:

    Statement stmt = connect.createStatement();

    (I have also tried:
    PreparedStatement pstmt = conenct.prepareStatement(sqlString);
    )

    When I make either the createStatement or prepareStatement call, I throw an SQLException with CLI0601E which states, "Invalid statement handle or statement is closed."

    I have tested that the connection is open, by calling connect.isClosed(); which returns false. I have also called connect.toString() which dumps connection properties and looks valid.

    Is there a way to get a connection back to the database from within a user defined function?

    ----
    My latest attempt at this was to extend UDF which resulted in the following error:
    [IBM][CLI Driver] SQL0487N User defined function or procedure "" (specific name "") attempted to execute an SQL statement. SQLSTATE=38001

    What I'm trying to do is get a unique integer id value to use as an id for records in various tables. I was hoping to execute this as a UDF so that I could get the id on inserting from sql code or get the id ahead of time for use in programs executing outside of the database. I got this to work from a stored proc, but I don't know if there's a way to get a value from a stored proc from sql.

    I know that DB2 7.2 has something similar to Oracle's sequence which is exactly what I need, but my company can't justify the expense of upgrading from 6.1.
    Last edited by sethaniel; 08-20-02 at 12:21.

  2. #2
    Join Date
    Jun 2002
    Location
    Madrid, Spain
    Posts
    3
    JDBC Connection from Java scalar User Defined Function (UDF)

    Hi:

    I had the same problem and the same error message than you. Have you a solution yet? I've read Application Development Guide from the beggining and I don't know what is the problem. Maybe I'm not connected whe I try to create de statement?

    TIA.
    jiruiz

    This is the code of my UDF:


    public void pSQL(String b, String salida) throws Exception{
    try {
    Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").ne wInstance();
    }catch (Exception e){
    set(2, "Driver not load: " +e.getMessage());
    }

    Connection con = null;
    String url ="jdbc:db2IC001";
    try {
    con = DriverManager.getConnection(url,"SICGENE","SICGENE ");
    Statement stmt = con.createStatement();
    // set(2,"Conected");
    ResultSet rs = stmt.executeQuery("SELECT FECHA_INICONS FROM SIC.SICA003 WHERE BUSQUEDAS = '.'");
    set(2,rs.getString(1));
    rs.close();
    stmt.close();
    con.close();
    } catch(Exception e){
    set(2,"Consulta erronea: " + e.getMessage());
    }
    }

Posting Permissions

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