input: declaration and assignment of QueryScript local variables by externally provided values


input $variable1 [, $variable2 ...];
if ($variable1 = 'x')


input is a QueryScript statement which declares local variables, and assigns them values as given from an external source.

The input statement is only expected to appears once, if at all, within a script. It must not appear within a loop. The variables declared by input are local variables as any other variable, and the same rules apply for them as for all local variables.

input makes for an interface between external routines and QueryScript. A routines which wishes to invoke a script based on some values, passes pre-defined values via MySQL user defined variables, and these are assigned to the input local variables.

In particular, when an input statement is encountered, the following MySQL user defined variables are looked at:

  • @_query_script_input_col1, assigned to 1st declared variable.
  • @_query_script_input_col2, assigned to 2nd declared variable.
  • ...
  • @_query_script_input_col9, assigned to 9th declared variable.
While syntactically permitted, it makes no sense to declare more than 9 input variables, as nothing will map to any variable exceeding the 9th.

To illustrate, consider:

input $a, $b, $c;

$a will be assigned the value of @_query_script_input_col1.
$b will be assigned the value of @_query_script_input_col2.
$c will be assigned the value of @_query_script_input_col3.
The values of @_query_script_input_col[4..9] remain unread.

A routine which in fact sends input data to QueryScript is foreach() (not to be confused with QueryScript's own foreach flow control looping device). In the following example:

mysql> call foreach('{USA, FRA, CAN}', '
  input $country;
  DELETE FROM world.City WHERE CountryCode = $country;
foreach sets @_query_script_input_col1 to the iterating value of 'USA', 'FRA', 'CAN', each in turn, and calls upon the script which assigns the value onto the $country variable.

For completeness, the above example is very simple, so as to illustrate the workings of input. The following two code fragments perform the same operation as the above:

mysql> call foreach('{USA, FRA, CAN}', 'DELETE FROM world.City WHERE CountryCode = ''${1}''');
Or, via QueryScript only:
foreach($country: {USA, FRA, CAN})
  DELETE FROM world.City WHERE CountryCode = $country;

Developers who wish to integrate QueryScript execution into their code are able to do so by providing the aforementioned user defined variables and by using input.


var, Variables


Shlomi Noach
common_schema documentation