diff options
Diffstat (limited to 'mysql-test/t/signal_demo2.test')
| -rw-r--r-- | mysql-test/t/signal_demo2.test | 207 |
1 files changed, 207 insertions, 0 deletions
diff --git a/mysql-test/t/signal_demo2.test b/mysql-test/t/signal_demo2.test new file mode 100644 index 00000000000..fc909cb926c --- /dev/null +++ b/mysql-test/t/signal_demo2.test @@ -0,0 +1,207 @@ +# Copyright (C) 2008 Sun Microsystems, Inc +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; version 2 of the License. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA + +# +# Demonstrate how RESIGNAL can be used to 'catch' and 're-throw' an error +# + +--disable_warnings +drop database if exists demo; +--enable_warnings + +create database demo; + +use demo; + +delimiter $$; + +create procedure proc_top_a(p1 integer) +begin + ## DECLARE CONTINUE HANDLER for SQLEXCEPTION, NOT FOUND + begin + end; + + select "Starting ..."; + call proc_middle_a(p1); + select "The end"; +end +$$ + +create procedure proc_middle_a(p1 integer) +begin + DECLARE l integer; + # without RESIGNAL: + # Should be: DECLARE EXIT HANDLER for SQLEXCEPTION, NOT FOUND + DECLARE EXIT HANDLER for 1 /* not sure how to handle exceptions */ + begin + select "Oops ... now what ?"; + end; + + select "In prod_middle()"; + + create temporary table t1(a integer, b integer); + select GET_LOCK("user_mutex", 10) into l; + + insert into t1 set a = p1, b = p1; + + call proc_bottom_a(p1); + + select RELEASE_LOCK("user_mutex") into l; + drop temporary table t1; +end +$$ + +create procedure proc_bottom_a(p1 integer) +begin + select "In proc_bottom()"; + + if (p1 = 1) then + begin + select "Doing something that works ..."; + select * from t1; + end; + end if; + + if (p1 = 2) then + begin + select "Doing something that fail (simulate an error) ..."; + drop table no_such_table; + end; + end if; + + if (p1 = 3) then + begin + select "Doing something that *SHOULD* works ..."; + select * from t1; + end; + end if; + +end +$$ + +delimiter ;$$ + +# +# Code without RESIGNAL: +# errors are apparent to the caller, +# but there is no cleanup code, +# so that the environment (get_lock(), temporary table) is polluted ... +# +call proc_top_a(1); + +# Expected +--error ER_BAD_TABLE_ERROR +call proc_top_a(2); + +# Dirty state +--error ER_TABLE_EXISTS_ERROR +call proc_top_a(3); + +# Dirty state +--error ER_TABLE_EXISTS_ERROR +call proc_top_a(1); + +drop temporary table if exists t1; + +delimiter $$; + +create procedure proc_top_b(p1 integer) +begin + select "Starting ..."; + call proc_middle_b(p1); + select "The end"; +end +$$ + +create procedure proc_middle_b(p1 integer) +begin + DECLARE l integer; + DECLARE EXIT HANDLER for SQLEXCEPTION, NOT FOUND + begin + begin + DECLARE CONTINUE HANDLER for SQLEXCEPTION, NOT FOUND + begin + /* Ignore errors from the cleanup code */ + end; + + select "Doing cleanup !"; + select RELEASE_LOCK("user_mutex") into l; + drop temporary table t1; + end; + + RESIGNAL; + end; + + select "In prod_middle()"; + + create temporary table t1(a integer, b integer); + select GET_LOCK("user_mutex", 10) into l; + + insert into t1 set a = p1, b = p1; + + call proc_bottom_b(p1); + + select RELEASE_LOCK("user_mutex") into l; + drop temporary table t1; +end +$$ + +create procedure proc_bottom_b(p1 integer) +begin + select "In proc_bottom()"; + + if (p1 = 1) then + begin + select "Doing something that works ..."; + select * from t1; + end; + end if; + + if (p1 = 2) then + begin + select "Doing something that fail (simulate an error) ..."; + drop table no_such_table; + end; + end if; + + if (p1 = 3) then + begin + select "Doing something that *SHOULD* works ..."; + select * from t1; + end; + end if; + +end +$$ + +delimiter ;$$ + +# +# Code with RESIGNAL: +# errors are apparent to the caller, +# the but cleanup code did get a chance to act ... +# + +call proc_top_b(1); + +--error ER_BAD_TABLE_ERROR +call proc_top_b(2); + +call proc_top_b(3); + +call proc_top_b(1); + +drop database demo; + |