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

Look at the SIGNAL statement.

Andy

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.

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

Hi,

Ok, thanks. I will give this a try.

TR.

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

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.

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