1 #### suite/funcs_1/storedproc/storedproc_07.inc
3 --source suite/funcs_1/storedproc/load_sp_tb.inc
5 # ==============================================================================
6 # (numbering from requirement document TP v1.0, Last updated: 25 Jan 2005 01:00)
8 # 3.1.7 SQL mode checks:
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.
13 # ==============================================================================
14 let $message= Section 3.1.7 - SQL
mode checks:;
15 --source include/show_msg80.inc
19 # ------------------------------------------------------------------------------
20 let $message= Testcase 3.1.7.1:
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
25 --source include/show_msg80.inc
28 DROP PROCEDURE IF EXISTS sp1;
29 DROP
TABLE IF EXISTS temp_tbl;
30 DROP
TABLE IF EXISTS result;
33 CREATE
TABLE temp_tbl (f1 tinyint);
34 CREATE
TABLE result (f1 text(200), f2
char(20));
36 set @@sql_mode=
'traditional';
37 SHOW VARIABLES LIKE
'sql_mode';
40 CREATE PROCEDURE sp1()
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);
49 INSERT INTO result VALUES (@cur_val_sql_mode, 'value restored');
51 INSERT INTO result VALUES (@cur_val_sql_mode, 'value not restored');
56 SHOW CREATE PROCEDURE sp1;
59 SHOW VARIABLES LIKE 'sql_mode';
64 SHOW VARIABLES LIKE 'sql_mode';
68 SET @@sql_mode=
'TRADITIONAL';
74 # ------------------------------------------------------------------------------
75 let $message= Testcase 3.1.7.2:
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
80 --source include/show_msg80.inc
83 DROP PROCEDURE IF EXISTS sp2;
86 --echo ... show initial value
87 SHOW VARIABLES LIKE
'sql_mode';
90 CREATE PROCEDURE sp2()
92 SET @@sql_mode='MAXDB';
93 SHOW VARIABLES LIKE 'sql_mode';
97 SHOW CREATE PROCEDURE sp2;
98 --echo ... show value prior calling procedure
99 SHOW VARIABLES LIKE 'sql_mode';
101 --echo ... call procedure that changes sql_mode
104 --echo ... check whether old value is re-set
105 SHOW VARIABLES LIKE 'sql_mode';
111 # ==============================================================================
112 # USE the same .inc to cleanup before and after the test
113 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
115 # ==============================================================================
116 let $message= . +++ END OF SCRIPT +++;
117 --source include/show_msg80.inc
118 # ==============================================================================