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
