Debian Codegraph @

mysql-test/suite/x/include/test_flow_resultset_prepexecute_stored_procedures.inc

raw
1 --let $STORED_PROC_DB_NAME=xtest
2
3 --source ../include/stored_procedures.inc
4 --write_file $MYSQL_TMP_DIR/prep_stmt_cursor.xpl
5 -->import assert_messages.macro
6 -->import assert_notices.macro
7 -->import assert_variable.macro
8 -->quiet
9
10 -->macro Prepare_statement_with_procedure %PROCEDURE%
11 Mysqlx.Prepare.Prepare {
12 stmt_id: 1
13 stmt: {
14 type: STMT
15 stmt_execute: {
16 stmt: 'CALL xtest.%PROCEDURE%'
17 }
18 }
19 }
20 -->recvok
21 -->endmacro
22
23 -->macro Execute_no_args
24 Mysqlx.Prepare.Execute {
25 stmt_id: 1
26 }
27 -->endmacro
28
29 -->macro Deallocate_statement
30 Mysqlx.Prepare.Deallocate {
31 stmt_id: 1
32 }
33 -->recvok
34 -->endmacro
35
36 -->macro Execute_procedure_no_output_params_base %PROCEDURE%
37 -->callmacro Prepare_statement_with_procedure %PROCEDURE%
38 -->callmacro Execute_no_args
39 -->endmacro
40
41 -->macro Execute_procedure_no_output_params %PROCEDURE%
42 -->echo ###############################################################
43 -->echo Run '%PROCEDURE%' with no output parameters
44 -->echo ###############################################################
45 -->callmacro Execute_procedure_no_output_params_base %PROCEDURE%
46 -->endmacro
47
48 -->macro Assert_local_var
49 -->echo Verify [Variable local_var not equal to zero]
50 -->noquery_result
51 -->stmtsql SELECT @local_var;
52 -->recvtovar %TEST_VAR%
53 -->assert_ne 0 %TEST_VAR%
54 -->query_result
55 -->endmacro
56
57 -->macro Execute_procedure_with_local_var %PROCEDURE%
58 -->stmtsql SET @local_var=0;
59 -->recvresult
60 -->echo ###############################################################
61 -->echo Run '%PROCEDURE%' with local var
62 -->echo ###############################################################
63 -->callmacro Execute_procedure_no_output_params_base %PROCEDURE%
64 -->endmacro
65
66 -->macro Execute_with_args %OUT_PARAM%
67 Mysqlx.Prepare.Execute {
68 stmt_id: 1
69 args {
70 type: SCALAR scalar {
71 type: V_STRING v_string {
72 value: "%OUT_PARAM%"
73 }
74 }
75 }
76 }
77 -->endmacro
78
79 -->macro Execute_procedure_with_out_param %PROCEDURE% %OUT_PARAM%
80 -->callmacro Prepare_statement_with_procedure %PROCEDURE%
81 -->echo ###############################################################
82 -->echo Run '%PROCEDURE%' with out param
83 -->echo ###############################################################
84 -->callmacro Execute_with_args %OUT_PARAM%
85 -->endmacro
86
87 -->macro Execute_procedure_with_multiple_out_params %PROCEDURE% %OUT_PARAM1% %OUT_PARAM2%
88 -->callmacro Prepare_statement_with_procedure %PROCEDURE%
89 -->echo ###############################################################
90 -->echo Run '%PROCEDURE%' with multiple out parameters
91 -->echo ###############################################################
92 Mysqlx.Cursor.Open {
93 cursor_id: 1
94 stmt: {
95 type: PREPARE_EXECUTE
96 prepare_execute: {
97 stmt_id: 1
98 args {
99 type: SCALAR scalar {
100 type: V_STRING v_string {
101 value: "%OUT_PARAM1%"
102 }
103 }
104 }
105 args {
106 type: SCALAR scalar {
107 type: V_STRING v_string {
108 value: "%OUT_PARAM2%"
109 }
110 }
111 }
112 }
113 }
114 }
115 -->endmacro
116
117 -->macro Show_warnings
118 -->echo Warnings:
119 -->stmtsql SHOW WARNINGS
120 -->recvresult
121 -->echo
122 -->endmacro
123
124 -->macro Execute_procedure_no_output_params_with_error %PROCEDURE%
125 -->callmacro Prepare_statement_with_procedure %PROCEDURE%
126 -->echo ########################################################################
127 -->echo Run '%PROCEDURE%' with errors and without output parameters
128 -->echo ########################################################################
129 -->callmacro Execute_no_args
130 -->endmacro
131
132 -->macro Execute_procedure_with_output_params_and_with_error %PROCEDURE% %OUT_PARAM%
133 -->callmacro Prepare_statement_with_procedure %PROCEDURE%
134 -->echo ########################################################################
135 -->echo Run '%PROCEDURE%' with errors and with output parameters
136 -->echo ########################################################################
137 -->callmacro Execute_with_args %OUT_PARAM%
138 -->endmacro
139
140 # Check stored procedures without any output parameters
141 #
142 -->callmacro Execute_procedure_no_output_params `empty`()
143 -->callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0
144 -->callmacro Assert_message Mysqlx.Sql.StmtExecuteOk
145 -->callmacro Deallocate_statement
146
147 -->callmacro Execute_procedure_no_output_params one_results_set()
148 -->callmacro Assert_metadata UINT 'c1t1'
149 -->callmacro Assert_metadata BYTES 'c2t1'
150 -->callmacro Assert_rows 3
151 -->callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets
152 -->callmacro Assert_message Mysqlx.Resultset.FetchDone
153 -->callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0
154 -->callmacro Assert_message Mysqlx.Sql.StmtExecuteOk
155 -->callmacro Deallocate_statement
156
157 -->callmacro Execute_procedure_no_output_params three_results_sets()
158 -->callmacro Assert_metadata UINT 'c1t1'
159 -->callmacro Assert_metadata BYTES 'c2t1'
160 -->callmacro Assert_rows 3
161 -->callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets
162 -->callmacro Assert_metadata DATETIME 'c1t2'
163 -->callmacro Assert_rows 4
164 -->callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets
165 -->callmacro Assert_metadata UINT 'c1t1'
166 -->callmacro Assert_metadata BYTES 'c2t1'
167 -->callmacro Assert_rows 3
168 -->callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets
169 -->callmacro Assert_message Mysqlx.Resultset.FetchDone
170 -->callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0
171 -->callmacro Assert_message Mysqlx.Sql.StmtExecuteOk
172 -->callmacro Deallocate_statement
173
174 -->callmacro Execute_procedure_no_output_params update_no_select()
175 -->callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:2
176 -->callmacro Assert_message Mysqlx.Sql.StmtExecuteOk
177 -->callmacro Deallocate_statement
178
179 -->callmacro Execute_procedure_no_output_params update_and_select()
180 -->callmacro Assert_metadata UINT 'c1t1'
181 -->callmacro Assert_metadata BYTES 'c2t1'
182 -->callmacro Assert_rows 3
183 -->callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets
184 -->callmacro Assert_message Mysqlx.Resultset.FetchDone
185 -->callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0
186 -->callmacro Assert_message Mysqlx.Sql.StmtExecuteOk
187 -->callmacro Deallocate_statement
188
189 -->callmacro Execute_procedure_no_output_params insert_no_select()
190 -->callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:3
191 -->callmacro Assert_message Mysqlx.Sql.StmtExecuteOk
192 -->callmacro Deallocate_statement
193
194 -->callmacro Execute_procedure_no_output_params insert_and_select()
195 -->callmacro Assert_metadata UINT 'c1t1'
196 -->callmacro Assert_metadata BYTES 'c2t1'
197 -->callmacro Assert_rows 9
198 -->callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets
199 -->callmacro Assert_message Mysqlx.Resultset.FetchDone
200 -->callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0
201 -->callmacro Assert_message Mysqlx.Sql.StmtExecuteOk
202 -->callmacro Deallocate_statement
203
204 # Check stored procedures with parameters (both local and output)
205 #
206 -->callmacro Execute_procedure_with_local_var out_param_no_select(10, @local_var)
207 -->callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0
208 -->callmacro Assert_message Mysqlx.Sql.StmtExecuteOk
209 -->callmacro Deallocate_statement
210
211 -->callmacro Execute_procedure_with_out_param out_param_no_select(10, ?) @my_var
212 -->callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreOutParams
213 -->callmacro Assert_metadata SINT 'res'
214 -->callmacro Assert_rows 1
215 -->callmacro Assert_message Mysqlx.Resultset.FetchDone
216 -->callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0
217 -->callmacro Assert_message Mysqlx.Sql.StmtExecuteOk
218 -->callmacro Deallocate_statement
219
220 -->callmacro Execute_procedure_with_local_var update_and_out_param(@local_var)
221 -->callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:1
222 -->callmacro Assert_message Mysqlx.Sql.StmtExecuteOk
223 -->callmacro Assert_local_var
224 -->callmacro Deallocate_statement
225
226 -->callmacro Execute_procedure_with_out_param update_and_out_param(?) @my_var
227 -->callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreOutParams
228 -->callmacro Assert_metadata SINT 'max_val'
229 -->callmacro Assert_rows 1
230 -->callmacro Assert_message Mysqlx.Resultset.FetchDone
231 -->callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:1
232 -->callmacro Assert_message Mysqlx.Sql.StmtExecuteOk
233 -->callmacro Deallocate_statement
234
235 -->callmacro Execute_procedure_with_local_var update_and_out_param_and_select(@local_var)
236 -->callmacro Assert_metadata DECIMAL 'AVG(c1t1)'
237 -->callmacro Assert_rows 1
238 -->callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets
239 -->callmacro Assert_message Mysqlx.Resultset.FetchDone
240 -->callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0
241 -->callmacro Assert_message Mysqlx.Sql.StmtExecuteOk
242 -->callmacro Assert_local_var
243 -->callmacro Deallocate_statement
244
245 -->callmacro Execute_procedure_with_out_param update_and_out_param_and_select(?) @my_var
246 -->callmacro Assert_metadata DECIMAL 'AVG(c1t1)'
247 -->callmacro Assert_rows 1
248 -->callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreOutParams
249 -->callmacro Assert_metadata SINT 'max_val'
250 -->callmacro Assert_rows 1
251 -->callmacro Assert_message Mysqlx.Resultset.FetchDone
252 -->callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0
253 -->callmacro Assert_message Mysqlx.Sql.StmtExecuteOk
254 -->callmacro Deallocate_statement
255
256 -->callmacro Execute_procedure_with_local_var insert_and_out_param(@local_var)
257 -->callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:1
258 -->callmacro Assert_message Mysqlx.Sql.StmtExecuteOk
259 -->callmacro Assert_local_var
260 -->callmacro Deallocate_statement
261
262 -->callmacro Execute_procedure_with_out_param insert_and_out_param(?) @my_var
263 -->callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreOutParams
264 -->callmacro Assert_metadata SINT 'max_val'
265 -->callmacro Assert_rows 1
266 -->callmacro Assert_message Mysqlx.Resultset.FetchDone
267 -->callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:1
268 -->callmacro Assert_message Mysqlx.Sql.StmtExecuteOk
269 -->callmacro Deallocate_statement
270
271 -->callmacro Execute_procedure_with_local_var insert_and_out_param_and_select(@local_var)
272 -->callmacro Assert_metadata UINT 'MIN(c1t1)'
273 -->callmacro Assert_rows 1
274 -->callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets
275 -->callmacro Assert_message Mysqlx.Resultset.FetchDone
276 -->callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0
277 -->callmacro Assert_message Mysqlx.Sql.StmtExecuteOk
278 -->callmacro Assert_local_var
279 -->callmacro Deallocate_statement
280
281 -->callmacro Execute_procedure_with_out_param insert_and_out_param_and_select(?) @my_var
282 -->callmacro Assert_metadata UINT 'MIN(c1t1)'
283 -->callmacro Assert_rows 1
284 -->callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreOutParams
285 -->callmacro Assert_metadata SINT 'max_val'
286 -->callmacro Assert_rows 1
287 -->callmacro Assert_message Mysqlx.Resultset.FetchDone
288 -->callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0
289 -->callmacro Assert_message Mysqlx.Sql.StmtExecuteOk
290 -->callmacro Deallocate_statement
291
292 # Check stored procedures with multiple output parameters
293 #
294 -->callmacro Execute_procedure_with_multiple_out_params out_params_with_select(?, ?) @my_var 1@my_var2
295 -->callmacro Assert_metadata UINT 'c1t1'
296 -->callmacro Assert_metadata BYTES 'c2t1'
297 -->callmacro Assert_rows 13
298 -->callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreOutParams
299 -->callmacro Assert_metadata DATETIME 'min_val'
300 -->callmacro Assert_metadata DATETIME 'max_val'
301 -->callmacro Assert_rows 1
302 -->callmacro Assert_message Mysqlx.Resultset.FetchDone
303 -->callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0
304 -->callmacro Assert_message Mysqlx.Sql.StmtExecuteOk
305 -->callmacro Deallocate_statement
306
307 # Check stored procedures with error conditions when there are no output
308 # parameters
309 #
310 -->callmacro Execute_procedure_no_output_params_with_error error()
311 -->callmacro Assert_message Mysqlx.Error
312 -->callmacro Show_warnings
313 -->callmacro Deallocate_statement
314
315 -->callmacro Execute_procedure_no_output_params_with_error with_warning()
316 -->callmacro Assert_message Mysqlx.Error
317 -->callmacro Show_warnings
318 -->callmacro Deallocate_statement
319
320 -->callmacro Execute_procedure_no_output_params_with_error select_and_error()
321 -->callmacro Assert_metadata UINT 'c1t1'
322 -->callmacro Assert_metadata BYTES 'c2t1'
323 -->callmacro Assert_rows 13
324 -->callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets
325 -->callmacro Assert_message Mysqlx.Error
326 -->callmacro Show_warnings
327 -->callmacro Deallocate_statement
328
329 -->callmacro Execute_procedure_no_output_params_with_error select_and_warning()
330 -->callmacro Assert_metadata DATETIME 'c1t2'
331 -->callmacro Assert_rows 4
332 -->callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets
333 -->callmacro Assert_message Mysqlx.Error
334 -->callmacro Show_warnings
335 -->callmacro Deallocate_statement
336
337 # Check stored procedures with error conditions with output parameters present
338 #
339 -->callmacro Execute_procedure_with_output_params_and_with_error out_param_and_error(?) @my_var
340 -->callmacro Assert_message Mysqlx.Error
341 -->callmacro Show_warnings
342 -->callmacro Deallocate_statement
343
344 -->callmacro Execute_procedure_with_output_params_and_with_error select_out_param_and_error(?) @my_var
345 -->callmacro Assert_metadata SINT 'COUNT(c1t1)'
346 -->callmacro Assert_rows 1
347 -->callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets
348 -->callmacro Assert_message Mysqlx.Error
349 -->callmacro Show_warnings
350 -->callmacro Deallocate_statement
351
352 -->callmacro Execute_procedure_with_output_params_and_with_error out_param_and_warning(?) @my_var
353 -->callmacro Assert_message Mysqlx.Error
354 -->callmacro Show_warnings
355 -->callmacro Deallocate_statement
356
357 -->callmacro Execute_procedure_with_output_params_and_with_error select_out_param_and_warning(?) @my_var
358 -->callmacro Assert_metadata SINT 'COUNT(c1t1)'
359 -->callmacro Assert_rows 1
360 -->callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets
361 -->callmacro Assert_message Mysqlx.Error
362 -->callmacro Show_warnings
363 -->callmacro Deallocate_statement
364
365 EOF
366