Skip to main content

signal

1. SIGNAL Function​

The SIGNAL function in H2 database is used to raise an exception with a specified SQLSTATE code and message.

2. Syntax​

The syntax for the SIGNAL function in H2 database is as follows:

SIGNAL SQLSTATE '<sqlstate>' SET MESSAGE_TEXT = '<message>';

Arguments​

  • <sqlstate>: The SQLSTATE code to be raised. It should be a valid SQLSTATE code. For example, '45000' can be used for a user-defined exception.
  • <message>: The error message associated with the exception. It should be a string enclosed in single quotes.

3. Notes​

  • The SIGNAL function in H2 database allows you to raise an exception with a specific SQLSTATE code and message.
  • The SQLSTATE codes are standardized and indicate different types of exceptions or errors. You can use standard codes or define custom codes for user-defined exceptions.
  • The SIGNAL function is typically used within a stored procedure or trigger to handle exceptional conditions.
  • Be cautious when using the SIGNAL function, as it can terminate the execution of the current statement or transaction.

4. Examples​

Here are a few examples demonstrating the usage of the SIGNAL function in H2 database:

Example 1 - Raising a user-defined exception:​

CREATE ALIAS RAISE_CUSTOM_EXCEPTION FOR "org.h2.tools.TriggerAdapter.signal";
CREATE OR REPLACE TRIGGER trig1 BEFORE INSERT ON my_table
CALL "org.h2.tools.TriggerAdapter"(
    'RAISE_CUSTOM_EXCEPTION',
    '45000',
    'Invalid operation: Custom exception'
);

INSERT INTO my_table VALUES (1, 'John');

Output:

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Invalid operation: Custom exception [45000-200]

Example 2 - Raising a standard exception:​

CREATE OR REPLACE PROCEDURE check_age(age INT)
BEGIN
    IF age < 18 THEN
        SIGNAL SQLSTATE '22000' SET MESSAGE_TEXT = 'Underage not allowed';
    END IF;
END;

CALL check_age(16);

Output:

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Underage not allowed [22000-200]

There are no directly related functions to the SIGNAL function in H2 database. However, it is often used in conjunction with stored procedures, triggers, and exception handling mechanisms.