Error Handling

Error Handling

When an error occurs during stored procedure execution, the error is returned to the calling program and the stored procedure terminates. Error handling is necessary in order to avoid abnormal termination of stored procedures.

A MySQL error handler has three main parts: error condition, action and statements. An error handler specifies an error condition and the statements to execute followed by an action to be taken if the error occurs.

The error condition is the reason why the handler is invoked and can be one of the three: a MySQL error code, a SQLSTATE code or a user defined named condition associated with an error code or SQLSTATE value. MySQL has error codes that are unique to the MySQL Server. On the other hand ANSI has also defined error codes which are database independent. These are referred to as SQLSTATE error codes.

The action can either be to CONTINUE execution or EXIT the block or stored procedure. This action is taken after the statements defined in the handler are executed.

The last part of the handler is the statements to be executed once it is activated. Most of the time the statement is simply setting a variable value to be checked within the stored procedure. But in some cases multiple lines of code may be written within a BEGIN END block.

The DECLARE HANDLER statement is used to declare a handler. MySQL also has the option to define a name for an error condition. This facility makes the code readable because instead of using error codes that are difficult to remember, we can use easy to understand names. The DECLARE CONDITION statement is used to declare a named error condition by specifying a condition_name for a condition_value which can be a MySQL error code or SQLSTATE value.

Syntax

DECLARE CONTINUE | EXIT HANDLER FOR

MySQL error code| SQLSTATE code| condition_name

statements;

DECLARE condition_name CONDITION FOR condition_value;

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/57lesson.sh and wait for the MySQL prompt to start-up.