Raise signal with dynamic message_text

[Serman D.]

Environment is DB2/LINUX 9.5.0.

I wonder if it is possible to set the MESSAGE_TEXT dynamically when
using the SIGNAL statement. The objective is to return more specific
information to the calling user or application. E.g. I would rather
return "Unknown value 42" than just "Unknown value".

The documentation says:

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0004232.htm

SET MESSAGE_TEXT =

diagnostic-string-expression

An expression of type CHAR or VARCHAR that returns a character
string of up to 70 bytes to describe the error condition. If the
string is longer than 70 bytes, it is truncated.

So i thought something like this should work, but it does not (for me,
at least):

CREATE PROCEDURE customerror ()
BEGIN
DECLARE v_value CHAR(5) DEFAULT 'wrong';
DECLARE c_unknown CONDITION FOR SQLSTATE '90001';
SIGNAL c_unknown SET MESSAGE_TEXT = ('Unknown value ' | v_value);
END
@

Is it possible to set the MESSAGE_TEXT dynamically?

Regards,
Serman D.
--

[Serge Rielau]

Re: Raise signal with dynamic message_text

Serman D. wrote:

Environment is DB2/LINUX 9.5.0.
>
I wonder if it is possible to set the MESSAGE_TEXT dynamically when
using the SIGNAL statement. The objective is to return more specific
information to the calling user or application. E.g. I would rather
return "Unknown value 42" than just "Unknown value".
>
The documentation says:
>

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0004232.htm

>
SET MESSAGE_TEXT =
>
diagnostic-string-expression
>
An expression of type CHAR or VARCHAR that returns a character
string of up to 70 bytes to describe the error condition. If the
string is longer than 70 bytes, it is truncated.
>
So i thought something like this should work, but it does not (for me,
at least):
>
CREATE PROCEDURE customerror ()
BEGIN
DECLARE v_value CHAR(5) DEFAULT 'wrong';
DECLARE c_unknown CONDITION FOR SQLSTATE '90001';
SIGNAL c_unknown SET MESSAGE_TEXT = ('Unknown value ' | v_value);
END
@
>
Is it possible to set the MESSAGE_TEXT dynamically?

Of course. Simply compose the text in a local variable and then use that
variable in the SIGNAL.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab