Friday, December 23, 2011

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

  • 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:

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.


1 comment:

Justin Willey said...

Would be good - different language versions would be needed, though.

SQLBase has some nice functinality whereby standard error messages can be customised on a table-by-table basis to provide meaningful errors, eg foreign key errors explaining what is required rather than a generic primary key does not exist message


http://support.unify.com/Docs/SQLBaseDoc116/wwhelp/wwhimpl/js/html/wwhelp.htm#href=sqllang_ref_integrity.html#7592