Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2010
    Posts
    7

    Question Unanswered: return SQLSTATE from User Defined Function (Java)

    Hi,

    I am interested in developing Java user defined functions. I am wondering if it is possible to set some SQLSTATE numbers in that UDF when some custom error condition is satisfied. Say, I want to make sure that some integer parameter is within a specific range. If I determine this is not the case I would prefer to terminate the UDF immediately.

    Would it be ok just to throw a Java Exception? That surely makes the call fail but I am not sure if this is elegant enough . Would the following be possible (?):

    if(error condition satisfied) {
    setSQLstate(....); // some number, but which one
    setSQLmessage("number not within bounds");
    }

    I have heard of some SQLSTATE numbers that are "reserved" for user implementations, but I have found no documentation on this.

    We use "DB2 v9.5.0.4", "s090429", "MI00303", and Fix Pack "4".

    Regards,
    TR

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Look at the SIGNAL statement.

    Andy

  3. #3
    Join Date
    Sep 2010
    Posts
    7

    sporiss 1940:

    Hi,

    Thanks for your reply . I have found a webpage that shows some examples exhibiting the signal statement in stored procedures/functions (see: DB2 UDFs to Ease Migration to DB2: Fun with Bits, Booleans and Bitwise Operations).

    Two questions remain.

    (1) I am not sure how to call the signal statement from a Java UDF (derived from the Java class COM.ibm.db2.app.UDF provided by DB2). I believe I have to use the methods setSQLstate() and setSQLmessage() somehow.

    (2) Also, on that webpage they use the code '77701'. Is this one of those codes that could be reserved for applications? (Is there a list of these, so I can pick the one that suits my application's semantics?).

    Thanks

    TR.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    1) Yes you use those two methods to signal the error.
    2) There is no "List". You make it up. Anything beginning with 7,8,9 is for you to use.

    Andy

  5. #5
    Join Date
    Sep 2010
    Posts
    7
    Hi,

    Ok, thanks. I will give this a try.

    TR.

  6. #6
    Join Date
    Sep 2010
    Posts
    7

    Thumbs up

    Hi,

    I tried your suggestions. However, as I show below I get errors when I execute my UDF.

    I have a Java helper method that checks if the error condition has occured and if so it fires some SQL state. Here is a snippet:

    public void helpFunction(UDF udf) {

    boolean ok = ...

    if(!ok) {
    udf.setSQLstate("70000"); // own SQL state code
    udf.setSQLmessage("invalid state: ...");

    }

    }

    If I don't make these calls to the UDF and I provide valid input to the UDF everything works fine. However, I want to elegantly exit the UDF call when the input is not valid. When I make the setSQLstate() and setSQLmessage() calls, I get the following error when I execute my UDF from within a SQL statement:

    SQL4302N Procedure or user-defined function "...", specific name
    "...." aborted with an exception "Error in UDF setSQLstate.". SQLSTATE=38501

    Apparently the calls to the Java UDF type throw an Exception. When I catch that exception and print the message separately I get the same "Error in UDF setSQLstate". The exception stacktrace also just stops in my own code, not showing any DB2 internals that could be diagnostic.

    So, I am still quite puzzled as to what the problem is. I got the same problem when I called the setSQLstate and setSQLmessage methods from within the subclass that is derived from the DB2 UDF type.

    EDIT: when I call the setSQLstate() method with the string "02000" everything is fine too. This is described on the following link:http://publib.boulder.ibm.com/infoce.../r0008987.html. Thus, apparently there must be something wrong the the states that I customly created?

    Any suggestions?

    Regards,
    TR
    Last edited by tuplerecord; 09-21-10 at 07:34. Reason: (forgot something)

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I don't remember the exact details, but SQLSTATEs returned from external functions must either begin with 01H (for warning conditions) or 38 (for error conditions). I couldn't quickly find the the restrictions in the reference, though.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Sep 2010
    Posts
    7

    Smile

    Hi,

    Thank you The problem is solved now. I have found the entries about the warning and error codes in the pdf user manual called "Message Reference, Volume 2".

    Regards,
    TR

Tags for this Thread

Posting Permissions

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