# # SQL Syntax for Prepared Statements test # --disable_warnings drop table if exists t1,t2; --enable_warnings create table t1 ( a int primary key, b char(10) ); insert into t1 values (1,'one'); insert into t1 values (2,'two'); insert into t1 values (3,'three'); insert into t1 values (4,'four'); # basic functionality set @a=2; prepare stmt1 from 'select * from t1 where a <= ?'; execute stmt1 using @a; set @a=3; execute stmt1 using @a; # non-existant statement --error 1243 deallocate prepare no_such_statement; --error 1210 execute stmt1; # Nesting ps commands is not allowed: --error 1064 prepare stmt2 from 'prepare nested_stmt from "select 1"'; --error 1064 prepare stmt2 from 'execute stmt1'; --error 1064 prepare stmt2 from 'deallocate prepare z'; # PS insert prepare stmt3 from 'insert into t1 values (?,?)'; set @arg1=5, @arg2='five'; execute stmt3 using @arg1, @arg2; select * from t1 where a>3; # PS update prepare stmt4 from 'update t1 set a=? where b=?'; set @arg1=55, @arg2='five'; execute stmt4 using @arg1, @arg2; select * from t1 where a>3; # PS create/delete prepare stmt4 from 'create table t2 (a int)'; execute stmt4; prepare stmt4 from 'drop table t2'; execute stmt4; # Do something that will cause error --error 1051 execute stmt4; # placeholders in result field names. prepare stmt5 from 'select ? + a from t1'; set @a=1; execute stmt5 using @a; execute stmt5 using @no_such_var; set @nullvar=1; set @nullvar=NULL; execute stmt5 using @nullvar; set @nullvar2=NULL; execute stmt5 using @nullvar2; # Check that multiple SQL statements are disabled inside PREPARE --error 1064 prepare stmt6 from 'select 1; select2'; --error 1064 prepare stmt6 from 'insert into t1 values (5,"five"); select2'; # This shouldn't parse --error 1064 explain prepare stmt6 from 'insert into t1 values (5,"five"); select2'; create table t2 ( a int ); insert into t2 values (0); # parameter is NULL set @arg00=NULL ; prepare stmt1 from 'select 1 FROM t2 where a=?' ; execute stmt1 using @arg00 ; drop table t1,t2;