| 1 |
#### suite/funcs_1/storedproc/storedproc_07.inc |
| 2 |
# |
| 3 |
--source suite/funcs_1/storedproc/load_sp_tb.inc |
| 4 |
|
| 5 |
# ============================================================================== |
| 6 |
# (numbering from requirement document TP v1.0, Last updated: 25 Jan 2005 01:00) |
| 7 |
# |
| 8 |
# 3.1.7 SQL mode checks: |
| 9 |
# |
| 10 |
# 1. Ensure that the sql_mode setting in effect at the time a stored procedure is created is the same setting under which the stored procedure runs when it is called/executed. |
| 11 |
# 2. Ensure that if the sql_mode setting is changed when a stored procedure is run, that the original setting is restored as soon as the stored procedure execution is complete. |
| 12 |
# |
| 13 |
# ============================================================================== |
| 14 |
let $message= Section 3.1.7 - SQL mode checks:; |
| 15 |
--source include/show_msg80.inc |
| 16 |
|
| 17 |
USE db_storedproc; |
| 18 |
|
| 19 |
# ------------------------------------------------------------------------------ |
| 20 |
let $message= Testcase 3.1.7.1: |
| 21 |
----------------- |
| 22 |
Ensure that the sql_mode setting in effect at the time a stored procedure is |
| 23 |
created is the same setting under which the stored procedure runs when it is |
| 24 |
called/executed.; |
| 25 |
--source include/show_msg80.inc |
| 26 |
|
| 27 |
--disable_warnings |
| 28 |
DROP PROCEDURE IF EXISTS sp1; |
| 29 |
DROP TABLE IF EXISTS temp_tbl; |
| 30 |
DROP TABLE IF EXISTS result; |
| 31 |
--enable_warnings |
| 32 |
|
| 33 |
CREATE TABLE temp_tbl (f1 tinyint); |
| 34 |
CREATE TABLE result (f1 text(200), f2 char(20)); |
| 35 |
|
| 36 |
set @@sql_mode='traditional'; |
| 37 |
SHOW VARIABLES LIKE 'sql_mode'; |
| 38 |
|
| 39 |
delimiter //; |
| 40 |
CREATE PROCEDURE sp1() |
| 41 |
BEGIN |
| 42 |
declare a tinyint; |
| 43 |
declare count_ int default 1; |
| 44 |
declare continue handler for sqlstate '22003' set count_=1000; |
| 45 |
SHOW VARIABLES LIKE 'sql_mode'; |
| 46 |
SELECT @@sql_mode into @cur_val_sql_mode; |
| 47 |
insert into temp_tbl values (1000); |
| 48 |
if count_ = 1000 THEN |
| 49 |
INSERT INTO result VALUES (@cur_val_sql_mode, 'value restored'); |
| 50 |
ELSE |
| 51 |
INSERT INTO result VALUES (@cur_val_sql_mode, 'value not restored'); |
| 52 |
END if; |
| 53 |
END// |
| 54 |
delimiter ;// |
| 55 |
|
| 56 |
SHOW CREATE PROCEDURE sp1; |
| 57 |
|
| 58 |
set @@sql_mode=''; |
| 59 |
SHOW VARIABLES LIKE 'sql_mode'; |
| 60 |
|
| 61 |
CALL sp1(); |
| 62 |
SELECT * from result; |
| 63 |
|
| 64 |
SHOW VARIABLES LIKE 'sql_mode'; |
| 65 |
SELECT @@sql_mode; |
| 66 |
|
| 67 |
# cleanup |
| 68 |
SET @@sql_mode='TRADITIONAL'; |
| 69 |
DROP PROCEDURE sp1; |
| 70 |
DROP TABLE temp_tbl; |
| 71 |
DROP TABLE result; |
| 72 |
|
| 73 |
|
| 74 |
# ------------------------------------------------------------------------------ |
| 75 |
let $message= Testcase 3.1.7.2: |
| 76 |
----------------- |
| 77 |
Ensure that if the sql_mode setting is changed when a stored procedure is run, |
| 78 |
that the original setting is restored as soon as the stored procedure execution |
| 79 |
is complete.; |
| 80 |
--source include/show_msg80.inc |
| 81 |
|
| 82 |
--disable_warnings |
| 83 |
DROP PROCEDURE IF EXISTS sp2; |
| 84 |
--enable_warnings |
| 85 |
|
| 86 |
--echo ... show initial value |
| 87 |
SHOW VARIABLES LIKE 'sql_mode'; |
| 88 |
|
| 89 |
delimiter //; |
| 90 |
CREATE PROCEDURE sp2() |
| 91 |
BEGIN |
| 92 |
SET @@sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE'; |
| 93 |
SHOW VARIABLES LIKE 'sql_mode'; |
| 94 |
END// |
| 95 |
delimiter ;// |
| 96 |
|
| 97 |
SHOW CREATE PROCEDURE sp2; |
| 98 |
--echo ... show value prior calling procedure |
| 99 |
SHOW VARIABLES LIKE 'sql_mode'; |
| 100 |
|
| 101 |
--echo ... call procedure that changes sql_mode |
| 102 |
CALL sp2(); |
| 103 |
|
| 104 |
--echo ... check whether old value is re-set |
| 105 |
SHOW VARIABLES LIKE 'sql_mode'; |
| 106 |
|
| 107 |
# cleanup |
| 108 |
DROP PROCEDURE sp2; |
| 109 |
|
| 110 |
|
| 111 |
# ============================================================================== |
| 112 |
# USE the same .inc to cleanup before and after the test |
| 113 |
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc |
| 114 |
|
| 115 |
# ============================================================================== |
| 116 |
let $message= . +++ END OF SCRIPT +++; |
| 117 |
--source include/show_msg80.inc |
| 118 |
# ============================================================================== |