|
1835.1.6
by Brian Aker
This patch returns dynamic SQL into the core (through SQL-Server syntax). |
1 |
--replace_column 1 # |
2 |
EXECUTE "SELECT TABLE_NAME FROM DATA_DICTIONARY.tables LIMIT 2"; |
|
3 |
SET @t1=1, @t2=2, @t3:=4; |
|
4 |
SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3; |
|
5 |
||
6 |
EXECUTE "SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3"; |
|
7 |
||
8 |
SET @bugus_syntax= "3"; |
|
9 |
--error 1064 |
|
10 |
EXECUTE "SELECT * FROM DATA_DICTIONARY.tables LIMIT @bugus_syntax"; |
|
11 |
||
12 |
--replace_column 1 # |
|
13 |
EXECUTE "SELECT TABLE_NAME FROM DATA_DICTIONARY.tables LIMIT 2"; |
|
14 |
||
15 |
--error 1064 |
|
16 |
EXECUTE "EXECUTE SELECT * FROM DATA_DICTIONARY.tables LIMIT 2"; |
|
17 |
||
18 |
EXECUTE "SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3"; |
|
19 |
set @sub_var="SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3"; |
|
20 |
EXECUTE @sub_var; |
|
21 |
||
22 |
EXECUTE "CREATE TABLE t1 (a SERIAL)"; |
|
23 |
SHOW CREATE TABLE t1; |
|
24 |
EXECUTE "INSERT INTO t1 VALUES ()"; |
|
25 |
EXECUTE "DROP TABLE t1"; |
|
|
1921.4.5
by Brian Aker
Fixing up support so that even when not using a new thread, we can execute |
26 |
SHOW TABLES; |
|
1835.1.6
by Brian Aker
This patch returns dynamic SQL into the core (through SQL-Server syntax). |
27 |
|
28 |
--error 1064 |
|
29 |
EXECUTE "SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3; SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3"; |
|
30 |
||
31 |
set @foo="SELECT 1+2+3"; |
|
32 |
execute @foo; |
|
33 |
||
34 |
set @create="CREATE TABLE t1 (a SERIAL)"; |
|
35 |
set @insert="INSERT INTO t1 VALUES ()"; |
|
36 |
set @select="SELECT a FROM t1"; |
|
37 |
set @drop="DROP TABLE t1"; |
|
38 |
||
39 |
execute @create; |
|
40 |
execute @insert; |
|
41 |
execute @insert; |
|
42 |
execute @insert; |
|
43 |
execute @select; |
|
44 |
execute @drop; |
|
45 |
||
|
1921.4.1
by Brian Aker
Adding in support for EXECUTE to have WITH NO RETURN. |
46 |
execute @create WITH NO RETURN; |
47 |
execute @insert WITH NO RETURN; |
|
48 |
execute @insert WITH NO RETURN; |
|
49 |
execute @insert WITH NO RETURN; |
|
50 |
execute @select WITH NO RETURN; |
|
51 |
execute @drop WITH NO RETURN; |
|
52 |
||
|
1835.1.6
by Brian Aker
This patch returns dynamic SQL into the core (through SQL-Server syntax). |
53 |
--error 1210 |
54 |
EXECUTE @does_not_exist; |
|
55 |
||
56 |
--error 1210 |
|
57 |
EXECUTE ""; |
|
|
1835.1.7
by Brian Aker
Adding in a subquery test. |
58 |
|
59 |
EXECUTE "SELECT (SELECT 1) UNION SELECT (SELECT 2)"; |
|
60 |
EXECUTE "explain extended SELECT (SELECT 1) UNION SELECT (SELECT 2)"; |
|
61 |
EXECUTE "SELECT (SELECT (SELECT 0 UNION SELECT 0))"; |
|
62 |
EXECUTE "explain extended SELECT (SELECT (SELECT 0 UNION SELECT 0))"; |
|
|
1921.4.1
by Brian Aker
Adding in support for EXECUTE to have WITH NO RETURN. |
63 |
|
64 |
EXECUTE "SELECT (SELECT 1) UNION SELECT (SELECT 2)" WITH NO RETURN; |
|
65 |
EXECUTE "explain extended SELECT (SELECT 1) UNION SELECT (SELECT 2)" WITH NO RETURN; |
|
66 |
EXECUTE "SELECT (SELECT (SELECT 0 UNION SELECT 0))" WITH NO RETURN; |
|
67 |
EXECUTE "explain extended SELECT (SELECT (SELECT 0 UNION SELECT 0))" WITH NO RETURN; |
|
|
1921.4.5
by Brian Aker
Fixing up support so that even when not using a new thread, we can execute |
68 |
|
69 |
# Test multiple statements
|
|
70 |
||
71 |
SET @var="CREATE SCHEMA d; CREATE SCHEMA e;"; |
|
72 |
EXECUTE @var WITH NO RETURN; |
|
73 |
||
74 |
show schemas LIKE "d"; |
|
75 |
show schemas LIKE "e"; |
|
76 |
DROP SCHEMA d; |
|
77 |
DROP SCHEMA e; |
|
|
1921.4.6
by Brian Aker
Fix so that an error causes a statement to fail/exit. |
78 |
|
79 |
# Test error statement in mid query
|
|
80 |
SET @var="CREATE SCHEMA d; SELECT fosdsd; CREATE SCHEMA e;"; |
|
81 |
EXECUTE @var WITH NO RETURN; |
|
82 |
show schemas LIKE "d"; |
|
83 |
show schemas LIKE "e"; |
|
84 |
DROP SCHEMA d; |
|
|
1938.5.1
by patrick crews
Additional test cases for EXECUTE |
85 |
|
86 |
# Test recursion
|
|
87 |
SET @var = "SET @var='test value'"; |
|
88 |
EXECUTE @var; |
|
89 |
SELECT @var; |
|
90 |
||
91 |
# Alter table
|
|
92 |
CREATE TABLE t1 (a int); |
|
93 |
SET @var = "ALTER TABLE t1 ADD COLUMN (b char(100))"; |
|
94 |
EXECUTE @var; |
|
95 |
SHOW CREATE TABLE t1; |
|
96 |
DROP TABLE t1; |
|
97 |
||
98 |
# Insert
|
|
99 |
CREATE TABLE t1 (a int, b char(20)); |
|
100 |
SET @var = "INSERT INTO t1 VALUES (@a_val, @b_val)"; |
|
101 |
SET @a_val = 1; |
|
102 |
SET @b_val = 'TNETENNBA'; |
|
103 |
EXECUTE @var; |
|
104 |
SELECT * FROM t1; |
|
105 |
SET @a_val = 2; |
|
106 |
SET @b_val = 'OVERNUMEROUSNESS'; |
|
107 |
EXECUTE @var; |
|
108 |
SELECT * FROM t1; |
|
109 |
DROP TABLE t1; |
|
110 |
||
111 |
# Update
|
|
112 |
CREATE TABLE t1 (a int, b char(100), c char(100)); |
|
113 |
INSERT INTO t1 VALUES (1, 'Moss', 'IT'); |
|
114 |
INSERT INTO t1 VALUES (2, 'Roy', 'IT'); |
|
115 |
INSERT INTO t1 VALUES (3, 'Jen', 'IT'); |
|
116 |
SET @var = "UPDATE t1 SET c = @new_dept WHERE b = @name"; |
|
117 |
SET @new_dept = "Relationship Manager"; |
|
118 |
SET @name = 'Jen'; |
|
119 |
EXECUTE @var; |
|
120 |
SELECT * FROM t1; |
|
121 |
DROP TABLE t1; |
|
122 |
||
123 |
# DELETE
|
|
124 |
CREATE TABLE t1 (a int); |
|
125 |
INSERT INTO t1 VALUES (42); |
|
126 |
INSERT INTO t1 VALUES (9); |
|
127 |
INSERT INTO t1 VALUES (1); |
|
128 |
INSERT INTO t1 VALUES (0); |
|
129 |
INSERT INTO t1 VALUES (2); |
|
130 |
SET @var = "DELETE FROM t1 WHERE a%2=0"; |
|
131 |
EXECUTE @var; |
|
132 |
SELECT * FROM t1; |
|
133 |
DROP TABLE t1; |
|
134 |
||
135 |
# INDEX
|
|
136 |
CREATE TABLE t1 (a int); |
|
137 |
SET @var = "CREATE INDEX a_index ON t1 (a)"; |
|
138 |
EXECUTE @var; |
|
139 |
SHOW CREATE TABLE t1; |
|
140 |
SET @var = "DROP INDEX a_index ON t1"; |
|
141 |
EXECUTE @var; |
|
142 |
SHOW CREATE TABLE t1; |
|
143 |
DROP TABLE t1; |
|
144 |