Debian Codegraph @

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

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