How to Implement a Database in MySQL for HTML5 and CSS3 Programming
How to Run a Script with phpMyAdmin in SQL for HTML5and CSS3 Programming
How to Use SQL with Microsoft Access

How to Handle SQL Exceptions

When trying to determine the source of an SQL error and SQLSTATE indicates an exception condition by holding a value other than 00000, 00001, or 00002, you may want to handle the situation in one of the following ways:

  • Return control to the parent procedure that called the subprocedure that raised the exception.

  • Use a WHENEVER clause to branch to an exception-handling routine or perform some other action.

  • Handle the exception on the spot with a compound SQL statement. A compound SQL statement consists of one or more simple SQL statements, sandwiched between BEGIN and END keywords.

The following is an example of a compound-statement exception handler:

BEGIN
DECLARE ValueOutOfRange EXCEPTION FOR SQLSTATE'73003' ;
 INSERT INTO FOODS
  (Calories)
  VALUES
  (:cal) ;
 SIGNAL ValueOutOfRange ;
 MESSAGE 'Process a new calorie value.'
 EXCEPTION
  WHEN ValueOutOfRange THEN
   MESSAGE 'Handling the calorie range error' ;
  WHEN OTHERS THEN
RESIGNAL ;
END

With one or more DECLARE statements, you can give names to specific SQLSTATE values that you suspect may arise. The INSERT statement is the one that might cause an exception to occur. If the value of :cal exceeds the maximum value for a SMALLINT data item, SQLSTATE is set to “73003”. The SIGNAL statement signals an exception condition. It clears the top diagnostics area.

It sets the RETURNED_SQLSTATE field of the diagnostics area to the SQLSTATE for the named exception. If no exception has occurred, the series of statements represented by the MESSAGE ‘Process a new calorie value’ statement is executed. However, if an exception has occurred, that series of statements is skipped, and the EXCEPTION statement is executed.

If the exception was a ValueOutOfRange exception, then a series of statements represented by the MESSAGE ‘Handling the calorie range error’ statement is executed. The RESIGNAL statement is executed if the exception isn’t a ValueOutOfRange exception.

RESIGNAL merely passes control of execution to the calling parent procedure. That procedure may have additional error-handling code to deal with exceptions other than the expected value-out-of-range error.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
How to Calculate Date Values in SQL Data for HTML5and CSS3 Programming
Reliably Retrieving Data with SQL
How to Use Normalization in SQL for HTML5and CSS3 Programming
How to Use SQL:2011 Times and Periods
How to Use CAST Data-Type Conversions with SQL
Advertisement

Inside Dummies.com