1
drop procedure if exists p1|
2
drop procedure if exists p2|
5
prepare stmt from "select 1";
9
deallocate prepare stmt;
37
prepare stmt from "call p1()"|
38
set @SAVE_SP_RECURSION_LEVELS=@@max_sp_recursion_depth|
39
set @@max_sp_recursion_depth=100|
41
ERROR HY000: The prepared statement contains a stored routine call that refers to that same statement. It's not allowed to execute a prepared statement in such a recursive manner
43
ERROR HY000: The prepared statement contains a stored routine call that refers to that same statement. It's not allowed to execute a prepared statement in such a recursive manner
45
ERROR HY000: The prepared statement contains a stored routine call that refers to that same statement. It's not allowed to execute a prepared statement in such a recursive manner
47
ERROR HY000: The prepared statement contains a stored routine call that refers to that same statement. It's not allowed to execute a prepared statement in such a recursive manner
49
ERROR HY000: The prepared statement contains a stored routine call that refers to that same statement. It's not allowed to execute a prepared statement in such a recursive manner
51
ERROR HY000: The prepared statement contains a stored routine call that refers to that same statement. It's not allowed to execute a prepared statement in such a recursive manner
52
set @@max_sp_recursion_depth=@SAVE_SP_RECURSION_LEVELS|
54
ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine p1
56
ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine p1
58
ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine p1
62
prepare stmt from "create procedure p2() begin select 1; end";
64
deallocate prepare stmt;
67
ERROR HY000: This command is not supported in the prepared statement protocol yet
69
ERROR HY000: This command is not supported in the prepared statement protocol yet
73
prepare stmt from "drop procedure p2";
75
deallocate prepare stmt;
78
ERROR HY000: This command is not supported in the prepared statement protocol yet
80
ERROR HY000: This command is not supported in the prepared statement protocol yet
84
prepare stmt_drop from "drop table if exists t1";
86
prepare stmt from "create table t1 (a int)";
88
insert into t1 (a) values (1);
90
prepare stmt_alter from "alter table t1 add (b int)";
92
insert into t1 (a,b) values (2,1);
93
deallocate prepare stmt_alter;
94
deallocate prepare stmt;
95
deallocate prepare stmt_drop;
104
create procedure p1()
106
set @tab_name=concat("tab_", replace(curdate(), '-', '_'));
107
set @drop_sql=concat("drop table if exists ", @tab_name);
108
set @create_sql=concat("create table ", @tab_name, " (a int)");
109
set @insert_sql=concat("insert into ", @tab_name, " values (1), (2), (3)");
110
set @select_sql=concat("select * from ", @tab_name);
116
prepare stmt_drop from @drop_sql;
118
prepare stmt from @create_sql;
120
prepare stmt from @insert_sql;
122
prepare stmt from @select_sql;
125
deallocate prepare stmt;
126
deallocate prepare stmt_drop;
131
create procedure p1()
133
prepare stmt_drop from "drop table if exists t1";
135
prepare stmt from "create table t1 (a int)";
137
deallocate prepare stmt;
138
deallocate prepare stmt_drop;
140
drop function if exists f1|
141
create function f1(a int) returns int
147
ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger
149
ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger
150
select f1(f1(f1(0)))|
151
ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger
154
create procedure p1()
156
drop table if exists t1;
157
create table t1 (id integer not null primary key,
158
name varchar(20) not null);
159
insert into t1 (id, name) values (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
160
prepare stmt from "select name from t1";
165
"select name from t1 where name=(select name from t1 where id=2)";
167
select name from t1 where name=(select name from t1 where id=2);
209
prepare stmt from "select * from t1"|
210
create procedure p1()
213
deallocate prepare stmt;
221
ERROR HY000: Unknown prepared statement handler (stmt) given to EXECUTE
223
create procedure p1()
227
set @a="mysql-variable";
228
prepare stmt from "select 'dynamic sql:', @a, a";
232
ERROR 42S22: Unknown column 'a' in 'field list'
234
ERROR 42S22: Unknown column 'a' in 'field list'
236
create procedure p1()
238
prepare stmt from 'select ? as a';
239
execute stmt using @a;
249
drop table if exists t1|
250
drop table if exists t2|
252
Note 1051 Unknown table 't2'
253
create table t1 (id integer primary key auto_increment,
254
stmt_text char(35), status varchar(20))|
255
insert into t1 (stmt_text) values
256
("select 1"), ("flush tables"), ("handler t1 open as ha"),
257
("analyze table t1"), ("check table t1"), ("checksum table t1"),
258
("check table t1"), ("optimize table t1"), ("repair table t1"),
259
("describe extended select * from t1"),
260
("help help"), ("show databases"), ("show tables"),
261
("show table status"), ("show open tables"), ("show storage engines"),
262
("insert into t1 (id) values (1)"), ("update t1 set status=''"),
263
("delete from t1"), ("truncate t1"), ("call p1()"), ("foo bar"),
264
("create view v1 as select 1"), ("alter view v1 as select 2"),
265
("drop view v1"),("create table t2 (a int)"),("alter table t2 add (b int)"),
267
create procedure p1()
269
declare v_stmt_text varchar(255);
270
declare v_id integer;
271
declare done int default 0;
272
declare c cursor for select id, stmt_text from t1;
273
declare continue handler for 1295 -- ER_UNSUPPORTED_PS
274
set @status='not supported';
275
declare continue handler for 1064 -- ER_SYNTAX_ERROR
276
set @status='syntax error';
277
declare continue handler for sqlstate '02000' set done = 1;
278
prepare update_stmt from "update t1 set status=? where id=?";
282
fetch c into v_id, v_stmt_text;
283
set @id=v_id, @stmt_text=v_stmt_text;
284
set @status="supported";
285
prepare stmt from @stmt_text;
286
execute update_stmt using @status, @id;
288
until done end repeat;
289
deallocate prepare update_stmt;
295
2 flush tables supported
296
3 handler t1 open as ha not supported
297
4 analyze table t1 supported
298
5 check table t1 not supported
299
6 checksum table t1 supported
300
7 check table t1 not supported
301
8 optimize table t1 supported
302
9 repair table t1 supported
303
10 describe extended select * from t1 supported
304
11 help help not supported
305
12 show databases supported
306
13 show tables supported
307
14 show table status supported
308
15 show open tables supported
309
16 show storage engines supported
310
17 insert into t1 (id) values (1) supported
311
18 update t1 set status='' supported
312
19 delete from t1 supported
313
20 truncate t1 supported
314
21 call p1() supported
315
22 foo bar syntax error
316
23 create view v1 as select 1 supported
317
24 alter view v1 as select 2 not supported
318
25 drop view v1 supported
319
26 create table t2 (a int) supported
320
27 alter table t2 add (b int) supported
321
28 drop table t2 supported
324
prepare stmt from 'select 1'|
325
create procedure p1() execute stmt|
333
create function f1() returns int
335
deallocate prepare stmt;
338
ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger
339
create procedure p1()
341
prepare stmt from 'select 1 A';
344
prepare stmt from 'call p1()'|
346
ERROR HY000: The prepared statement contains a stored routine call that refers to that same statement. It's not allowed to execute a prepared statement in such a recursive manner
348
ERROR HY000: The prepared statement contains a stored routine call that refers to that same statement. It's not allowed to execute a prepared statement in such a recursive manner
350
drop table if exists t1, t2|
351
create procedure p1 (a int) language sql deterministic
353
declare rsql varchar(100);
354
drop table if exists t1, t2;
355
set @rsql= "create table t1 (a int)";
357
prepare pst from @rsql;
360
set @rsql= "create table t2 (a int)";
362
prepare pst from @rsql;
364
drop table if exists t1, t2;
369
create table t1 (a int)
371
create table t2 (a int)
377
create table t1 (a int)
379
create table t2 (a int)
383
drop procedure if exists p1|