Add Error Handling to Stored Procedures

This guide will walk you through adding simple error handling to a stored procedure created in the Appian cloud database (MariaDB), allowing you to capture errors and warnings. Refer to the official documentation for your database if you're using one other than MariaDB, as the examples provided below may have syntax that only applies to MariaDB.

Example Setup

Our example stored procedure will be used to insert string values into a test table.

Create a simple test table:

CREATE TABLE test (name VARCHAR(10));

Create a simple stored procedure:

DELIMITER //

CREATE OR REPLACE PROCEDURE exampleProc(IN p_text VARCHAR(255))
BEGIN
  # Procedure body
  INSERT INTO test SELECT p_text;
END;
//

DELIMITER ;

SQLException Handling

First, create an error log table to store errors:

CREATE TABLE errorhandling (
  `id`             INT AUTO_INCREMENT,
  `procedureName`  VARCHAR(255),
  `errorText`      VARCHAR(5000),
  `errorNumber`    SMALLINT UNSIGNED,
  `sqlState`       VARCHAR(5),
  `created`        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

Next, add the following SQL statements after the “BEGIN” line to capture SQL Exceptions:

# Exception handling
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
  @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  # set procedure name variable for logging
  SET @proc_name = â`˜exampleProcâ`™;
  SET @error_text = CONCAT(â`œERROR: â`œ, @text);
  INSERT INTO errorhandling(`procedureName`, `errorText`, `errorNumber`, `sqlState`) VALUES (@proc_name, @error_text, @errno, @sqlstate);
END;

SQLWarning Handling

If you also want to capture warnings while your procedure is running, add the following SQL statements after the SQLException section:

# Warning handling
DECLARE EXIT HANDLER FOR SQLWARNING
BEGIN
  GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  # Set procedure name variable for logging
  SET @proc_name = â`˜exampleProcâ`™;
  SET @error_text = CONCAT(â`œWarning: â`œ, @text);
  INSERT INTO errorhandling(`procedureName`, `errorText`, `errorNumber`, `sqlState`) VALUES (@proc_name, @error_text, @errno, @sqlstate);
END;

Putting it All Together

Be careful when copying and pasting. Quotes and spaces may get converted between this document and a terminal, potentially breaking the query.

# Create procedure for inserting data into test table:
DELIMITER //

CREATE OR REPLACE PROCEDURE exampleProc(IN p_text VARCHAR(255))
BEGIN

  # Exception handling
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
    @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
    # Set procedure name variable for logging
    SET @proc_name = â`˜exampleProcâ`™;
    SET @error_text = CONCAT(â`œERROR: â`œ, @text);
    INSERT INTO errorhandling(`procedureName`, `errorText`, `errorNumber`, `sqlState`) VALUES (@proc_name, @error_text, @errno, @sqlstate);
  END;

  # Warning handling
  DECLARE EXIT HANDLER FOR SQLWARNING
  BEGIN
    GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
    # Set procedure name variable for logging
    SET @proc_name = â`˜exampleProcâ`™;
    SET @error_text = CONCAT(â`œWarning: â`œ, @text);
    INSERT INTO errorhandling(`procedureName`, `errorText`, `errorNumber`, `sqlState`) VALUES (@proc_name, @error_text, @errno, @sqlstate);
  END;

  # Procedure body
  INSERT INTO test SELECT p_text;

END;
//

DELIMITER ;

Testing

Call the example procedure with a value that fits into the column. It will work without any errors or warnings.

CALL exampleProc("TEST1");

Call the example procedure with a value that doesn’t fit into the column. It'll still work, but it'll write to the error handling table, indicating a warning and that the data was truncated.

MariaDB [AppianAnywhere]> select * from errorhandling;
+----+---------------+----------------------------------------------------+-------------+----------+---------------------+
| id | procedureName | errorText                                          | errorNumber | sqlState | created             |
+----+---------------+----------------------------------------------------+-------------+----------+---------------------+
|  1 | exampleProc   | Warning: Data truncated for column 'name' at row 1 |        1265 | 01000    | 2023-04-14 16:46:56 |
+----+---------------+----------------------------------------------------+-------------+----------+---------------------+
1 row in set (0.000 sec)

Advanced Error Handling

Some queries may have more than one condition for an exception or warning. If you want all of them to be written, you'll need to loop through them and insert them individually. You may want to apply the same approach to any other handlers you have. Please ensure you have read through the documentation about continue and exit handlers, as there are precedence rules for these handlers.

â`“ Example to get number of conditions 
GET DIAGNOSTICS @num_conditions = NUMBER;

â`“ Example loop through conditions
FOR i IN 1..@num_conditions
   DO
      GET DIAGNOSTICS CONDITION i @sqlstate = RETURNED_SQLSTATE,
      @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
      SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
      INSERT INTO errorhandling  VALUES (@proc_name, @full_error, current_timestamp);
END FOR;

More Resources

For more advanced error handling and MariaDB documentation, consult the following resources:

• DECLARE HANDLER: Construct to declare how errors are handled.
• SQLSTATE: A string that identifies a condition’s class and subclass.
• Diagnostics Area: The diagnostics area contains information about the error conditions produced by an SQL statement, as well as some information about the statement that generated them.
• GET DIAGNOSTICS: Copy information about the diagnostics area into variables.
• DECLARE CONDITION: For declaring a named error condition.
• SIGNAL: May be used to produce a custom error message.
• RESIGNAL: Used to send a SIGNAL again for the previous error.
• MariaDB Error Code Reference: MariaDB shares error codes with MySQL, while also adding a number of new error codes specific to MariaDB.