SIGNAL and RESIGNAL

In this lesson we will learn how to raise errors and warnings using SIGNAL and RESIGNAL statements.

We'll cover the following

SIGNAL and RESIGNAL

SIGNAL statement is a way to return an error to a handler or a client application. The sender can choose which error characteristics (like the error number or message text) to return. SIGNAL provides a way to handle unexpected events that may lead to application termination. By raising errors, we can provide information to the error handler for a graceful exit rather than an abrupt termination.

The SIGNAL keyword is used to raise errors. It is followed by a SQLSTATE value or a named condition defined with a SQLSTATE value. SIGNAL cannot be associated with a MySQL error code. The SQLSTATE value 45000 is generic and can be used to catch any un-handled user defined exceptions.

The SET clause is used to return information about the error. The diagnostic information about an error includes MYSQL_ERRNO, MESSAGE_TEXT, CLASS_ORIGIN, SUBCLASS_ORIGIN, CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CATALOG_NAME, SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, and CURSOR_NAME. The sender can choose to send more than one of these items as a comma separated list.

Syntax

SIGNAL SQLSTATE | condition_name

SET condition_information_item1 = val1, condition_information_item2 = val2, … ;

The RESIGNAL statement is similar to the SIGNAL statement. It is used to raise warnings and errors. RESIGNAL passes on the information about an error condition to a handler. The SQLSTATE value and the SET clause are optional and RESIGNAL can be used alone in which case it just passes the error without modifying the error information. When the SET clause is used with RESIGNAL, it is used to modify the error attributes like changing the error number or the message text. RESIGNAL can only be used within the scope of a condition handler while the SIGNAL statement can be used anywhere in a stored procedure.

Syntax

RESIGNAL [SQLSTATE | condition_name]

[SET condition_information_item1 = val1, condition_information_item2 = val2, …];

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/58lesson.sh and wait for the MySQL prompt to start-up.

Level up your interview prep. Join Educative to access 70+ hands-on prep courses.