Product Suggestion: User-Defined ERRORMSG()

Exception handling in SQL Anywhere procedures, triggers and events is quite powerful, of that there is no question.

When it comes to user-defined exceptions, however...

...not so much

Here's a demonstration:

• Procedure p1 calls procedure p2.
  

• Procedure p1 is called by an application (in this case ISQL).
  

• When p2 executes it uses the SIGNAL statement to raise a user-defined exception.
  

• That exception is captured by the EXCEPTION block in p2.
  

• The exception block in p2 then
  
  • displays the SQLCODE, SQLSTATE and ERRORMSG() on the database console log, and then
    
  • uses the RESIGNAL statement to pass the exception up to procedure p1.
    

• When the exception reaches p1 it is captured by the EXCEPTION block in p1.
  

• The exception block in p1 then
  
  • displays another message on the database console log and
    
  • once again uses the RESIGNAL statement to pass the exception up to the application.
    

• When the exception reaches the application (ISQL) a dialog box is displayed.
  

Here's the code:

CREATE PROCEDURE p1()
BEGIN
   DECLARE @sqlcode   INTEGER;
   DECLARE @sqlstate  VARCHAR ( 5 );
   DECLARE @errormsg  VARCHAR ( 32767 );

   CALL p2();

EXCEPTION

   WHEN OTHERS THEN

      SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;

      MESSAGE STRING ( 
         'EXCEPTION detected in p1() at ', 
         CURRENT TIMESTAMP, 
         ': SQLCODE = ', @sqlcode,  
         ', SQLSTATE = ', @sqlstate,  
         ', ERRORMSG() = ', @errormsg ) 
         TO CONSOLE;

      RESIGNAL;

END;

CREATE PROCEDURE p2()
BEGIN
   DECLARE @sqlcode   INTEGER;
   DECLARE @sqlstate  VARCHAR ( 5 );
   DECLARE @errormsg  VARCHAR ( 32767 );

   BEGIN
      DECLARE @exception EXCEPTION FOR SQLSTATE '99001';
      SIGNAL @exception;
   END;

EXCEPTION
   WHEN OTHERS THEN

      SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;

      MESSAGE STRING ( 
         'EXCEPTION detected in p2() at ', 
         CURRENT TIMESTAMP, 
         ': SQLCODE = ', @sqlcode,  
         ', SQLSTATE = ', @sqlstate,  
         ', ERRORMSG() = ', @errormsg ) 
         TO CONSOLE;

      RESIGNAL;

END;

CALL p1(); -- call from ISQL

Here's what appears in the database console log, followed by the dialog box:

EXCEPTION detected in p2() at 2011-12-22 15:16:54.381: SQLCODE = -297, SQLSTATE = 99001, ERRORMSG() = User-defined exception signaled
EXCEPTION detected in p1() at 2011-12-22 15:16:54.382: SQLCODE = -297, SQLSTATE = 99001, ERRORMSG() = User-defined exception signaled

That's all well and good, but the problem is this: only the SQLSTATE is user-defined. The SQLCODE and ERRORMSG() values are both fixed and pre-defined, and they mean exactly the same thing:

• SQLCODE = -297 User-defined exception signaled
  

• ERRORMSG() = User-defined exception signaled
  

Hey! We know that already!

In order to be truly useful, user-defined exceptions need some mechanism for providing a user-defined error message as well as a SQLSTATE code.

Even that pathetic limited Transact SQL RAISERROR statement has a mechanism for providing a user-defined message as well as an error code.

---