Exception handling in db2 UDF

[mailar]

Hi,
Can someone tell me how to perform exception handling in DB2 UDFs(not
procedures).
ALso, while usin the SIGNAL statement in one of my UDFs I encountered
an error message which is as below

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "HANDLER" was found following "E INT;
DECLARE
EXIT". Expected tokens may include: "CONDITION". LINE NUMBER=12.
SQLSTATE=42601
The code that i used in my UDF is
DECLARE EXIT HANDLER FOR SQLSTATE VALUE '23503'
SIGNAL SQLSTATE '75002'
SET MESSAGE_TEXT = 'Customer number is not known';
So, Can anyone help me with this?

Thanks and regards
mailar

[amurchis]

SQL UDFs cannot contain any commands that are outside the scope of the
SQL reference. DB2 will take an SQL UDF and stick it directly into the
calling SQL, so that when it's executed it's indistinguishable from the
rest of the statement, as far as the engine is concerned.

An SQL Procedure, on the other hand, is completely separate entity,
which contains it's own set of information specific to it's execution.
As such, the SQL/PL language has directives that are NOT found in the
SQL reference, as they are specific to the procedur processing. I
believe "DECLARE EXIT HANDLER" is one of them.

ma****@gmail.com wrote:

Hi,
Can someone tell me how to perform exception handling in DB2 UDFs(not
procedures).
ALso, while usin the SIGNAL statement in one of my UDFs I encountered
an error message which is as below

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "HANDLER" was found following "E INT;
DECLARE
EXIT". Expected tokens may include: "CONDITION". LINE NUMBER=12.
SQLSTATE=42601
The code that i used in my UDF is
DECLARE EXIT HANDLER FOR SQLSTATE VALUE '23503'
SIGNAL SQLSTATE '75002'
SET MESSAGE_TEXT = 'Customer number is not known';
So, Can anyone help me with this?

Thanks and regards
mailar

[mailar]

Hi amurchis,
But does it mean that we cannot do error handling in UDFs?
Meanwhile I found a function RAISE_ERROR which raises an SQL state and
displays my error message
but the format of the message is like this

SQL0438N Application raised error with diagnostic text: "Invalid
customer number".
SQLSTATE=70001
but I just want 'Invalid customer number" to be displayed instead of
the whole message. So, could you help me with this

Thanks for the reply
mailar

amurchis wrote:

SQL UDFs cannot contain any commands that are outside the scope of the SQL reference. DB2 will take an SQL UDF and stick it directly into the calling SQL, so that when it's executed it's indistinguishable from the rest of the statement, as far as the engine is concerned.

An SQL Procedure, on the other hand, is completely separate entity,
which contains it's own set of information specific to it's execution. As such, the SQL/PL language has directives that are NOT found in the SQL reference, as they are specific to the procedur processing. I
believe "DECLARE EXIT HANDLER" is one of them.

ma****@gmail.com wrote:

Hi,
Can someone tell me how to perform exception handling in DB2 UDFs(not procedures).
ALso, while usin the SIGNAL statement in one of my UDFs I encountered an error message which is as below

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "HANDLER" was found following "E INT;
DECLARE
EXIT". Expected tokens may include: "CONDITION". LINE NUMBER=12.
SQLSTATE=42601
The code that i used in my UDF is
DECLARE EXIT HANDLER FOR SQLSTATE VALUE '23503'
SIGNAL SQLSTATE '75002'
SET MESSAGE_TEXT = 'Customer number is not known';
So, Can anyone help me with this?

Thanks and regards
mailar

[amurchis]

Even in a stored procedure, you'd still get a message similar to what
you see below. I believe it's something like "procedure <name>,
specific name <specname> returned sqlstate <your state> with message
text "<your text>", but don't hold me to that.

Error handling in an SQL UDF must be done strictly with SQL commands.

FYI: I am NOT an expert in SQL. The only thing I can suggest is that
you check the SQL reference for CREATE FUNCTION (SQL) for what commands
are available.

ma****@gmail.com wrote:

Hi amurchis,
But does it mean that we cannot do error handling in UDFs?
Meanwhile I found a function RAISE_ERROR which raises an SQL state and
displays my error message
but the format of the message is like this

SQL0438N Application raised error with diagnostic text: "Invalid
customer number".
SQLSTATE=70001
but I just want 'Invalid customer number" to be displayed instead of
the whole message. So, could you help me with this

Thanks for the reply
mailar

amurchis wrote:

SQL UDFs cannot contain any commands that are outside the scope of

the

SQL reference. DB2 will take an SQL UDF and stick it directly into

the

calling SQL, so that when it's executed it's indistinguishable from

the

rest of the statement, as far as the engine is concerned.

An SQL Procedure, on the other hand, is completely separate entity,
which contains it's own set of information specific to it's

execution.

As such, the SQL/PL language has directives that are NOT found in the

SQL reference, as they are specific to the procedur processing. I
believe "DECLARE EXIT HANDLER" is one of them.

ma****@gmail.com wrote:

Hi,
Can someone tell me how to perform exception handling in DB2
UDFs(not
procedures).
ALso, while usin the SIGNAL statement in one of my UDFs I
encountered
an error message which is as below

DB21034E The command was processed as an SQL statement because it
was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "HANDLER" was found following "E INT;
DECLARE
EXIT". Expected tokens may include: "CONDITION". LINE NUMBER=12.
SQLSTATE=42601
The code that i used in my UDF is
DECLARE EXIT HANDLER FOR SQLSTATE VALUE '23503'
SIGNAL SQLSTATE '75002'
SET MESSAGE_TEXT = 'Customer number is not known';
So, Can anyone help me with this?

Thanks and regards
mailar