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/infoce...c/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]

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/infoce...c/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