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;
101
Note 1051 Unknown table 't1'
106
create procedure p1()
108
set @tab_name=concat("tab_", replace(curdate(), '-', '_'));
109
set @drop_sql=concat("drop table if exists ", @tab_name);
110
set @create_sql=concat("create table ", @tab_name, " (a int)");
111
set @insert_sql=concat("insert into ", @tab_name, " values (1), (2), (3)");
112
set @select_sql=concat("select * from ", @tab_name);
118
prepare stmt_drop from @drop_sql;
120
prepare stmt from @create_sql;
122
prepare stmt from @insert_sql;
124
prepare stmt from @select_sql;
127
deallocate prepare stmt;
128
deallocate prepare stmt_drop;
133
create procedure p1()
135
prepare stmt_drop from "drop table if exists t1";
137
prepare stmt from "create table t1 (a int)";
139
deallocate prepare stmt;
140
deallocate prepare stmt_drop;
142
drop function if exists f1|
143
create function f1(a int) returns int
149
ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger
151
ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger
152
select f1(f1(f1(0)))|
153
ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger
156
create procedure p1()
158
drop table if exists t1;
159
create table t1 (id integer not null primary key,
160
name varchar(20) not null);
161
insert into t1 (id, name) values (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
162
prepare stmt from "select name from t1";
167
"select name from t1 where name=(select name from t1 where id=2)";
169
select name from t1 where name=(select name from t1 where id=2);
211
prepare stmt from "select * from t1"|
212
create procedure p1()
215
deallocate prepare stmt;
223
ERROR HY000: Unknown prepared statement handler (stmt) given to EXECUTE
225
create procedure p1()
229
set @a="mysql-variable";
230
prepare stmt from "select 'dynamic sql:', @a, a";
234
ERROR 42S22: Unknown column 'a' in 'field list'
236
ERROR 42S22: Unknown column 'a' in 'field list'
238
create procedure p1()
240
prepare stmt from 'select ? as a';
241
execute stmt using @a;
251
drop table if exists t1|
252
drop table if exists t2|
254
Note 1051 Unknown table 't2'
255
create table t1 (id integer primary key auto_increment,
256
stmt_text char(35), status varchar(20))|
257
insert into t1 (stmt_text) values
258
("select 1"), ("flush tables"), ("handler t1 open as ha"),
259
("analyze table t1"), ("check table t1"), ("checksum table t1"),
260
("check table t1"), ("optimize table t1"), ("repair table t1"),
261
("describe extended select * from t1"),
262
("help help"), ("show databases"), ("show tables"),
263
("show table status"), ("show open tables"), ("show storage engines"),
264
("insert into t1 (id) values (1)"), ("update t1 set status=''"),
265
("delete from t1"), ("truncate t1"), ("call p1()"), ("foo bar"),
266
("create view v1 as select 1"), ("alter view v1 as select 2"),
267
("drop view v1"),("create table t2 (a int)"),("alter table t2 add (b int)"),
269
create procedure p1()
271
declare v_stmt_text varchar(255);
272
declare v_id integer;
273
declare done int default 0;
274
declare c cursor for select id, stmt_text from t1;
275
declare continue handler for 1295 -- ER_UNSUPPORTED_PS
276
set @status='not supported';
277
declare continue handler for 1064 -- ER_SYNTAX_ERROR
278
set @status='syntax error';
279
declare continue handler for sqlstate '02000' set done = 1;
280
prepare update_stmt from "update t1 set status=? where id=?";
284
fetch c into v_id, v_stmt_text;
285
set @id=v_id, @stmt_text=v_stmt_text;
286
set @status="supported";
287
prepare stmt from @stmt_text;
288
execute update_stmt using @status, @id;
290
until done end repeat;
291
deallocate prepare update_stmt;
297
2 flush tables supported
298
3 handler t1 open as ha not supported
299
4 analyze table t1 supported
300
5 check table t1 not supported
301
6 checksum table t1 supported
302
7 check table t1 not supported
303
8 optimize table t1 supported
304
9 repair table t1 supported
305
10 describe extended select * from t1 supported
306
11 help help not supported
307
12 show databases supported
308
13 show tables supported
309
14 show table status supported
310
15 show open tables supported
311
16 show storage engines supported
312
17 insert into t1 (id) values (1) supported
313
18 update t1 set status='' supported
314
19 delete from t1 supported
315
20 truncate t1 supported
316
21 call p1() supported
317
22 foo bar syntax error
318
23 create view v1 as select 1 supported
319
24 alter view v1 as select 2 not supported
320
25 drop view v1 supported
321
26 create table t2 (a int) supported
322
27 alter table t2 add (b int) supported
323
28 drop table t2 supported
326
prepare stmt from 'select 1'|
327
create procedure p1() execute stmt|
335
create function f1() returns int
337
deallocate prepare stmt;
340
ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger
341
create procedure p1()
343
prepare stmt from 'select 1 A';
346
prepare stmt from 'call p1()'|
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
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
352
drop table if exists t1, t2|
353
create procedure p1 (a int) language sql deterministic
355
declare rsql varchar(100);
356
drop table if exists t1, t2;
357
set @rsql= "create table t1 (a int)";
359
prepare pst from @rsql;
362
set @rsql= "create table t2 (a int)";
364
prepare pst from @rsql;
366
drop table if exists t1, t2;
371
create table t1 (a int)
373
create table t2 (a int)
375
Note 1051 Unknown table 't1'
376
Note 1051 Unknown table 't2'
382
create table t1 (a int)
384
create table t2 (a int)
386
Note 1051 Unknown table 't1'
387
Note 1051 Unknown table 't2'
391
drop procedure if exists p1|