From: Peter Brawley
Date: August 19 2005 2:51pm
Subject: Re: Trigger exception handling
List-Archive: http://lists.mysql.com/mysql/188048
Message-Id: <4305F1FC.8060904@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Dan,

See http://dev.mysql.com/doc/mysql/en/declare-handlers.html. Here's a 
wee example...

USE test ;
SET @err = 0 ;
SELECT 'Before running errhandlerdemo:', @err ;
CREATE TABLE IF NOT EXISTS testhandler (i INT, PRIMARY KEY(i)) ;

SET GLOBAL log_bin_trust_routine_creators=TRUE
DROP PROCEDURE IF EXISTS errhandlerdemo ;
DELIMITER |
CREATE PROCEDURE errhandlerdemo()
  BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET @err=23000;
    INSERT INTO testhandler VALUES( NULL) ;
    SET @err=-1 ;
  END ;
|
DELIMITER ;
CALL errhandlerdemo();
DROP TABLE testhandler;
SELECT 'After running errhandlerdemo:', @err;

PB
http://www.artfulsoftware.com

-----


Burke, Dan wrote:

>Hello,
>
>I've been looking into converting our existing Oracle PL/SQL code to mysql.  A lot of the syntax is pretty straight forward, and really doesn't require much change from what I've been testing with thus far.  However, I'm trying to handle exceptions, and I cannot seem to find any documentation that shows me what I'm looking for.
>
>In oracle, we have bits of code like this:
>
>        IF INSTR(:new.ACCOUNT_NUM, ' ') > 0 THEN
>                RAISE AcctNumHasSpace;
>        END IF;
>
>and then
>
>        EXCEPTION
>                WHEN AcctNumHasSpace THEN
>                        RAISE_APPLICATION_ERROR(-20001, 'Cannot insert space into ACCOUNT_NUM');
>
>But I cannot seem to figure out how to replicate that behavior.  Is it something that's possible to do as of yet?  Or not really?  Basically in this situation, we want the insert to fail if there is a space in that field.
>
>Any advice would be great.
>
>Dan.
>
>
>_________________________________________________
>This e-mail transmission is strictly confidential 
>and intended solely for the person or organization 
>to whom it is addressed. It may contain privileged 
>and confidential information and if you are not the 
>intended recipient, you must not copy, distribute or 
>take any action in reliance on it. If you have 
>received this e-mail in error, please notify the 
>sender as soon as possible and delete the e-mail 
>message and any attachment(s).
>
>This message has been scanned for viruses 
>by TechTeam's email gateway.
>
>
>
>  
>


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.13/78 - Release Date: 8/19/2005