Error handling

Error handling means ability to raise or catch some error conditions also known as exceptions in your stored procedure or function.

SQL standard says:

• Each SQLSTATE is a five-symbols string (digits or latin letters in upper case)
• First two symbols define class of state, the rest 3 define subclass. 000 subclass means unspecified one.
• Classes that have the first symbol as (0,1,2,3,4 and A, B, C, D, E, F, G, H) or HZ (special case) are reserved by standard.
• You can define sub-class-extensions for “standard” classes. In this case subclass must begin from (5,6,7,8,9, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, or Z).
• Your own classes must begin from (5,6,7,8,9, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, or Z). In this case subclass may begin from any allowed symbol.

NOTE: Valentina SQL allows only A-F letters to fit HEX values range!

You can choose any allowed SQL state to raise in the stored procedure. For this, you must use the reserved class 50 (user-defined SQL states), i.e. states that are in the range 50000 - 50FFF.

You can raise errors using RAISE (RAISEERROR) clause.

raise_stmt
    :    { RAISE | RAISEERROR } [exception_error_code [, message_string]]

exception_error_code
    :    UINT_HEX | err_name

err_name 
    :    IDENT

Example:

Raise regular Valentina Error

RAISE ERR_FIELD_NOT_UNIQUE;

Example:

Raise User Error

RAISE 50000, 'Some user exception.';
RAISE 50FFF, 'Some user exception2.';

DECLARE err LONG;
DECLARE errStr String; 
SET err = 50001;
SET errStr = 'Some user error';

RAISE err, errStr;

You can trap errors using BEGIN block with an EXCEPTION clause (special section in a block). When an error occurs, an exception is raised (by kernel self or by RAISE clause) normal execution stops and control transfers to this special section, which may come at the end of any block.

In the WHEN clause you can write any valid for stored procedures SQL statement, including RAISE error to throw the same or another exception. To pass the same exception, which was catched just use RAISE command without parameters.

Syntax

BEGIN
    statements
EXCEPTION
    [ WHEN exception_error_code [ OR exception_error_code ... ] THEN
         statement_list
      ... ]
    [ WHEN OTHERS THEN
         statement_list ]
END;

exception_error_code
    :    UINT_HEX | err_name

err_name 
    :    IDENT

Example:

This example catches an exception of UNIQUE violation by its number and PRINT notes about this. You should prefer to catch an exception by its symbolic name although.

CREATE TABLE t1 ( f1 LONG UNIQUE );

BEGIN
    INSERT INTO t1 VALUES(1);
    INSERT INTO t1 VALUES(1);
EXCEPTION
    WHEN 23503 THEN
        PRINT 'Unique violation'
END;

Example:

This example catches an exception by its symbolic name.

CREATE TABLE t1 ( f1 LONG UNIQUE );

BEGIN
    INSERT INTO t1 VALUES(1);
    INSERT INTO t1 VALUES(1);
EXCEPTION
    WHEN ERR_CONSTRAINT_UNIQUE_VIOLATION THEN
        PRINT 'Unique violation'
END;

Example:

This example shows how to catch several different exceptions.

CREATE TABLE t1 ( f1 LONG UNIQUE );

BEGIN
    INSERT INTO t1 VALUES(1);
    INSERT INTO t1 VALUES(1);
EXCEPTION
    WHEN 50001 THEN
        PRINT 'Some user state.'
    WHEN 50002 THEN
        PRINT 'Some user state2.'
    WHEN OTHERS THEN
        PRINT 'Any others errors are caught here.'
        RAISE; -- throw the same exception forward.
END;

Example:

This example shows how to catch ANY exception.

CREATE TABLE t1 ( f1 LONG UNIQUE );

BEGIN
    INSERT INTO t1 VALUES(1);
    INSERT INTO t1 VALUES(1);
EXCEPTION
    WHEN OTHERS THEN
        PRINT 'Any others errors are caught here.'
END;

Example:

This example shows how to work with exception in the nested BEGIN-END block.

CREATE TABLE t1 (f1 long);

CREATE OR REPLACE PROCEDURE SP1()
BEGIN
    declare t1_f1 LONG;
    DECLARE cur1 CURSOR FOR select f1 FROM T1;                          
    OPEN cur1;

    BEGIN
        -- If no records in the cursor the next line will throw
        -- ERR_CURSOR_WRONG_POSITION

        FETCH FIRST cur1 INTO t1_f1;

    EXCEPTION WHEN ERR_CURSOR_WRONG_POSITION THEN
        print 'Cursor.First Exception';
        LEAVE
    END
                                                             
    LOOP
        BEGIN
            FETCH NEXT cur1 INTO t1_f1;

        EXCEPTION WHEN ERR_CURSOR_WRONG_POSITION THEN
            print 'Cursor.Next Loop Exception';
            LEAVE
        END
    END LOOP

    close cur1; 	
END