0

I want to treat all types of generated signals in my code. I know how to catch a specific one with a Declare Handler, however I would like all of them.

For example, here I catch the 72822 signal.

DECLARE EXIT HANDLER FOR SQLSTATE '72822'
  BEGIN
    IF ( var = 'OK' )
      RESIGNAL SQLSTATE '72623' SET MESSAGE_TEXT = 'Got SQLSTATE 72822';
    ELSE
      RESIGNAL SQLSTATE '72319' SET MESSAGE_TEXT = var;
  END;

I will call a stored procedure that I do not know the code, and I have to be able to deal with all possible signals (for example insert the signal code in a table.)

I would like something like

DECLARE EXIT HANDLER FOR ALL
AngocA
  • 7,655
  • 6
  • 39
  • 55

1 Answers1

3

That would be DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, I think.

http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0004239.html

mustaccio
  • 18,234
  • 16
  • 48
  • 57
  • 2
    The answer above is a way to do this. I would recommend, however, that you separate the handlers (depending on the application). You would want an exit handler for the exceptions, but you will probably want a continue handler for the warnings. DECLARE CONTINUE HANDLER FOR SQLWARNING This is helpful because you do not always want to exit when there is simply a conversion. – jgriffin Feb 10 '14 at 15:58
  • @mustaccio It is just a question, are you interested to develop an open source framework for unit test in SQL PL? contact me if you are interested: angoca at yahoo dot com – AngocA Feb 10 '14 at 16:15
  • Agree with @jgriffin. Continue handlers can be useful for exceptions as well, because you may want to recover from certain types of errors (e.g. create a table if it didn't exist). –  Jun 30 '14 at 07:43