Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2008
    Posts
    3

    Unanswered: how can i call a simple sql pl-defined function using jdbc?

    hello forum,

    here are some details about my system:
    Code:
    DB21085I  Instance "DB2" uses "32" bits and DB2 code release "SQL08023" with
    level identifier "03040106".
    Informational tokens are "DB2 v8.1.10.812", "s050811", "WR21362", and FixPak
    "10".
    i created - in the db2 development center - a simple scalar sql pl-based udf that returns the next sequence number generated by a sequence object:
    Code:
    CREATE FUNCTION suncertified.get_next_trans_id(  )
        RETURNS INTEGER
        SPECIFIC suncertified.get_next_trans_id
    ------------------------------------------------------------------------
    -- SQL UDF (Scalar)
    ------------------------------------------------------------------------
    F1: BEGIN ATOMIC
       DECLARE aVar INTEGER;
        set aVar = nextval for iau_transaction_seq;
       RETURN aVar;
    END
    the function runs perfectly in the db2 development center. it also runs perfectly from the db2 command line processor:
    Code:
       db2 =>select suncertified.get_next_trans_id() from sysibm.sysdummy1 
       
       1
       -----------
                   12
    
       1 record(s) selected.
    so, i'd expect it to run with the following code from a java app:
    Code:
    ...
    Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
    Connection conn = DriverManager.getConnection("jdbc:db2:FOO","suncertified, "********");
    CallableStatement func = conn.prepareCall(" {? = call suncertified.get_next_trans_id()} ");
    func.registerOutParameter(1, java.sql.Types.INTEGER);
    func.execute();
    int xid = func.getInt(1);
    ...
    i'm doing the exact same thing as above in an oracle db (except for the db2-specific stuff, of course), and it works perfectly. but with db2, the above code results in:
    Code:
    COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0444N  Routine "*trans_id" (specific name "SQL080622121524410") is implemented with code in library or path "\get_next_trans_id", function "get_next_trans_id" which cannot be accessed.  Reason code: "4".  SQLSTATE=42724
    i've been wrestling with this for the past 18 hours! i've googled my brains out searching for a solution. i've found this:
    ...Reason code: "4". SQLSTATE=42724...
    Code:
    The file in library-or-path could not be found. See the routine creator or your database administrator. The routine definition or the location of the routine may need to be corrected, or the routine may need to be re-linked. In a partitioned database, the file must be accessible as "<library-or-path>" on all partitions of the database.
    
    In addition to the above, this reason code can result if the routine requires a shared library or DLL, and the shared library cannot be located (using the concatenation of directories specified in the LIBPATH environment variable in UNIX-based systems, the PATH environment variable in INTEL systems). There can be multiple levels of this indirection involved, leading to this reason code. For example, routine body X can be found, and it needs shared library Y which can also be found. But Y needs Z, and Z cannot be located, and this will result in SQL0444N reason code 4.
    it's just a simple function! there ain't no shared libraries or dlls involved! how come i can't call it from java like i can with an analogous function defined in an oracle db? please, don't tell me that i have to go through all the rigamarole described in Building JDBC Routines like what's done in the examples in $DB2/SQLLIB/samples/java/jdbc!

    thanks in advance for your replies.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why you issued
    "call suncertified.get_next_trans_id()" in your java app?
    you did
    "select suncertified.get_next_trans_id() from sysibm.sysdummy1" from development center.
    It is different from that you did in your java app.

    SQL functions of DB2 can not be called. They can be used in(as? I'm sorry my poor English capability.) an expression in an SQL statement.

    Please try
    (1) change your app to equivalent to what you did from development center(it might be something like: SELECT ... INTO ...).

    (2) If (1) didn't solve your problem, drop function(s) and try your java app again.
    1) drop function(s) (or PROCEDURE?):
    DROP SPECIFIC FUNCTION[or PROCEDURE?] "SQL080622121524410"
    and
    DROP FUNCTION[or PROCEDURE?] trans_id

    2) then try again.your java app and see the message.
    It must be something different from the current message which you got.

  3. #3
    Join Date
    Jun 2008
    Posts
    3

    Thumbs up Re: how can i call a simple sql pl-defined function using jdbc?

    Quote Originally Posted by tonkuma
    Why you issued
    "call suncertified.get_next_trans_id()" in your java app?..
    because (1) that is how i ordinarily call functions in oracle and (2) that is what is suggested on this page which i referred to when i started this project:

    Calling a Function in a Database

    Quote Originally Posted by tonkuma
    SQL functions of DB2 can not be called. They can be used in(as? I'm sorry my poor English capability.) an expression in an SQL statement.
    ah! i see! (said the blind man ) that must be just a db2 thing. right? because in oracle, functions are callable statements that return a value. and btw, your english is superb!
    Quote Originally Posted by tonkuma
    Please try
    (1) change your app to equivalent to what you did from development center...
    thanks, tonkuma! your first suggestion worked perfectly! instead of "{? = call suncertified.get_next_trans_id }", now i do a select like i did from the clp:
    Code:
    ...
    Statement stmt = conn.createStatement();
    ResultSet result = stmt.executeQuery("select get_next_trans_id() from sysibm.sysdummy1");
    result.next();
    int xid = result.getInt(1); 
    ...
    sincerely grateful for your help, tonkuma. take care

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In DB2 you call a Stored Procedure, not a User Defined Function.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    In Oracle(referencing Oracle® Database SQL Reference 10.2 ),
    the (simplified)syntax of call function is
    Code:
    CALL routine_clause([argument[,argument[...]]])
     INTO :host_variable [[INDICATOR] :indicator_variable];
    
    routine_clause
    Specify the name of the function or procedure being called,
     or a synonym that resolves to a function or procedure.
    
    Restriction on Functions
     If the routine is a function, then the INTO clause is required.
    In DB2, you can use VALUES INTO statement to get result value(s) of function(s).
    Code:
    VALUES expression | (expression[,expression[...]])
     INTO host-variable[,host-variable[...]];
    
    host-variable:
     Identifies a variable that is described in the program
     under the rules for declaring host variables.
    Note: function is a kind of expression.

    I think that there are no essential difference between Oracle and DB2 except the name of statement(CALL and VALUES).

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by sun-certified
    ah! i see! (said the blind man ) that must be just a db2 thing. right?
    No, it is a standard SQL thing.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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