| 1 |
## Test of multi-resultsets and procedures output-parameters (part of WL9270). |
| 2 |
|
| 3 |
## Preamble |
| 4 |
--source include/xplugin_preamble.inc |
| 5 |
--source include/xplugin_create_user.inc |
| 6 |
|
| 7 |
## |
| 8 |
## Purpose of this test is: |
| 9 |
# |
| 10 |
# * validating out-params are transfered only in case of statements |
| 11 |
# prepared with Mysqlx.Prepare.Prepare |
| 12 |
# * validate that out-variables are set in case SQL prepare and SQL execution |
| 13 |
# |
| 14 |
# The out-param behavior depends on method(way) how the SP was executed: |
| 15 |
# |
| 16 |
# |Method |Sets out-params to user variables |Transfers out-params through X Protocol | |
| 17 |
# |-----------------|-----------------------------------|----------------------------------------| |
| 18 |
# |Direct SQL call |yes |no | |
| 19 |
# |SQL prepare call |yes |no | |
| 20 |
# |MySQLX prep call |no |yes | |
| 21 |
# |
| 22 |
# Please consider that macro 'Test_assert_user_variable' in some test executions is going to |
| 23 |
# ignore second parameter and use in its place '-1'. |
| 24 |
# |
| 25 |
|
| 26 |
## Test starts here |
| 27 |
--write_file $MYSQL_TMP_DIR/multiple_resultsets_and_out_params.xpl |
| 28 |
|
| 29 |
import assert_variable.macro; |
| 30 |
import %IMPORT_FILE%; |
| 31 |
|
| 32 |
callmacro Test_prepare_and_execute_proc_arg0 `empty`(); |
| 33 |
callmacro Test_prepare_and_execute_proc_arg0 one_results_set(); |
| 34 |
callmacro Test_prepare_and_execute_proc_arg0 three_results_sets(); |
| 35 |
|
| 36 |
callmacro Test_reset_variables; |
| 37 |
callmacro Test_prepare_and_execute_proc_arg1 out_param_no_select(@in_val,?) @res; |
| 38 |
callmacro Test_assert_user_variable res 10; |
| 39 |
|
| 40 |
callmacro Test_prepare_and_execute_proc_arg2 out_params_with_select(?,?) @min_val @max_val; |
| 41 |
callmacro Test_assert_user_variable min_val 1979-11-24 00:00:00; |
| 42 |
callmacro Test_assert_user_variable max_val 2013-06-15 00:00:00; |
| 43 |
|
| 44 |
callmacro Test_prepare_and_execute_proc_arg0 update_no_select(); |
| 45 |
callmacro Test_prepare_and_execute_proc_arg0 update_and_select(); |
| 46 |
callmacro Test_prepare_and_execute_proc_arg1 update_and_out_param(?) @max_val; |
| 47 |
callmacro Test_assert_user_variable max_val 6; |
| 48 |
callmacro Test_prepare_and_execute_proc_arg1 update_and_out_param_and_select(?) @max_val; |
| 49 |
callmacro Test_assert_user_variable max_val 12; |
| 50 |
|
| 51 |
callmacro Test_prepare_and_execute_proc_arg0 insert_no_select(); |
| 52 |
callmacro Test_prepare_and_execute_proc_arg0 insert_and_select(); |
| 53 |
callmacro Test_prepare_and_execute_proc_arg1 insert_and_out_param(?) @max_val; |
| 54 |
callmacro Test_assert_user_variable max_val 9999; |
| 55 |
callmacro Test_prepare_and_execute_proc_arg1 insert_and_out_param_and_select(?) @max_val; |
| 56 |
callmacro Test_assert_user_variable max_val 19999; |
| 57 |
|
| 58 |
callmacro Test_prepare_and_execute_proc_arg0 error() -->expecterror ER_NO_SUCH_TABLE; |
| 59 |
callmacro Test_prepare_and_execute_proc_arg0 select_and_error() -->expecterror ER_NO_SUCH_TABLE; |
| 60 |
|
| 61 |
callmacro Test_reset_variables; |
| 62 |
callmacro Test_prepare_and_execute_proc_arg1 out_param_and_error(?) @min_val -->expecterror ER_NO_SUCH_TABLE; |
| 63 |
callmacro Test_assert_user_variable min_val -1; |
| 64 |
|
| 65 |
callmacro Test_prepare_and_execute_proc_arg1 select_out_param_and_error(?) @min_val -->expecterror ER_NO_SUCH_TABLE; |
| 66 |
callmacro Test_assert_user_variable min_val -1; |
| 67 |
|
| 68 |
callmacro Test_prepare_and_execute_proc_arg0 with_warning() -->expectwarnings ER_WARN_DATA_OUT_OF_RANGE; |
| 69 |
|
| 70 |
callmacro Test_prepare_and_execute_proc_arg0 select_and_warning() -->expectwarnings ER_WARN_DATA_OUT_OF_RANGE; |
| 71 |
callmacro Test_prepare_and_execute_proc_arg1 out_param_and_warning(?) @vavg -->expectwarnings ER_WARN_DATA_OUT_OF_RANGE; |
| 72 |
SELECT @vavg; |
| 73 |
|
| 74 |
callmacro Test_prepare_and_execute_proc_arg0 select_out_param_and_warning(@min_val) -->expectwarnings WARN_DATA_TRUNCATED; |
| 75 |
callmacro Assert_user_variable min_val 3; |
| 76 |
|
| 77 |
EOF |
| 78 |
|
| 79 |
SET @@sql_mode = ""; |
| 80 |
--let $STORED_PROC_DB_NAME=xtest |
| 81 |
|
| 82 |
--echo |
| 83 |
--echo |
| 84 |
--echo ## I. Validate direct SP calls that generate multiple-results and uses out-params |
| 85 |
--echo # |
| 86 |
--echo # 1. Assert that result-file doesn't contain out-params inside the resultsets. |
| 87 |
--echo # 2. Assert that out-params set their assigned user variables |
| 88 |
--echo |
| 89 |
--echo ## II. Validate SQL prepared SP calls that generate multiple-results and uses out-params |
| 90 |
--echo # |
| 91 |
--echo # 1. Assert that result-file doesn't contain out-params inside the resultsets. |
| 92 |
--echo # 2. Assert that out-params set their assigned user variables |
| 93 |
--echo |
| 94 |
--echo ## III. Validate Mysqlx prepared SP calls that generate multiple-results and uses out-params |
| 95 |
--echo # |
| 96 |
--echo # 1. Assert that result-file contain out-params inside the resultsets. |
| 97 |
--echo # 2. Assert that out-params don't set their assigned user variables |
| 98 |
--echo |
| 99 |
|
| 100 |
--echo |
| 101 |
--echo # |
| 102 |
--echo # I. |
| 103 |
--source ../include/stored_procedures.inc |
| 104 |
exec $MYSQLXTEST |
| 105 |
-ux_root --password='' |
| 106 |
--schema=$STORED_PROC_DB_NAME |
| 107 |
-v%IMPORT_FILE%=test_multiple_resultsets_sql.macro |
| 108 |
--file=$MYSQL_TMP_DIR/multiple_resultsets_and_out_params.xpl 2>&1; |
| 109 |
|
| 110 |
--echo |
| 111 |
--echo # |
| 112 |
--echo # II. |
| 113 |
eval DROP SCHEMA IF EXISTS $STORED_PROC_DB_NAME; |
| 114 |
source ../include/stored_procedures.inc; |
| 115 |
exec $MYSQLXTEST |
| 116 |
-ux_root --password='' |
| 117 |
--schema=$STORED_PROC_DB_NAME |
| 118 |
-v%IMPORT_FILE%=test_multiple_resultsets_sql_prepare.macro |
| 119 |
--file=$MYSQL_TMP_DIR/multiple_resultsets_and_out_params.xpl 2>&1; |
| 120 |
|
| 121 |
--echo |
| 122 |
--echo # |
| 123 |
--echo # III. |
| 124 |
eval DROP SCHEMA IF EXISTS $STORED_PROC_DB_NAME; |
| 125 |
source ../include/stored_procedures.inc; |
| 126 |
exec $MYSQLXTEST |
| 127 |
-ux_root --password='' |
| 128 |
--schema=$STORED_PROC_DB_NAME |
| 129 |
-v%IMPORT_FILE%=test_multiple_resultsets_mysqlx_prepare.macro |
| 130 |
--file=$MYSQL_TMP_DIR/multiple_resultsets_and_out_params.xpl 2>&1; |
| 131 |
|
| 132 |
## Cleanup |
| 133 |
eval DROP SCHEMA IF EXISTS $STORED_PROC_DB_NAME; |
| 134 |
--source ../include/xplugin_cleanup.inc |