Debian Codegraph @

mysql-test/suite/funcs_1/storedproc/storedproc_07.inc

raw
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 # ==============================================================================