Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - SQL Usage » Stored procedures, triggers, batches, and user-defined functions » Error and warning handling

 

Example: Creating an error logging procedure that can be called by an exception handler

You can define an error logging procedure that can be used in exception handlers across applications for uniform error logging.

  1. Create the following tables to log error information every time the error logging procedure is run.



    CREATE TABLE IF NOT EXISTS error_info_table (
       idx INTEGER, 
       In UNSIGNED INTEGER, 
       code INTEGER, 
       state CHAR(5),
       err_msg CHAR(256),
       name CHAR(257),
       err_stack LONG VARCHAR,
       traceback LONG VARCHAR
    );
    CREATE TABLE IF NOT EXISTS error_stack_trace_table (
       idx UNSIGNED SMALLINT NOT NULL,
       stack_level UNSIGNED SMALLINT NOT NULL,
       user_name VARCHAR(128),
       proc_name VARCHAR(128),
       line_number UNSIGNED INTEGER NOT NULL,
       is_resignal BIT NOT NULL, PRIMARY KEY (idx, stack_level)
    );
  2. Create the following procedure that logs the error information to the error_info_table and error_stack_trace_table and writes a message to the database server messages window:



    CREATE OR REPLACE PROCEDURE error_report_proc ( IN location_indicator INTEGER )
    NO RESULT SET
    BEGIN
       INSERT INTO error_info_table VALUES (
         location_indicator,
         ERROR_LINE(),
         ERROR_SQLCODE(), 
         ERROR_SQLSTATE(),
         ERROR_MESSAGE(),
         ERROR_PROCEDURE(),
         ERROR_STACK_TRACE(),
         TRACEBACK()
       );
       INSERT INTO error_stack_trace_table 
         SELECT location_indicator, *
         FROM sa_error_stack_trace() ;
       MESSAGE 'The error message is '|| ERROR_MESSAGE() ||' and the stack trace is '|| ERROR_STACK_TRACE()
       TYPE WARNING TO CONSOLE ;
    END; 
  3. Create a procedure similar to the following and invoke the error logging procedure from the exception handler.



    CREATE OR REPLACE PROCEDURE MyProc()
    BEGIN
       DECLARE column_not_found
         EXCEPTION FOR SQLSTATE '52003';
       MESSAGE 'Hello from MyProc.' TO CLIENT;
       SIGNAL column_not_found;
       MESSAGE 'Line following SIGNAL.' TO CLIENT;
    EXCEPTION
    WHEN column_not_found THEN
       MESSAGE 'Column not found handling.' TO CLIENT;
       CALL error_report_proc();
    END ;    
 See also